Aggregating Points to H3 Cells in SQL

This guide computes counts and statistics per grid cell from a large point table using plain SQL, with the derived-column layout that makes it fast and the rollup that produces every coarser resolution for free.

Context and prerequisites

Point-to-cell aggregation is the most common spatial query on a lakehouse, and it should not involve geometry at all. This recipe runs on Trino or Spark SQL against a table carrying a materialised cell identifier; the layout it assumes is described in spatial aggregation and tiling, and the derivation itself in materializing bbox columns for pushdown.

The query that does no spatial work

Complete working solution

-- Trino / Spark SQL. Counts and statistics per cell for one day, one window.
SELECT
    h3_r8                                   AS cell_id,
    count(*)                                AS event_count,
    count(DISTINCT asset_id)                AS distinct_assets,
    sum(duration_s)                         AS total_duration_s,
    sum(duration_s) / count(*)              AS mean_duration_s,
    approx_percentile(speed_kmh, 0.95)      AS p95_speed_kmh
FROM lakehouse.spatial.telemetry
WHERE event_day = DATE '2026-03-11'
  AND bbox_min_x >= 13.0 AND bbox_max_x <= 13.8      -- prunes files
  AND bbox_min_y >= 52.3 AND bbox_max_y <= 52.7
GROUP BY h3_r8;

-- The rollup: every coarser level from the finest, without touching the facts again.
CREATE TABLE summary.telemetry_cells AS
WITH r8 AS (
  SELECT event_day, h3_r8 AS cell_id, 8 AS resolution,
         count(*) AS event_count, sum(duration_s) AS total_duration_s
  FROM lakehouse.spatial.telemetry
  WHERE event_day = DATE '2026-03-11'
  GROUP BY event_day, h3_r8
),
r6 AS (
  SELECT event_day, h3_cell_to_parent(cell_id, 6) AS cell_id, 6 AS resolution,
         sum(event_count) AS event_count, sum(total_duration_s) AS total_duration_s
  FROM r8 GROUP BY event_day, h3_cell_to_parent(cell_id, 6)
),
r4 AS (
  SELECT event_day, h3_cell_to_parent(cell_id, 4) AS cell_id, 4 AS resolution,
         sum(event_count) AS event_count, sum(total_duration_s) AS total_duration_s
  FROM r6 GROUP BY event_day, h3_cell_to_parent(cell_id, 4)
)
SELECT * FROM r8 UNION ALL SELECT * FROM r6 UNION ALL SELECT * FROM r4;

Step-by-step walkthrough

1. Filter on the bounding-box columns, not on geometry. The four comparisons prune files before any data is read; an ST_Intersects against a window would read everything and filter afterwards.

2. Group on the integer cell column. This is what makes the aggregation ordinary. No spatial extension is loaded, no geometry is decoded, and the engine’s normal hash aggregation applies.

3. Store sum and count, not the mean. The mean is computed at read time from the two, which is what allows a coarser level to be rolled up correctly. Storing the mean makes the rollup impossible without returning to the facts.

4. Roll up through the parent relation. Each coarser level aggregates the level above it rather than the raw table, so the expensive pass happens once. The parent function is a bit-manipulation on the identifier and costs nothing.

5. Keep the resolution as a column. One summary table holding several resolutions is easier to serve and to refresh than one table per level, and the resolution column makes the query trivial.

Common errors and fixes

Distinct counts, done correctly

-- Store the sketch alongside the count so both roll up.
SELECT event_day, h3_r8 AS cell_id,
       count(*)                              AS event_count,
       approx_set(asset_id)                  AS asset_sketch     -- Trino HLL
FROM lakehouse.spatial.telemetry
WHERE event_day = DATE '2026-03-11'
GROUP BY event_day, h3_r8;

-- Rolling up: merge the sketches, then estimate.
SELECT h3_cell_to_parent(cell_id, 6) AS cell_id,
       sum(event_count)              AS event_count,
       cardinality(merge(asset_sketch)) AS distinct_assets
FROM summary.telemetry_cells_r8
GROUP BY h3_cell_to_parent(cell_id, 6);

The approximation is typically within a percent or two, which is entirely adequate for a heatmap and inadequate for a billing figure. Where exactness is required at coarse levels, the honest answer is to recompute those levels from the facts rather than to roll up — the coarse aggregations are cheap precisely because there are few cells, so a second pass is affordable.

Verification

-- Rolled-up totals must equal the direct computation, within late-arrival tolerance.
WITH rolled AS (
  SELECT sum(event_count) AS n FROM summary.telemetry_cells
  WHERE resolution = 4 AND event_day = DATE '2026-03-11'
),
direct AS (
  SELECT count(*) AS n FROM lakehouse.spatial.telemetry
  WHERE event_day = DATE '2026-03-11'
)
SELECT rolled.n, direct.n, rolled.n - direct.n AS difference
FROM rolled CROSS JOIN direct;

A non-zero difference on a closed day is a defect rather than a rounding artefact: counts sum exactly, so any discrepancy means either a rollup bug or facts that arrived after the summary was computed. Distinguishing the two is a matter of re-running the summary and seeing whether the difference persists.

Run this reconciliation on a schedule for one representative day rather than for every day, and alert on a persistent difference. It costs one query and catches the entire class of silent divergence that makes precomputed summaries untrustworthy.

Weighted and Time-Bucketed Aggregations

The basic count generalises in two directions that cover most real dashboard requirements.

The time dimension is the one that needs restraint. Adding an hourly bucket multiplies the summary’s row count by twenty-four, and a summary at hourly × resolution 9 over a country is larger than the fact table it summarises. Choose the coarsest bucket the consumer genuinely uses — daily for most dashboards, hourly only where the diurnal pattern is the subject — and add finer buckets as separate, smaller-extent summaries if they are needed.

The weighted case is straightforward and has one trap: dividing by cell area to get a density must happen at read time, because a density does not roll up. Sum the weights, roll those up, and divide by the coarser cell’s area at the point of display. Storing the density directly makes every coarser level wrong.

Cost and Scaling

The aggregation’s cost is dominated by rows read rather than by the grouping, which makes it predictable and makes the optimisation obvious.

A GROUP BY on an integer over a pruned scan costs roughly what reading those two columns costs. On columnar storage, reading two narrow columns out of a table whose bulk is geometry is a small fraction of the table’s bytes — frequently under five percent — so a well-laid-out aggregation reads far less than its row count suggests.

The number that grows uncomfortably is the cardinality of the grouping key, because it determines the hash table size. A resolution-11 aggregation over a country produces hundreds of millions of groups, which spills on any single node and shuffles heavily on a cluster. Since a summary at that resolution is almost never displayed, the practical protection is to choose the resolution from the consumer rather than from the data — the same advice this topic gives everywhere, arriving here as a memory constraint rather than as a design preference.

Where a genuinely fine resolution is required over a large extent, the answer is to partition the aggregation by region and run it as several jobs rather than one, which bounds the hash table per job and parallelises cleanly.

Serving the Result

Once computed, the summary is a small table and the serving question is how a client asks for a region of it.

The natural interface takes a bounding box and a resolution, expands the box into cell identifiers at that resolution, and selects the matching rows. That expansion happens client-side or in a view; either way it turns a spatial request into an IN list against an integer column, which is as cheap as a lookup gets.

Two practical points. Cap the number of cells a single request may ask for, because a client that requests a continental extent at a fine resolution will ask for millions of cells and the IN list itself becomes the cost. Returning an error that names the limit is far better than attempting it, and it steers the client toward requesting a coarser resolution — which is what they should have asked for at that extent anyway.

And return the cell identifiers rather than geometries. A client that knows the grid system can compute the cell boundaries locally, which removes the geometry from the payload entirely and typically shrinks the response by an order of magnitude. Where the client cannot, offering a geometry-bearing variant as a separate endpoint keeps the fast path fast.

The full serving picture, including the precomputation decision and the freshness signalling, is in spatial aggregation and tiling.