A temporal cell value is entered using the standard temporal syntax with the underlying 64-bit cell identifier. The input parser accepts the canonical hexadecimal form each grid publishes — the form the H3 CLI and h3_cell_to_string emit, and the form the CARTO reference implementation reads — with an optional 0x prefix, in either letter case, and at most 16 significant digits. Hexadecimal is idiomatic in all three ecosystems and is what the output function emits, so every example below uses it. The value must also denote something its grid indexes: for H3 a cell, a directed edge or a vertex, so that a well-formed hexadecimal literal such as 12345, which is none of the three, is rejected rather than stored as a value with no location. For reference, the hexadecimal 480fffffffffffff used in the examples is the QUADBIN resolution-0 world cell, equal to the decimal 5192650370358181887.
The functions asBinary and asHexWKB serialize a raster or a raquet value, and rasterFromBinary, rasterFromHexWKB, raquetFromBinary and raquetFromHexWKB reconstruct it. A raster is written in the form PostGIS raster writes, so a raster column round-trips between PostgreSQL and any program reading that form. A tile carries its pixels and its QUADBIN georeferencing in one value, so it round-trips through a portable byte string with no spatial extension involved, and the hexadecimal form lets a tile column round-trip through a text-based COPY. Both output functions accept an optional endianness argument, NDR for little-endian or XDR for big-endian, defaulting to the endianness of the server.
Input and output of a temporal cell in its text form
tcell(text) → tcell text(tcell) → text
The companion static type cell holds a single (non-temporal) cell identifier and cellset a finite set of them; they share the temporal type's hexadecimal / decimal textual representation, carry the standard equality and ordering operators through a btree and a hash operator class, and expose the validity predicates below. A type modifier constrains a column to one subtype, which is Instant, or Sequence, or SequenceSet.
SELECT th3index '831c02fffffffff@2001-01-01';
-- 831c02fffffffff@2001-01-01
SELECT th3index '[831c02fffffffff@2001-01-01, 831c00fffffffff@2001-01-02]';
-- [831c02fffffffff@2001-01-01, 831c00fffffffff@2001-01-02]
SELECT th3index '{[831c02fffffffff@2001-01-01, 831c00fffffffff@2001-01-02]}';
-- {[831c02fffffffff@2001-01-01, 831c00fffffffff@2001-01-02]}
SELECT tquadbin '48a6227affffffff@2001-01-01';
-- 48a6227affffffff@2001-01-01
SELECT tquadbin '[48a6227affffffff@2001-01-01, 485623ffffffffff@2001-01-02]';
-- [48a6227affffffff@2001-01-01, 485623ffffffffff@2001-01-02]
SELECT tquadbin '{[48a6227affffffff@2001-01-01, 485623ffffffffff@2001-01-02]}';
-- {[48a6227affffffff@2001-01-01, 485623ffffffffff@2001-01-02]}
SELECT ts2cell '47c3c@2001-01-01';
-- 47c3c@2001-01-01
SELECT ts2cell '[47c3c@2001-01-01, 47c4@2001-01-02]';
-- [47c3c@2001-01-01, 47c4@2001-01-02]
SELECT ts2cell '{[47c3c@2001-01-01, 47c4@2001-01-02]}';
-- {[47c3c@2001-01-01, 47c4@2001-01-02]}
Serialize a temporal cell as text, as binary, or as hexadecimal well-known binary, with or without its SRID, or as MF-JSON
asText(tcell) → text asEWKT(tcell) → text asBinary(tcell,endian text='') → bytea asEWKB(tcell,endian text='') → bytea asHexWKB(tcell,endian text='') → text asHexEWKB(tcell,endian text='') → text asMFJSON(tcell) → text
The extended forms asEWKT, asEWKB and asHexEWKB state the SRID 4326 of a cell, which the other forms omit. The hexadecimal well-known binary form lets a temporal cell column round-trip through a text-based COPY. In MF-JSON each cell of the values array is its text form in a JSON string, the hexadecimal index of H3 and QUADBIN and the token of S2, since a JSON reader holding its numbers as double precision numbers loses the low bits of a 64-bit cell identifier, and the bbox member states the spatiotemporal box of the cells.
SELECT asEWKT(th3index '831c02fffffffff@2001-01-01');
-- SRID=4326;831c02fffffffff@2001-01-01
SELECT asHexEWKB(th3index '831c02fffffffff@2001-01-01');
-- 01460061E6100000FFFFFFFF2FC031080060885EC91C0000
SELECT asHexWKB(th3index '831c02fffffffff@2001-01-01');
-- 01460021FFFFFFFF2FC031080060885EC91C0000
SELECT asHexWKB(tquadbin '48a6227affffffff@2001-01-01');
-- 01490001FFFFFFFF7A22A6480060885EC91C0000
SELECT asHexWKB(ts2cell '47c3c@2001-01-01');
-- 015700210000000000C0C3470060885EC91C0000
SELECT asMFJSON(tquadbin '48a6227affffffff@2001-01-01');
/* {"type":"MovingQuadbin","crs":{"type":"Name","properties":{"name":"EPSG:4326"}},
"values":["48a6227affffffff"],"datetimes":["2001-01-01T00:00:00+00:00"],
"interpolation":"None"} */
Reconstruct a temporal cell from its text, binary, hexadecimal, or MF-JSON representation
tcellFromText(text) → tcell tcellFromEWKT(text) → tcell tcellFromBinary(bytea) → tcell tcellFromEWKB(bytea) → tcell tcellFromHexWKB(text) → tcell tcellFromHexEWKB(text) → tcell tcellFromMFJSON(text) → tcell
An SRID stated by an extended input must be 4326, the SRID of a cell. The MF-JSON input reads a cell written as its text form in a JSON string, and a cell written as the JSON number of its 64-bit identifier.
SELECT ts2cellFromText('47c3c@2001-01-01');
-- 47c3c@2001-01-01
SELECT th3indexFromEWKT('SRID=4326;831c02fffffffff@2001-01-01');
-- 831c02fffffffff@2001-01-01
SELECT tquadbinFromEWKB(asEWKB(tquadbin '48a6227affffffff@2001-01-01'));
-- 48a6227affffffff@2001-01-01
SELECT tquadbinFromMFJSON('{"type":"MovingQuadbin","values":["48a6227affffffff"],
"datetimes":["2001-01-01T00:00:00"],"interpolation":"None"}');
-- 48a6227affffffff@2001-01-01
SELECT tquadbinFromMFJSON('{"type":"MovingQuadbin","values":[5234909528541102079],
"datetimes":["2001-01-01T00:00:00"],"interpolation":"None"}');
-- 48a6227affffffff@2001-01-01
SELECT th3indexFromHexWKB(asHexWKB(th3index '831c02fffffffff@2001-01-01'));
-- 831c02fffffffff@2001-01-01
SELECT tquadbinFromHexWKB(asHexWKB(tquadbin '48a6227affffffff@2001-01-01'));
-- 48a6227affffffff@2001-01-01
SELECT ts2cellFromHexWKB(asHexWKB(ts2cell '47c3c@2001-01-01'));
-- 47c3c@2001-01-01
Return the Well-Known Binary (WKB) or the Hexadecimal Well-Known Binary (HexWKB) representation of a cell
asBinary(cell,endian text='') → bytea asHexWKB(cell,endian text='') → text
A cell is written as its 64-bit identifier, encoded using either the little-endian (NDR) or the big-endian (XDR) encoding, the encoding of the machine when none is specified. The SRID 4326 of a cell, which its grid fixes, is omitted.
SELECT asBinary(quadbin '48427fffffffffff'); -- \x01470000ffffffffff7f4248 SELECT asHexWKB(quadbin '48427fffffffffff', 'XDR'); -- 0000470048427FFFFFFFFFFF SELECT asHexWKB(s2cell '47c3c444c'); -- 01550000000000C044C4C347
Input of a cell from the Well-Known Binary (WKB) or the Hexadecimal Well-Known Binary (HexWKB) representation
cellFromBinary(bytea) → cell cellFromHexWKB(text) → cell
An SRID stated by an extended input must be 4326, the SRID of a cell, and the identifier must encode a cell of the grid.
SELECT quadbinFromHexWKB('01470040E6100000FFFFFFFFFF7F4248');
-- 48427fffffffffff
SELECT quadbinFromHexWKB('01470040110F0000FFFFFFFFFF7F4248');
-- ERROR: Only SRID 4326 (WGS84) is accepted for the type quadbin, received 3857
Return the text representation of a cell
asText(cell) → text
In the signature above, cell replaces h3index, quadbin or s2cell. The text is the hexadecimal index of H3 and QUADBIN and the token of S2, the form the output function emits; for h3index, the type of h3-pg, it is the text the output function of h3-pg writes.
SELECT asText(h3index '831c02fffffffff'); -- 831c02fffffffff SELECT asText(quadbin '48a6227affffffff'); -- 48a6227affffffff SELECT asText(s2cell '47c3c'); -- 47c3c
Input from the text representation of a cell
cellFromText(text) → cell
There is one function per cell type, the name of the function has as prefix the name of the type, which is h3index, quadbin or s2cell. The function is the inverse of asText and reads the forms a literal of the type accepts, from a constant or from a text column alike.
SELECT h3indexFromText('0x831C02FFFFFFFFF');
-- 831c02fffffffff
SELECT quadbinFromText('48a6227affffffff');
-- 48a6227affffffff
SELECT quadbinFromText('0x48a6227affffffff');
-- 48a6227affffffff
SELECT s2cellFromText('47c3c');
-- 47c3c
Return the Well-Known Binary (WKB) representation of a raster or a Raquet tile
asBinary({raster,raquet},endian text='') → bytea
SELECT length(asBinary(ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0, 0.0, 0.0, 4326)));
-- 61
SELECT get_byte(asBinary(ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0, 0.0, 0.0, 4326),
'XDR'), 0);
-- 0
SELECT length(asBinary(raquet('\x01020304'::bytea, 2, 2,
quadbin '4817ffffffffffff', 'uint8')));
-- 31
Return the Hexadecimal Well-Known Binary (HexWKB) representation of a raster or a Raquet tile
asHexWKB({raster,raquet},endian text='') → text
SELECT length(asHexWKB(ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0, 0.0, 0.0, 4326)));
-- 122
SELECT length(asHexWKB(raquet('\x01020304'::bytea, 2, 2,
quadbin '4817ffffffffffff', 'uint8')));
-- 62
Return a raster or a Raquet tile from its Well-Known Binary (WKB) representation
rasterFromBinary(bytea) → raster raquetFromBinary(bytea) → raquet
SELECT rasterFromBinary(asBinary(r, 'XDR')) = r
FROM (SELECT ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0, 0.0, 0.0, 4326) AS r) t;
-- true
SELECT raquetFromBinary(asBinary(raquet('\x01020304'::bytea, 2, 2,
quadbin '4817ffffffffffff', 'uint8')))
= raquet('\x01020304'::bytea, 2, 2, quadbin '4817ffffffffffff', 'uint8');
-- true
Return a raster or a Raquet tile from its Hexadecimal Well-Known Binary (HexWKB) representation
rasterFromHexWKB(text) → raster raquetFromHexWKB(text) → raquet
SELECT rasterFromHexWKB(asHexWKB(r, 'XDR')) = r
FROM (SELECT ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0, 0.0, 0.0, 4326) AS r) t;
-- true
SELECT raquetFromHexWKB(asHexWKB(raquet('\x01020304'::bytea, 2, 2,
quadbin '4817ffffffffffff', 'uint8')))
= raquet('\x01020304'::bytea, 2, 2, quadbin '4817ffffffffffff', 'uint8');
-- true
Return a raster, a Raquet tile or the values a trajectory reads as a CoverageJSON coverage
asCoverageJSON(raster) → text asCoverageJSON(raquet) → text asCoverageJSON(tfloat,tgeompoint) → text
CoverageJSON is the encoding of OGC API – Coverages and OGC API – Environmental Data Retrieval. A raster is a Grid coverage: its axes x and y run from the centre of the first pixel to the centre of the last, each band is a parameter band1, band2 ..., whose values run row by row from the top row, and a nodata pixel is null; a raster with a skew, whose pixels lie on no such axes, and a raster in no spatial reference system raise an error. A raquet tile is a grid of Web Mercator, EPSG:3857, over the bounds of its QUADBIN cell. The values a trajectory reads, such as those of rasterValue, are a Trajectory coverage whose points are the instants of the values, each at the position of the trajectory at its instant; values read at one place are a Point for one instant and a PointSeries for several, the instants on its axis t, the coverages the position query of OGC API – Environmental Data Retrieval answers. EPSG:4326 is stated as OGC CRS84, whose longitude comes first as MobilityDB states it.
SELECT asCoverageJSON(ST_SetValues(ST_AddBand(
ST_MakeEmptyRaster(3, 2, 10.0, 20.0, 2.0, -2.0, 0.0, 0.0, 3857),
'16BSI'::text, 7::float8, 0::float8), 1, 1, 1,
ARRAY[[1, 2, 3], [4, -5, 0]]::double precision[][]))::json
#> '{ranges,band1,values}';
-- [1,2,3,4,-5,null]
Return a raster written in a format of GDAL
asGDALRaster(rast raster,format text,options text[]=NULL,srid integer=NULL) → bytea asTIFF(rast raster,options text[]=NULL,srid integer=NULL) → bytea asPNG(rast raster,options text[]=NULL) → bytea asJPEG(rast raster,options text[]=NULL) → bytea
Writes the raster as PostGIS's ST_AsGDALRaster, ST_AsTIFF, ST_AsPNG and ST_AsJPEG write it, so that the bytes are those PostGIS writes. format is the short name of a GDAL driver that writes a copy of a raster into memory, such as GTiff, PNG or JPEG; options are the creation options of the driver, each KEY=VALUE; srid is the spatial reference system the file states, the raster's own when NULL, the pixels not being reprojected. A GeoTIFF holds one pixel type and one nodata value for all its bands, PNG one to four bands of 8 or 16 unsigned bits, JPEG one or three bands of 8 unsigned bits; a format refusing the bands of the raster raises an error.
WITH r AS (
SELECT ST_SetValues(ST_AddBand(ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0,
0.0, 0.0, 4326), '8BUI'::text, 0::float8, NULL::float8), 1, 1, 1,
ARRAY[[1, 2], [3, 4]]::double precision[][]) AS rast)
SELECT asTIFF(rast) = ST_AsTIFF(rast),
encode(substring(asPNG(rast) from 2 for 3), 'escape')
FROM r;
-- true | PNG
Return a raster read from the bytes of a file in a format of GDAL
rasterFromGDALRaster(gdaldata bytea,srid integer=0) → raster
Reads the raster as PostGIS's ST_FromGDALRaster reads it, the format found by GDAL from the bytes. srid is the spatial reference system of the result, the one the file states when 0.
WITH r AS (
SELECT ST_AddBand(ST_MakeEmptyRaster(2, 2, 0.0, 2.0, 1.0, -1.0, 0.0, 0.0,
3857), '16BSI'::text, 7::float8, 0::float8) AS rast)
SELECT SRID(rasterFromGDALRaster(asTIFF(rast))),
SRID(rasterFromGDALRaster(asTIFF(rast), 4326))
FROM r;
-- 3857 | 4326