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.
Operational guidance on when to choose th3index over tgeompoint or tgeogpoint; the H3-specific hazards consumers should anticipate (int64 ordering arbitrary with respect to grid geometry, resolution mixing in operations, pentagon cells, compaction round-trip cell-order non-preservation, antimeridian and pole behaviour at high resolution); and the durability and storage conventions are collected in the section called “Operational notes”.
A th3index value is entered using the standard temporal syntax with the underlying 64-bit H3 cell identifier. The input parser accepts both the canonical hexadecimal form (as produced by the H3 CLI and by h3_cell_to_string upstream) and the equivalent unsigned decimal. Hexadecimal is idiomatic in the H3 ecosystem and is what the output function emits — all examples below use it. For reference, the hex value 831c02fffffffff used in the examples corresponds to the decimal value 590464338553208831.
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 function arrowRoundtrip converts a temporal H3 index to the Arrow C Data Interface and reconstructs it, returning a value equal to the input.
SELECT arrowRoundtrip(th3index '831c02fffffffff@2001-01-01'); -- 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