H3 Hexagon Binning in DuckDB

Aggregating points into Uber’s H3 hexagons inside DuckDB requires the separate h3 community extension, and the two failures that dominate real workloads are a reversed lat/lng axis order and feeding projected coordinates into functions that expect WGS84 — both silent. This walkthrough sits under Spatial Clustering and Grid Binning and isolates the H3 path specifically: installing the extension, converting points to cells with h3_latlng_to_cell, aggregating by cell, choosing a resolution, and reconstructing hexagon boundaries with h3_cell_to_boundary_wkt for rendering — with the validation queries that catch the axis-order and CRS traps before they ship.

Root-Cause Analysis of Binning Failures #

H3 binning breaks along four distinct axes, and each has a different fix:

• Reversed axis order. h3_latlng_to_cell(lat, lng, res) takes latitude first, but DuckDB Spatial’s ST_X returns longitude and ST_Y returns latitude. Writing h3_latlng_to_cell(ST_X(geom), ST_Y(geom), r) swaps the two, and because the transposed coordinate is still a valid location on the globe the function returns a perfectly valid — and completely wrong — cell. Nothing errors.
• Non-WGS84 input. H3 is defined on a spherical model of the Earth in geographic degrees. Passing Web Mercator metres (EPSG:3857) or any projected coordinate produces cells that are numerically valid but geographically nonsensical. The input must be lon/lat degrees (EPSG:4326).
• Resolution mismatch. Binning at too fine a resolution yields one point per cell and defeats the aggregation; too coarse and every point collapses into a handful of cells. Resolution is the single knob that decides whether the output is useful.
• Extension not loaded. h3 is a community extension shipped separately from spatial. If it is not installed and loaded, every h3_ function fails to resolve — a hard error, but one that surprises teams who assume it comes bundled with the spatial extension.

Deterministic Configuration #

Load both extensions explicitly. spatial supplies ST_X/ST_Y and geometry parsing; h3 supplies the hexagon functions. They are independent installs.

INSTALL spatial;
LOAD spatial;

INSTALL h3 FROM community;   -- community extension: separate download, NOT bundled with spatial
LOAD h3;                     -- per-session; h3_latlng_to_cell resolves only after this

-- Match physical cores. H3 encoding is a cheap per-row scalar, so the aggregation is
-- hash-build bound; oversubscribing threads adds contention without adding throughput.
SET threads = 8;

-- The cell key is a small UBIGINT, so the group-by hash table stays compact. This ceiling
-- mainly guards the input scan; too low forces a needless spill on a wide source table.
SET memory_limit = '8GB';

Confirm the preconditions before binning:

• spatial and h3 both installed and loaded in the session.
• Point geometry is WGS84 lon/lat (EPSG:4326) — verify the SRID, do not assume it.
• A target resolution chosen from the analytical scale, not copied from an example.

Optimized Execution Pattern #

The core conversion is one scalar call per row, then a single hash aggregation on the resulting cell key. The before/after below is not about speed — both forms run at the same rate — but about correctness: the axis order.

-- BEFORE (silently wrong): ST_X is longitude, ST_Y is latitude, so this passes
-- longitude where latitude belongs and mislocates every single point.
SELECT
    h3_latlng_to_cell(ST_X(geom), ST_Y(geom), 8) AS h3_cell,
    COUNT(*) AS point_count
FROM rides
GROUP BY h3_cell;

-- AFTER (correct): latitude (ST_Y) first, longitude (ST_X) second, at resolution 8.
SELECT
    h3_latlng_to_cell(ST_Y(geom), ST_X(geom), 8) AS h3_cell,   -- lat, lng, res
    COUNT(*)       AS point_count,
    AVG(fare)      AS mean_fare
FROM rides
GROUP BY h3_cell;

The behavioural change is entirely in argument order: ST_Y (latitude) moves to the first position, ST_X (longitude) to the second. Because the key is a compact UBIGINT, this resolves to a HASH_GROUP_BY over a scalar — the same efficient shape described in the spatial clustering and grid binning guide, and the same key-then-aggregate discipline detailed in the vectorized aggregations reference. The encode itself is a per-row scalar in the projection beneath the group, so it vectorizes cleanly and never becomes the bottleneck; the cost is dominated by the scan and the hash build, exactly as it would be for an integer grid key.

There is no separate “assign then aggregate” step to materialize — DuckDB fuses the h3_latlng_to_cell projection into the group-by input, so the cell id is computed on the fly per vector and never lands in an intermediate table. If a pipeline needs the raw per-point cell id for a later join, materialize it explicitly as a stored column; otherwise let the planner keep it ephemeral.

Why hexagons beat squares #

A square grid has two neighbour classes: four edge-adjacent cells and four diagonal ones that sit $\sqrt{2}$ times farther from the centre. Any distance-weighted or spread metric computed on a square lattice is therefore biased by direction — a hot spot leaking into a diagonal neighbour is understated relative to one leaking across an edge. Hexagons have a single neighbour class: all six neighbours are equidistant from the centre, so a diffusion, flow, or adjacency calculation treats every direction the same. Hexagons also tessellate the plane with no gaps and no overlaps, whereas circles would leave gaps and squares carry the diagonal distortion. H3 additionally keeps cells close to equal-area within a resolution, so a raw count per cell is directly comparable across the map rather than distorted by latitude the way a lon/lat degree grid is, where a “1 degree” cell shrinks from roughly 111 km wide at the equator to almost nothing near the poles.

H3 is also hierarchical: every cell has exactly one parent at the next coarser resolution and (approximately) seven children at the next finer one. That containment is what makes H3 a good cross-source join key — two datasets binned to the same resolution share an exact integer key, and either can be rolled up to a coarser resolution without touching the original geometry. The hierarchy is approximate rather than perfectly nested because hexagons cannot tile hexagons exactly, but for aggregation and joins the parent/child relationship is stable and cheap.

Resolution selection #

Resolution is the trade-off between spatial precision and cell population. Coarser cells hold more points and smooth the surface; finer cells localize but fragment. Pick from the analytical scale:

Resolutions run 0 (coarsest, ~4.3 million km² per cell) to 15 (finest, sub-metre). Start one level coarser than feels right: it is cheap to refine a query to a finer resolution, but a fragmented surface at too fine a level hides the pattern you were binning to reveal. A useful sanity check is the ratio of distinct cells to input rows — a healthy density map has many points per cell, so if that ratio approaches one the resolution is too fine for the data volume and the aggregation is doing no smoothing at all.

Because the hierarchy is stable, a common pattern is to bin once at the finest resolution the analysis will ever need, store the cell id, and derive coarser views on demand rather than re-binning the raw points each time. The fine-grained cell id is the durable artifact; coarser resolutions are a roll-up over it. This keeps the expensive point-to-cell encode a one-time cost and turns every subsequent zoom level into a cheap hash aggregation over an integer key.

Reconstructing hexagon boundaries #

Aggregation yields a cell id and a metric; to render the hexagon you need its polygon. h3_cell_to_boundary_wkt returns the cell outline as WKT, which ST_GeomFromText turns back into a GEOMETRY for GeoParquet export or a map layer.

-- Turn each aggregated cell key back into a drawable hexagon polygon.
WITH binned AS (
    SELECT
        h3_latlng_to_cell(ST_Y(geom), ST_X(geom), 8) AS h3_cell,
        COUNT(*) AS point_count
    FROM rides
    GROUP BY h3_cell
)
SELECT
    h3_cell,
    point_count,
    ST_GeomFromText(h3_cell_to_boundary_wkt(h3_cell)) AS hex_geom   -- WKT → GEOMETRY
FROM binned;

Diagnostic Queries & Plan Validation #

Validate the rewrite with EXPLAIN ANALYZE rather than trusting wall-clock time:

EXPLAIN ANALYZE
SELECT h3_latlng_to_cell(ST_Y(geom), ST_X(geom), 8) AS h3_cell, COUNT(*)
FROM rides
GROUP BY h3_cell;

Check three things in the plan:

• Group node type. You want a HASH_GROUP_BY over the scalar UBIGINT cell key. A sort-based group or a NESTED_LOOP_JOIN means the key is not being treated as scalar — recheck that you grouped on h3_cell and not on a geometry.
• Cell cardinality sanity. If the number of distinct cells approaches the number of input rows, the resolution is too fine and the aggregation is doing no work; step the resolution down.
• Operator timing. The H3 encode is a cheap per-row scalar in the projection; if total time is dominated by anything other than the scan and the hash build, look for an implicit cast in the key expression.

A one-liner to confirm the extension is live before trusting any of the above:

-- Verify the h3 extension is installed and loaded in this session.
SELECT extension_name, loaded, installed
FROM duckdb_extensions() WHERE extension_name = 'h3';

Geometry Validation & Axis-Order Guards #

The axis-order trap is silent, so guard against it with a data check rather than hoping the code is right. WGS84 longitude is bounded to ±180 and latitude to ±90; if a column swaps them, latitudes above 90 appear the moment any point sits east of 90° longitude.

-- Catch reversed or non-WGS84 coordinates before binning: flag out-of-range lon/lat.
SELECT COUNT(*) AS bad_coords
FROM rides
WHERE ST_X(geom) NOT BETWEEN -180 AND 180   -- longitude range
   OR ST_Y(geom) NOT BETWEEN  -90 AND  90;   -- latitude range

Confirm the source is genuinely WGS84 and reproject if it is not — H3 has no concept of a projected CRS, so the fix is always to transform the input, following the rules in the CRS mapping and transformations reference:

-- If the SRID is not 4326, reproject before binning; H3 requires WGS84 lon/lat.
SELECT DISTINCT ST_SRID(geom) AS srid FROM rides;          -- expect 4326
-- ST_Transform(geom, 4326) up front when it is not.

Finally, invalid or empty geometry produces NULL coordinates that yield a NULL cell and quietly drop from the grouped output. Filter or repair before binning so the counts are complete:

-- Exclude null/empty geometry so it does not silently vanish from the cell counts.
SELECT COUNT(*) AS null_or_empty
FROM rides
WHERE geom IS NULL OR ST_IsEmpty(geom);

Related #

See also

• Spatial clustering and grid binning — how hexagon binning compares to square grids, DBSCAN, and geohash bucketing.
• Vectorized aggregations — the scalar-key hash-aggregation mechanics the H3 pattern relies on.

Up: Spatial Clustering and Grid Binning · Modern Spatial SQL Query Patterns