Convert between a tcell and a tbigint

{bigint,quadbin}::{quadbin,bigint}
{bigint,s2cell}::{s2cell,bigint}
{tbigint,tcell}::{tcell,tbigint}

The cast out of a cell type is a binary coercion, since every cell is an integer. The cast into a cell type checks every value as the text input of the type does and raises an error otherwise, as the MF-JSON and WKB input of the type do: an S2 value must be a cell of its grid, a QUADBIN value a well-formed index of any mode, as the quadbin type holds, and an H3 value a cell, a directed edge or a vertex, since an H3 index names all three. The h3index type and its casts to and from bigint are those of the H3 extension. The cast round trips: casting a cell to an integer and back answers the cell it started from.

SELECT (tbigint '590464338553208831@2001-01-01')::th3index;
-- 831c02fffffffff@2001-01-01
SELECT (tbigint '5234909528541102079@2001-01-01')::tquadbin;
-- 48a6227affffffff@2001-01-01
SELECT (tbigint '5171187903383994368@2001-01-01')::ts2cell;
-- 47c3c@2001-01-01
SELECT 5234909528541102079::bigint::quadbin;
-- 48a6227affffffff
SELECT (tbigint '42@2001-01-01')::tquadbin;
-- ERROR:  The value 42 does not encode a valid quadbin index

Cast a temporal cell to a temporal geodetic or planar point of cell centroids

th3index::{tgeogpoint,tgeompoint}
tquadbin::tgeompoint
ts2cell::tgeogpoint

The cast of a QUADBIN cell answers a point of the Web-Mercator plane the grid is defined on, while the cast of an S2 cell answers a geodetic one; each grid declares the single cast its own definition calls for. H3 declares both, so it is the one grid where the caller chooses between the geodetic and the SRID-4326 planar representation.

SELECT asText((th3index '831c02fffffffff@2001-01-01')::tgeogpoint, 6);
-- POINT(-144.523991 49.716503)@2001-01-01
SELECT asText((th3index '831c02fffffffff@2001-01-01')::tgeompoint, 6);
-- POINT(-144.523991 49.716503)@2001-01-01
SELECT asText((tquadbin '48a6227affffffff@2001-01-01')::tgeompoint, 6);
-- POINT(4.394531 50.847573)@2001-01-01
SELECT asText((ts2cell '47c3c@2001-01-01')::tgeogpoint, 6);
-- POINT(4.222019 50.936164)@2001-01-01

Convert a Raquet tile into a spatiotemporal box

stbox(raquet) → stbox

Returns the tile footprint: the longitude and latitude envelope of its QUADBIN cell, in SRID 4326 and without a time dimension. The footprint follows from the cell alone, so tiles differing in pixels, dimensions or pixel type share it. The conversion is also available as a cast.

-- The footprint of a tile covering the north-eastern quadrant at zoom 1
SELECT round(stbox(raquet('\x01020304'::bytea, 2, 2, quadbin '4817ffffffffffff',
  'uint8')), 6);
-- SRID=4326;STBOX X((0,0),(180,85.051129))
-- Keep the tiles whose footprint overlaps a region of interest
SELECT * FROM elevation_tiles WHERE tile::stbox && stbox 'SRID=4326;
  STBOX X((0,0),(30,30))';

The footprint carries the stbox operators, aggregates and operator classes, so a tile table is searched by spatial overlap at any zoom level rather than by an equality test on the QUADBIN cell at a single fixed one. Indexing the conversion gives the tile table a spatial index, and the extent of a set of tiles is the extent of their footprints.

-- A spatial index over the tile footprints
CREATE INDEX elevation_tiles_gist ON elevation_tiles USING gist (stbox(tile));
CREATE INDEX elevation_tiles_spgist ON elevation_tiles USING spgist (stbox(tile));
-- The extent covered by a set of tiles
SELECT extent(stbox(tile)) FROM elevation_tiles;
-- Tiles a trajectory passes through, at whatever zoom levels the table holds
SELECT t.tripid, r.tile FROM trips t,
  elevation_tiles r WHERE stbox(r.tile) && stbox(t.trip);

Convert a raster into a spatiotemporal box

stbox(raster) → stbox

Returns the extent of the raster in the reference system the raster itself states, without a time dimension. This is the one shape of a PostGIS raster the extension states, because PostGIS has no stbox: the width, height, georeferencing and band properties are read with PostGIS's own accessors, which are not restated here. The conversion is also available as a cast.

WITH rast AS (
  SELECT ST_MakeEmptyRaster(3, 3, 0.0, 3.0, 1.0, -1.0, 0.0, 0.0, 4326) AS r
)
SELECT stbox(r) AS box, stbox(r) = r::stbox AS cast_agrees FROM rast;
-- SRID=4326;STBOX X((0,0),(3,3)) | t
-- The box states the reference system of the raster, not a fixed one
SELECT stbox(ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0, 0.0, 0.0, 3857));
-- SRID=3857;STBOX X((0,0),(2,2))

The extent bears the rotation of the geotransform, so a skewed raster answers the box containing its four rotated corners rather than the one its scale alone would give.

WITH plain AS (
  SELECT ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0, 0.0, 0.0, 4326) AS r
), skewed AS (
  SELECT ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0, 1.0, 0.0, 4326) AS r
)
SELECT stbox((SELECT r FROM plain)) AS plain_box,
  stbox((SELECT r FROM skewed)) AS skewed_box;
-- SRID=4326;STBOX X((0,0),(2,2)) | SRID=4326;STBOX X((0,0),(4,2))

The extent carries the stbox operators, aggregates and operator classes, so a raster table is searched by spatial overlap and indexed on the conversion.

SELECT stbox(ST_MakeEmptyRaster(3, 3, 0.0, 3.0, 1.0, -1.0, 0.0, 0.0, 4326)) &&
  stbox 'SRID=4326;STBOX X((1,1),(2,2))';
-- t