Aggregating point data to hex bins with dbt

This page builds the model most projects want first from a grid key: point events rolled up into hexagonal bins, incrementally, at more than one resolution, with the normalisation and suppression rules that keep the resulting map honest.

When to use this approach

  • You need a density surface, not individual points. A million pings on a map is a blob; the same data in hexagons is readable and a hundred times smaller.
  • Aggregation must be cheap and repeatable. Grouping by a cell id is a hash aggregate the warehouse does well, with no geometry function in the hot path.
  • The output feeds a map. Hex bins are a serving artifact as much as an analytical one, and they inherit the payload constraints in serving spatial data to consumers.

Prerequisites

  • A cell id already attached in staging, via the macros in writing H3 index macros for dbt models.
  • A resolution decided from occupancy, not from a default.
  • A denominator for any rate you intend to show — population, road length, exposure hours — or the map will show where people are rather than where the phenomenon is.
  • A privacy threshold if the points relate to individuals, per masking precise coordinates for privacy.

Step-by-step instructions

1. Roll up by cell and day

sql
-- models/marts/mart_ping_hex_daily.sql
{{ config(
    materialized = 'incremental',
    unique_key = ['activity_date', 'h3_cell'],
    incremental_strategy = 'delete+insert',
    cluster_by = ['h3_cell']
) }}

select
    date(observed_at)          as activity_date,
    h3_cell,
    count(*)                   as ping_count,
    count(distinct trip_id)    as trip_count,
    avg(speed_kph)             as avg_speed_kph
from {{ ref('stg_trip_pings') }}
{% if is_incremental() %}
where date(observed_at) >= (select max(activity_date) - 2 from {{ this }})
{% endif %}
group by 1, 2

The two-day lookback with delete+insert handles late-arriving pings without duplicating rows: the affected days are rebuilt in full rather than appended to. Keeping the daily grain, rather than aggregating straight to a total, is what lets every downstream question — a week, a month, a weekday-only view — be answered from the same model.

Verify the aggregate did not lose or gain events:

sql
select
    (select count(*) from {{ ref('stg_trip_pings') }}) as source_rows,
    (select sum(ping_count) from {{ ref('mart_ping_hex_daily') }}) as binned_rows;
-- Expect equality on a full refresh
From scattered points to a cell-keyed daily aggregate On the left, scattered point events over a hexagonal grid, unreadable as a cloud. In the middle, the same points counted per cell, with cells shaded by count. On the right, the resulting table of three columns — activity date, cell id and count — noted as roughly a thousandth of the rows of the source and safe to send to a client. raw points counted per cell 3 4 2 4 mart_ping_hex_daily activity_date h3_cell ping_count Three columns, a thousandth of the rows, and no geometry in the payload at all.

2. Normalise before anyone draws it

A raw count map shows where the data is dense, which is usually where people are. If the question is “where is this phenomenon unusually common”, the count needs a denominator.

sql
-- models/marts/mart_ping_hex_rates.sql
select
    a.activity_date,
    a.h3_cell,
    a.ping_count,
    d.population,
    case
        when d.population >= {{ var('min_denominator', 50) }}
        then round(a.ping_count::numeric / d.population * 1000, 2)
    end as pings_per_1000_people
from {{ ref('mart_ping_hex_daily') }} a
left join {{ ref('dim_hex_population') }} d using (h3_cell)

The case guard is not decoration. A cell with three residents and two events produces a rate of 667 per thousand, which will dominate any colour scale and is noise rather than signal. Suppressing rates below a denominator floor is the difference between a map that informs and a map whose brightest cells are all artefacts.

Verify the suppression is doing something and not everything:

sql
select
    count(*) filter (where pings_per_1000_people is null) as suppressed,
    count(*) as total
from {{ ref('mart_ping_hex_rates') }};
-- A few per cent is healthy; a third means the resolution is too fine for the denominator

3. Build the resolution bands a map needs

One resolution cannot serve every zoom. Roll up the fine bins into coarser ones rather than re-aggregating the raw points, which is far cheaper.

sql
-- models/marts/mart_ping_hex_bands.sql
{% set bands = [
    {'name': 'overview', 'res': 5, 'min_zoom': 0,  'max_zoom': 7},
    {'name': 'regional', 'res': 7, 'min_zoom': 8,  'max_zoom': 10},
    {'name': 'local',    'res': 9, 'min_zoom': 11, 'max_zoom': 16}
] %}

{% for b in bands %}
select
    '{{ b.name }}'      as band_name,
    {{ b.min_zoom }}    as min_zoom,
    {{ b.max_zoom }}    as max_zoom,
    activity_date,
    {{ h3_parent('h3_cell', b.res) }} as h3_cell,
    sum(ping_count)     as ping_count
from {{ ref('mart_ping_hex_daily') }}
group by 1, 2, 3, 4, 5
{% if not loop.last %}union all{% endif %}
{% endfor %}

Because H3 resolutions do not nest exactly, h3_parent assigns each fine cell to the coarse cell containing its centre — an approximation that is invisible in an aggregate and would not be acceptable in an exact assignment. That distinction is drawn out in choosing between H3, S2 and geohash in dbt.

Verify each band preserves the total:

sql
select band_name, sum(ping_count) from {{ ref('mart_ping_hex_bands') }} group by 1;
-- Every band should sum to the same total

4. Attach geometry only at the edge

sql
-- models/serving/serve_hex_bins.sql
select
    band_name, min_zoom, max_zoom, activity_date, h3_cell, ping_count,
    {{ h3_cell_boundary('h3_cell') }} as geom
from {{ ref('mart_ping_hex_bands') }}
where activity_date >= current_date - interval '30 days'

Keeping geometry out of the analytical models and adding it in one serving model is what keeps the aggregates small enough to scan cheaply; the boundary is derivable from the cell id at any time, so storing it upstream buys nothing.

Verify the serving model is the size you expect, and that the analytical ones stayed small:

sql
select pg_size_pretty(pg_total_relation_size('analytics.mart_ping_hex_daily')) as analytical,
       pg_size_pretty(pg_total_relation_size('serving.serve_hex_bins'))        as serving;
The same data as raw counts and as a suppressed rate Two small hex maps side by side. The raw count map is brightest in the city centre, reproducing population density. The rate map, events per thousand residents with a minimum denominator, shifts the bright cells to two outlying areas and greys out four sparsely populated cells whose rates would otherwise be extreme. A caption notes that the two maps answer different questions and only one answers the question usually asked. raw counts — shows where people are rate per 1,000 residents — shows the phenomenon dashed = suppressed, denominator too small Same events, different question — and only the right-hand map answers the one usually asked. A linear colour scale against a quantile scale on the same heavy-tailed counts Two strips of cells shaded from the same count data. On the linear scale nearly every cell is at the pale end because one cell holds an extreme value, so the map appears empty. On the quantile scale the same cells spread evenly across the ramp and the structure becomes visible. A note records that the underlying numbers are identical and only the class breaks changed. linear scale · one outlier flattens everything one cell holds 94% of the range quantile scale · the same numbers, classed by rank structure becomes readable Say which classing the legend uses — the two maps support different claims.

Configuration reference

Parameter Where Typical value Note
grid_resolution project var 8–9 for city data Aim for tens of events per cell, not thousands
lookback window incremental filter 2–3 days Must cover the feed’s late-arrival tail
incremental_strategy model config delete+insert Rebuilds affected days rather than appending
min_denominator project var 50 Suppresses rates whose denominator is too small to mean anything
band resolutions band list 5 / 7 / 9 Roll up from the fine bin, never from the raw points
retention in serving serving model filter 30 days Serving carries what a map shows, not the archive

Gotchas & edge cases

  • A count map is a population map. Almost every “surprising hotspot” in an unnormalised hex map is a shopping centre or a station. Normalise, or label the map as a count map explicitly.
  • Empty cells are not zero cells. A cell with no rows is absent, and a client rendering absence as zero will draw a hole rather than a gap. Decide which you mean and encode it.
  • Colour scales lie under skew. Event counts are usually heavy-tailed, so a linear scale renders everything but the top cell as background. Bucket by quantile and say so in the legend.
  • Small counts identify people. Where events relate to individuals, suppress cells below a count threshold as well as a denominator threshold.
  • Resolution and zoom must agree. Bins coarser than the display scale look blocky; bins finer than it produce moiré and huge payloads.

FAQ

Should I aggregate to hexagons or to administrative areas?

Both, for different audiences. Hexagons are uniform, so they compare fairly and reveal patterns that boundaries cut across; administrative areas are what people act on, because budgets and responsibilities follow them. The usual answer is to aggregate to hexagons for analysis and to areas for reporting, from the same event-level model.

How do I keep the incremental rollup correct when events arrive late?

Use a lookback window that exceeds the feed’s observed tail and a delete+insert strategy so affected days are rebuilt rather than appended. Measure the tail rather than assuming it — the freshness instrumentation in configuring dbt source freshness for spatial feeds gives the number.

Can I store the hexagon geometry to save computing it?

You can, but it costs storage on every row for a value derivable from the key at any moment. Compute it in the serving model only. The exception is an export destined for a tool that cannot compute cell boundaries itself, where materialising once is cheaper than making every consumer solve it.

What if my events are lines or polygons rather than points?

Bin by a representative point — the centroid, or the start point for trips — and say which in the model description, or distribute the measure across every cell the feature touches when the quantity is genuinely spread along it, such as road length. The two answers differ, and choosing silently is how two dashboards end up disagreeing.

Up: Part of Discrete Global Grid Macros.