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