The tpcbox type is the spatiotemporal bounding box for the temporal point-cloud types. It mirrors the structure of stbox (see Chapter 3, Bounding Box Types), same X/Y/Z/T axes, same flag bits for which dimensions are present, and adds a single field: the pcid. Two tpcbox values with different pcid values describe different schemas, and an operation between them raises an error instead of comparing their geometric or temporal extent.
Carrying the pcid through bounding-box arithmetic guarantees that bounding-box pruning never drops a row whose schema differs from the query's schema, and never returns a hit across mismatched schemas.
Return the Well-Known Binary (WKB) or the Hexadecimal Well-Known Binary (HexWKB) representation of a point cloud box
asBinary(tpcbox,endian text='') → bytea asHexWKB(tpcbox,endian text='') → text
The result is the representation of the stbox the box spans followed by its pcid, encoded using either the little-endian (NDR) or the big-endian (XDR) encoding, the encoding of the machine when none is specified. As for every spatial type, the plain representation does not write the SRID, which asEWKB and asHexEWKB write.
SELECT asHexWKB(tpcboxX(0, 0, 10, 10, 1)); -- 0101000000000000000000000000000024400000000000000000000000000000244001000000 SELECT asHexWKB(tpcboxX(0, 0, 10, 10, 5), 'XDR'); -- 0001000000000000000040240000000000000000000000000000402400000000000000000005
Return the Extended Well-Known Binary (EWKB) or the Hexadecimal Extended Well-Known Binary (HexEWKB) representation of a point cloud box
asEWKB(tpcbox,endian text='') → bytea asHexEWKB(tpcbox,endian text='') → text
The result is the representation asBinary and asHexWKB write with the SRID of the box, the one the schema of its pcid states, as every spatial type writes its SRID in its extended representation.
SELECT asHexEWKB(tpcboxX(0, 0, 10, 10, 1)); -- 0101000000000000000000000000000024400000000000000000000000000000244001000000 SELECT asHexEWKB(tpcboxX(0, 0, 10, 10, 5), 'XDR'); -- 0041000010E6000000000000000040240000000000000000000000000000402400000000000000000005
Input of a point cloud box from the Well-Known Binary (WKB) or the Hexadecimal Well-Known Binary (HexWKB) representation
tpcboxFromBinary(bytea) → tpcbox tpcboxFromHexWKB(text) → tpcbox
The SRID of the result is the one the schema of its pcid states, which the plain representation does not write.
SELECT asText(tpcboxFromBinary(asBinary(tpcboxX(0, 0, 10, 10, 5)))); -- SRID=4326;TPCBOX(X((0,0),(10,10)), 5)
Input of a point cloud box from the Extended Well-Known Binary (EWKB) or the Hexadecimal Extended Well-Known Binary (HexEWKB) representation
tpcboxFromEWKB(bytea) → tpcbox tpcboxFromHexEWKB(text) → tpcbox
The SRID the input states must be the one the schema of its pcid states, when that schema states one.
SELECT tpcboxFromHexEWKB(asHexEWKB(tpcboxX(0, 0, 10, 10, 1))); -- TPCBOX(X((0,0),(10,10)), 1) SELECT tpcboxFromHexEWKB(replace(asHexEWKB(tpcboxX(0, 0, 10, 10, 5)), 'E6100000', '110F0000')); -- ERROR: Could not parse tpcbox value: The value states SRID 3857 and the schema of pcid 5 states SRID 4326
Return the Well-Known Text (WKT) representation
asText(tpcbox,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(tpcbox 'TPCBOX(X((0.12,1.23),(2.35,3.46)), 1)'); -- TPCBOX(X((0.12,1.23),(2.35,3.46)), 1) SELECT asText(tpcbox 'TPCBOX(X((0.12,1.23),(2.35,3.46)), 1)', 1); -- TPCBOX(X((0.1,1.2),(2.4,3.5)), 1)
Input from the Well-Known Text (WKT) representation
tpcboxFromText(text) → tpcbox
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.
SELECT tpcboxFromText('TPCBOX(ZT(((0,0,0),(10,10,10)),[2024-01-01, 2024-01-02]), 1)');
-- TPCBOX(ZT(((0,0,0),(10,10,10)),[2024-01-01, 2024-01-02]), 1)
Construct a tpcbox from explicit X/Y/Z/T bounds and a pcid, whose schema states the SRID
tpcboxX(xmin float,ymin float,xmax float,ymax float,pcid integer=0) → tpcbox tpcboxZ(xmin float,ymin float,zmin float,xmax float,ymax float,zmax float, pcid integer=0) → tpcbox tpcboxT(period tstzspan,pcid integer=0) → tpcbox tpcboxXT(xmin float,ymin float,xmax float,ymax float,period tstzspan, pcid integer=0) → tpcbox tpcboxZT(xmin float,ymin float,zmin float,xmax float,ymax float,zmax float, period tstzspan,pcid integer=0) → tpcbox
The SRID is not a parameter: it is read from the schema the pcid names, and a pcid naming no registered schema is an error
SELECT tpcboxX(0, 0, 10, 10, 1); -- TPCBOX(X((0,0),(10,10)), 1) SELECT tpcboxZT(0, 0, 0, 10, 10, 10, tstzspan '[2024-01-01, 2024-01-02]', 1); -- TPCBOX(ZT(((0,0,0),(10,10,10)),[2024-01-01, 2024-01-02]), 1)
Convert a pcpoint to a degenerate (zero-extent) tpcbox; SRID and pcid come from the schema
tpcbox(pcpoint) → tpcbox
SELECT tpcbox(pcpoint(1, 10.0, 20.0, 30.0)); -- TPCBOX(Z((10,20,30),(10,20,30)), 1)
Convert a pcpatch to a tpcbox
tpcbox(pcpatch) → tpcbox
The X and Y extent of the result is the PCBOUNDS the patch embeds, and its Z extent, where the schema the pcid names holds a Z dimension, is the one the points of the patch state. The SRID is the one the schema the pcid names states.
SELECT tpcbox(pcpatch(pcpoint(1, 10.0, 20.0, 30.0))); -- TPCBOX(Z((10,20,30),(10,20,30)), 1)
Return whether a tpcbox has the X/Y, Z, or T dimensions set
hasX(tpcbox) → boolean hasZ(tpcbox) → boolean hasT(tpcbox) → boolean
WITH b AS (SELECT tpcboxX(0, 0, 10, 10, 1) AS b) SELECT hasX(b), hasZ(b) FROM b; -- t | f
Return a coordinate bound, NULL if the corresponding dimension is absent
xmin(tpcbox) → float xmax(tpcbox) → float ymin(tpcbox) → float ymax(tpcbox) → float zmin(tpcbox) → float zmax(tpcbox) → float
WITH b AS (SELECT tpcboxX(0, 0, 10, 10, 1) AS b) SELECT xmin(b), xmax(b) FROM b; -- 0 | 10
Return a temporal bound, NULL if the box has no T dimension
tmin(tpcbox) → timestamptz tmax(tpcbox) → timestamptz
SELECT tmin(tpcboxT(tstzspan '[2024-01-01, 2024-01-02]', 1)); -- 2024-01-01
Return the schema id and the SRID its schema states
pcid(tpcbox) → integer SRID(tpcbox) → integer
WITH b AS (SELECT tpcboxX(0, 0, 10, 10, 1) AS b) SELECT pcid(b), SRID(b) FROM b; -- 1 | 4326
Return a tpcbox with coordinates rounded to a given number of decimal digits
round(tpcbox,integer=0) → tpcbox
SELECT round(tpcboxX(0.123456, 1.234567, 2.345678, 3.456789, 1), 2); -- TPCBOX(X((0.12,1.23),(2.35,3.46)), 1)
Return a tpcbox stating a reference system its schema does not state
setSRID(tpcbox,integer) → tpcbox
The schema a pcid names is what holds the reference system, so where that schema states one the box is refused: a pcid of 0 names no schema, and a schema may state none. Does not reproject coordinates, for that, project the underlying tpcpoint first and take the bbox of the result
SELECT SRID(setSRID(tpcboxX(0, 0, 10, 10), 4326)); -- 4326 SELECT setSRID(tpcboxX(0, 0, 10, 10, 1), 3857); -- ERROR: The SRID of a TPCBox is the one its schema states: pcid 1 states SRID 4326
Union and intersection of two tpcboxes
tpcbox {+, *} tpcbox → tpcbox
Both operands must share the same pcid, and the intersection is NULL when the two boxes are disjoint.
SELECT tpcboxX(0, 0, 5, 5, 1) + tpcboxX(3, 3, 10, 10, 1); -- TPCBOX(X((0,0),(10,10)), 1) SELECT tpcboxX(0, 0, 5, 5, 1) * tpcboxX(3, 3, 10, 10, 1); -- TPCBOX(X((3,3),(5,5)), 1)
Note: a generic box-vs-box minus is not provided, mirroring the convention for stbox and tbox: the difference of two boxes is not generally a box.
All topological operators take two tpcbox values that state the same schema, the same reference system, and the same kind of coordinate, planar or spherical. A mismatch in either raises an error rather than answering false: the pcid is what gives a stored number its meaning as a coordinate, so two boxes naming different schemas have no common extent to compare.
They also take two boxes sharing an axis. A box holding coordinates and a box holding a period meet on no axis at all, so a topological predicate between them has nothing to compare and raises in its turn.
Topological operators
tpcbox {&&, @>, <@, ~=, -|-} tpcbox → boolean
SELECT tpcboxX(0, 0, 10, 10, 1) @> tpcboxX(2, 2, 8, 8, 1); -- t SELECT tpcboxX(2, 2, 8, 8, 1) <@ tpcboxX(0, 0, 10, 10, 1); -- t SELECT tpcboxX(0, 0, 5, 5, 1) && tpcboxX(3, 3, 10, 10, 1); -- t SELECT tpcboxX(0, 0, 2, 2) ~= tpcboxX(0, 0, 2, 2); -- t SELECT tpcboxX(0, 0, 2, 2) -|- tpcboxX(5, 5, 7, 7); -- f SELECT tpcboxX(0, 0, 5, 5, 1) && tpcboxX(0, 0, 5, 5, 2); -- ERROR: Operation on TPCBox values with different schemas: 1 vs 2 SELECT tpcboxX(0, 0, 10, 10, 1) && tpcboxT(tstzspan '[2024-01-01, 2024-01-02]', 0); -- ERROR: The temporal values must have at least one common dimension SELECT tpcbox 'SRID=4326;TPCBOX(X((1,1),(2,2)), 1)' && tpcbox 'GEODTPCBOX(X((1,1),(2,2)), 1)'; -- ERROR: Operation on mixed planar and geodetic coordinates
Sixteen axis-aligned position predicates, parallel to those on stbox. Each tests a strict-or-overlap relationship along one axis. The twelve that read coordinates require the same schema, the same reference system and the same kind of coordinate, and a mismatch in any of the three raises an error; the four that read the time axis require neither, a timestamp meaning the same thing whichever schema the coordinates are read in. Every predicate requires both boxes to carry the axis it reads, so the Z-axis ones need a Z dimension in both and the T-axis ones a time span in both.
Position operators
tpcbox {<<, &<, >>, &>} tpcbox → boolean
tpcbox {<<|, &<|, |>>, |&>} tpcbox → boolean
tpcbox {<</, &</, />>, /&>} tpcbox → boolean
tpcbox {<<#, &<#, #>>, #&>} tpcbox → boolean
The four groups test the X, the Y, the Z and the time axis, and each states, in order, strictly before, does not extend after, strictly after, and does not extend before along its axis.
SELECT tpcboxX(0, 0, 2, 2) << tpcboxX(5, 5, 7, 7); -- t SELECT tpcboxX(0, 0, 2, 2) <<| tpcboxX(5, 5, 7, 7); -- t -- Neither box carries a z dimension or a time span, so neither of the next two -- has an axis to compare along. The last pair states two schemas, which the -- time axis does not read. SELECT tpcboxX(0, 0, 2, 2) <</ tpcboxX(5, 5, 7, 7); -- ERROR: The tpcbox must have Z dimension SELECT tpcboxX(0, 0, 2, 2) <<# tpcboxX(5, 5, 7, 7); -- ERROR: The tpcbox must have T dimension SELECT tpcboxXT(0, 0, 5, 5, tstzspan '[2024-01-01, 2024-01-15]', 1) <<# tpcboxXT(0, 0, 5, 5, tstzspan '[2025-01-01, 2025-01-15]', 2); -- t
Compare two tpcboxes; total order over the canonical encoding (pcid, srid, flags, period, then spatial bounds in XYZ-min/max order)
tpcbox {=, <>, <, <=, >, >=} tpcbox → boolean
SELECT tpcboxX(0, 0, 2, 2) = tpcboxX(0, 0, 2, 2); -- t SELECT tpcboxX(0, 0, 2, 2) <> tpcboxX(5, 5, 7, 7); -- t SELECT tpcboxX(0, 0, 2, 2) < tpcboxX(5, 5, 7, 7); -- t