Point Cloud Bounding Boxes

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.

Input and Output

  • 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));
    -- 01500001000000000000000000000000000024400000000000000000000000000000244001000000
    SELECT asHexWKB(tpcboxX(0, 0, 10, 10, 5), 'XDR');
    -- 00005001000000000000000040240000000000000000000000000000402400000000000000000005
    
  • 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));
    -- 01500001000000000000000000000000000024400000000000000000000000000000244001000000
    SELECT asHexEWKB(tpcboxX(0, 0, 10, 10, 5), 'XDR');
    -- 00005041000010E6000000000000000040240000000000000000000000000000402400000000000000000005
    
  • 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). As for every spatial type, the text of a tpcbox does not write its SRID, which asEWKT writes.

    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)
    SELECT asText(tpcbox 'SRID=4326;TPCBOX(X((0,0),(10,10)), 5)');
    -- TPCBOX(X((0,0),(10,10)), 5)
    
  • Return the Extended Well-Known Text (EWKT) representation of a point cloud box

    asEWKT(tpcbox,maxdecimaldigits integer=15) → text
    

    The result is the text asText writes prefixed with the SRID of the box, as every spatial type writes its SRID in its extended representation.

    SELECT asEWKT(tpcbox 'SRID=4326;TPCBOX(X((0,0),(10,10)), 5)');
    -- SRID=4326;TPCBOX(X((0,0),(10,10)), 5)
    
  • 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)
    
  • Input of a point cloud box from the Extended Well-Known Text (EWKT) representation

    tpcboxFromEWKT(text) → tpcbox
    

    The function is the inverse of asEWKT, reading the SRID the text states.

    SELECT tpcboxFromEWKT('SRID=4326;TPCBOX(X((0,0),(10,10)), 5)');
    -- SRID=4326;TPCBOX(X((0,0),(10,10)), 5)
    

Constructors

  • 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)
    

Conversions

  • 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)
    

Accessors

  • 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
    

Transformations

  • 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)
    
  • Transform to the schema of another pcid

    transform(tpcbox,pcid integer) → tpcbox
    

    The spatial extent of the box is transformed from the SRID of the schema of its pcid to the SRID of the schema of the target pcid, as the transform function of stbox transforms it, and the box takes the target pcid. A box without spatial extent only takes the target pcid. An error is raised when the target pcid names no schema or when either schema states no SRID.

    -- pcid 101 names a schema in SRID 4326 and pcid 102 a schema in SRID 3857
    WITH b AS (SELECT transform(tpcbox(pcpoint(101, 4.35, 50.85, 10)), 102) AS b)
    SELECT round(xMin(b)::numeric, 3), pcid(b), SRID(b) FROM b;
    -- 484239.785 | 102 | 3857
    

Set Operations

  • 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.

Topological Operations

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
    

Position Operations

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
    

Comparisons

  • 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