Table of Contents
We present next the functions and operators for bounding box types. These functions and operators are polimorhic, that is, their argumentos can be of various types and their result type may depend on the type of the arguments. To express this in the signature of the operators, we use the following notation:
box represents any bounding box type, that is, tbox or stbox,
number represents any number base type, that is, integer, bigint, or float,
numspan represents any number span type, that is, intspan, bigintspan, or floatspan,
numbers represents any number set or range type, such as integer, intset, intspan, or intspanset,
times represents any time type with timestamptz granularity, that is, timestamptz, tstzset, tstzspan, or tstzspanset,
tnumber represents any temporal number type, that is, tint, tbigint, or tfloat,
geo represents the types geometry or geography,
tgeo represents a temporal geometry/geography type, that is, tgeometry, tgeography, tgeompoint, or tgeogpoint.
MobilityDB generalizes Open Geospatial Consortium's Well-Known Text (WKT) and Well-Known Binary (WKB) input and output format for all temporal types. We present next the functions for input and output box types.
A tbox is composed of a numeric and/or time dimensions. For each dimension, a span given, that is, one of an intspan, bigintspan, or a floatspan for the value dimension and a tstzspan for the time dimension. Examples of input of tbox values are as follows:
-- Both value and time dimensions SELECT tbox 'TBOXINT XT([1,3),[2001-01-01,2001-01-02])'; SELECT tbox 'TBOXBIGINT XT([1,3),[2001-01-01,2001-01-02])'; SELECT tbox 'TBOXFLOAT XT([1.5,2.5],[2001-01-01,2001-01-02])'; -- Only value dimension SELECT tbox 'TBOXINT X([1,3))'; SELECT tbox 'TBOXBIGINT X([1,3))'; SELECT tbox 'TBOXFLOAT X((1.5,2.5))'; -- Only time dimension SELECT tbox 'TBOX T((2001-01-01,2001-01-02))';
An stbox is composed of a spatial and/or time dimensions, where the coordinates of the spatial dimension may be 2D or 3D. For the time dimension a tstzspan is given and for the spatial dimension minimum and maximum coordinate values are given, where the latter may be Cartesian (planar) or geodetic (spherical). The SRID of the coordinates may be specified; if it is not the case, a value of 0 (unknown) and 4326 (corresponding to WGS84) is assumed, respectively, for planar and geodetic boxes. Geodetic boxes always have a Z dimension to account for the curvature of the underlying sphere or spheroid. Examples of input of stbox values are as follows:
-- Only value dimension with X and Y coordinates SELECT stbox 'STBOX X((1.0,2.0),(1.0,2.0))'; -- Only value dimension with X, Y, and Z coordinates SELECT stbox 'STBOX Z((1.0,2.0,3.0),(1.0,2.0,3.0))'; -- Both value (with X and Y coordinates) and time dimensions SELECT stbox 'STBOX XT(((1.0,2.0),(1.0,2.0)),[2001-01-03,2001-01-03])'; -- Both value (with X, Y, and Z coordinates) and time dimensions SELECT stbox 'STBOX ZT(((1.0,2.0,3.0),(1.0,2.0,3.0)),[2001-01-01,2001-01-03])'; -- Only time dimension SELECT stbox 'STBOX T([2001-01-03,2001-01-03])'; -- Only value dimension with X, Y, and Z geodetic coordinates SELECT stbox 'GEODSTBOX Z((1.0,2.0,3.0),(1.0,2.0,3.0))'; -- Both value (with X, Y and Z geodetic coordinates) and time dimension SELECT stbox 'GEODSTBOX ZT(((1.0,2.0,3.0),(1.0,2.0,3.0)),[2001-01-04,2001-01-04])'; -- Only time dimension for geodetic box SELECT stbox 'GEODSTBOX T([2001-01-03,2001-01-03])'; -- SRID is given SELECT stbox 'SRID=5676;STBOX XT(((1.0,2.0),(1.0,2.0)),[2001-01-04,2001-01-04])'; SELECT stbox 'SRID=4326;GEODSTBOX Z((1.0,2.0,3.0),(1.0,2.0,3.0))';
We give next the functions for input and output of box types in Well-Known Text (WKT) and Well-Known Binary (WKB) format.
Return the Well-Known Text (WKT) representation
asText(box,maxdecimaldigits integer=15) → text
The maxdecimaldigits argument can be used to set the maximum number of decimal places in the output of floating point values (default 15).
SELECT asText(tbox 'TBOXFLOAT XT([1.123456789,2.123456789),[2001-01-01,2001-01-02))', 3); -- TBOXFLOAT XT([1.123, 2.123),[2001-01-01, 2001-01-02)) SELECT asText(stbox 'STBOX Z((1.55,1.55,1.55),(2.55,2.55,2.55))', 0); -- STBOX Z((2,2,2),(3,3,3))
Return the Well-Known Binary (WKB) or the Hexadecimal Well-Known Binary (HexWKB) representation
asBinary(box,endian text='') → bytea asHexWKB(box,endian text='') → text
The result is encoded using either the little-endian (NDR) or the big-endian (XDR) encoding. If no encoding is specified, then the encoding of the machine is used. As for every spatial type, the representation of an stbox does not write its SRID, which asEWKB and asHexEWKB write.
SELECT asBinary(tbox 'TBOXFLOAT XT([1,2),[2001-01-01,2001-01-02))'); -- \x0103270001009c57d3c11c000000fc2ef1d51c00000d0001000000000000f03f0000000000000040 SELECT asBinary(tbox 'TBOXFLOAT XT([1,2),[2001-01-01,2001-01-02))', 'XDR'); -- \x000300270100001cc1d3579c0000001cd5f12efc00000d013ff00000000000004000000000000000 SELECT asBinary(stbox 'STBOX X((1,1),(2,2))'); -- \x0101000000000000f03f0000000000000040000000000000f03f0000000000000040 SELECT asHexWKB(tbox 'TBOXFLOAT XT([1,2),[2001-01-01,2001-01-02))'); -- 0103270001009C57D3C11C000000FC2EF1D51C00000D0001000000000000F03F0000000000000040 SELECT asHexWKB(tbox 'TBOXFLOAT XT([1,2)[2001-01-01,2001-01-02))', 'XDR'); -- 000300270100001CC1D3579C0000001CD5F12EFC00000D013FF00000000000004000000000000000 SELECT asHexWKB(stbox 'STBOX X((1,1),(2,2))'); -- 0101000000000000F03F0000000000000040000000000000F03F0000000000000040
Return the Extended Well-Known Binary (EWKB) or the Hexadecimal Extended Well-Known Binary (HexEWKB) representation of a spatiotemporal box
asEWKB(stbox,endian text='') → bytea asHexEWKB(stbox,endian text='') → text
The result is the representation asBinary and asHexWKB write with the SRID of the box, as every spatial type writes its SRID in its extended representation.
SELECT asEWKB(stbox 'SRID=3812;STBOX X((1,1),(2,2))'); -- \x0141e40e0000000000000000f03f0000000000000040000000000000f03f0000000000000040 SELECT asHexEWKB(stbox 'SRID=3812;STBOX X((1,1),(2,2))', 'XDR'); -- 004100000EE43FF000000000000040000000000000003FF00000000000004000000000000000
Input from the Well-Known Text (WKT) representation
boxFromText(text) → box
In the signature above, box replaces any box type, that is, tbox or stbox. The function is the inverse of asText: it reads the text a literal of the type is written in, from a constant or from a text column alike, including the SRID of a spatiotemporal box.
SELECT tboxFromText('TBOXFLOAT XT([1,2],[2001-01-01,2001-01-02])');
-- TBOXFLOAT XT([1, 2],[2001-01-01, 2001-01-02])
SELECT stboxFromText('SRID=3812;STBOX XT(((1,1),(2,2)),[2001-01-01,2001-01-02])');
-- SRID=3812;STBOX XT(((1,1),(2,2)),[2001-01-01, 2001-01-02])
Input from the Well-Known Binary (WKB) or from the Hexadecimal Well-Known Binary (HexWKB) representation
boxFromBinary(bytea) → box boxFromHexWKB(text) → box
In the signatures above, box replaces any box type, that is, tbox or stbox.
SELECT tboxFromBinary( '\x0103270001009c57d3c11c000000fc2ef1d51c00000d0001000000000000f03f0000000000000040'); -- TBOXFLOAT XT([1,2),[2001-01-01,2001-01-02)) SELECT stboxFromBinary( '\x0101000000000000f03f0000000000000040000000000000f03f0000000000000040'); -- STBOX X((1,1),(2,2)) SELECT tboxFromHexWKB( '0103270001009C57D3C11C000000FC2EF1D51C00000D0001000000000000F03F0000000000000040'); -- TBOXFLOAT XT([1,2),[2001-01-01,2001-01-02))) SELECT stboxFromHexWKB( '0101000000000000F03F0000000000000040000000000000F03F0000000000000040'); -- STBOX X((1,1),(2,2))
Input of a spatiotemporal box from the Extended Well-Known Binary (EWKB) or the Hexadecimal Extended Well-Known Binary (HexEWKB) representation
stboxFromEWKB(bytea) → stbox stboxFromHexEWKB(text) → stbox
SELECT stboxFromEWKB(asEWKB(stbox 'SRID=3812;STBOX X((1,1),(2,2))')); -- SRID=3812;STBOX X((1,1),(2,2)) SELECT stboxFromHexEWKB( '0141E40E0000000000000000F03F0000000000000040000000000000F03F0000000000000040'); -- SRID=3812;STBOX X((1,1),(2,2))