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.
Construct a tpcbox from explicit X/Y/Z/T bounds and a pcid, whose schema states the SRID
tpcboxX(xmin,ymin,xmax,ymax,pcid) → tpcbox tpcboxZ(xmin,ymin,zmin,xmax,ymax,zmax,pcid) → tpcbox tpcboxT(period,pcid) → tpcbox tpcboxXT(xmin,ymin,xmax,ymax,period,pcid) → tpcbox tpcboxZT(xmin,ymin,zmin,xmax,ymax,zmax,period,pcid) → 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