Table of Contents
The temporal H3 cell index type th3index represents the movement of objects as a sequence of H3 hexagonal cell identifiers. Uber's H3 is a discrete global grid system that tessellates the sphere into 122 base cells at resolution 0 and subdivides each cell into 7 children at every finer resolution (up to 15). Each cell has a 64-bit integer identifier that encodes the base cell, resolution, and hierarchical position.
The th3index type shares its on-disk representation with tbigint: both store a temporal 64-bit integer. The distinct SQL type exists purely to let the type-checker reject h3index-specific functions when applied to arbitrary bigint trajectories (and vice-versa). Casts to / from tbigint are explicit (AS ASSIGNMENT) and binary-coercion only, zero-cost at runtime but requiring an explicit :: in SQL.
As with other temporal types, th3index has four subtypes: Instant, discrete-sequence, continuous-sequence with step interpolation (H3 cells are discrete, linear interpolation is meaningless), and SequenceSet.
A th3index value is entered using the standard temporal syntax with the underlying 64-bit H3 cell identifier. The input parser accepts the canonical hexadecimal form (as produced by the H3 CLI and by h3_cell_to_string upstream), with an optional 0x prefix and at most 16 significant digits. The value must also denote something in the H3 indexing system, that is, 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. Hexadecimal is idiomatic in the H3 ecosystem and is what the output function emits, all examples below use it.
SELECT th3index '831c02fffffffff@2001-01-01';
SELECT th3index '{831c02fffffffff@2001-01-01, 831c00fffffffff@2001-01-02}';
SELECT th3index '[831c02fffffffff@2001-01-01, 831c00fffffffff@2001-01-02]';
SELECT th3index '{[831c02fffffffff@2001-01-01, 831c00fffffffff@2001-01-02],
[880326b885fffff@2001-01-03, 880326b88dfffff@2001-01-04]}';
The type accepts type modifiers (typmod) to constrain a column to a specific subtype: Instant, Sequence, or SequenceSet.
SELECT th3index(Instant) '831c02fffffffff@2001-01-01';
SELECT th3index(SequenceSet) '{[831c02fffffffff@2001-01-01, 831c00fffffffff@2001-01-02]}';
-- ERROR: temporal type (Instant) does not match column type (Sequence)
SELECT th3index(Sequence) '831c02fffffffff@2001-01-01';
The functions asText, asBinary, and asHexWKB serialize a th3index value, and th3indexFromBinary and th3indexFromHexWKB reconstruct it. The hexadecimal WKB form lets a temporal H3 index column round-trip through a text-based COPY.
SELECT asHexWKB(th3index '831c02fffffffff@2001-01-01'); SELECT th3indexFromHexWKB(asHexWKB(th3index '831c02fffffffff@2001-01-01')); -- 831c02fffffffff@2001-01-01