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 and the same reference system. 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.
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
Sixteen axis-aligned position predicates, parallel to those on stbox. Each tests strict-or-overlap relationship along one axis. All require the same schema and the same reference system, and a mismatch in either raises an error. Z-axis predicates require both boxes to have a Z dimension; T-axis predicates require both to have a T dimension.
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 those tests are false. SELECT tpcboxX(0, 0, 2, 2) <</ tpcboxX(5, 5, 7, 7); -- f SELECT tpcboxX(0, 0, 2, 2) <<# tpcboxX(5, 5, 7, 7); -- f
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