Discrete Global Grid Macros

A discrete global grid turns a coordinate into a string or an integer. That single change makes almost everything in a warehouse easier: a spatial join becomes an equality join, a partition key becomes obvious, a set of points becomes groupable without any geometry function at all, and the whole thing works on engines that have no spatial index to offer. It also introduces a specific class of error — cells have edges, features straddle them, and a naive grid join quietly loses the features that sit on a boundary.

This topic covers grid indexing as a dbt concern: which grid system to pick, how to wrap the cell functions in macros so a project stays portable, how to choose a resolution from the data rather than from a default, and how to write the join so boundary cases stay correct. It pairs with the predicate work in spatial joins and predicate tuning, which is where the geometry-first version of the same problem lives.

Prerequisites checklist

• A grid implementation on the target engine: the h3 PostgreSQL extension, BigQuery’s S2_CELLIDFROMPOINT, Snowflake’s H3_POINT_TO_CELL, or DuckDB’s H3 community extension.
• A canonical SRID of 4326 before indexing. Every grid system takes longitude and latitude; feeding it projected coordinates produces cell ids that are silently wrong.
• A decision on resolution, made from measured cell occupancy rather than copied from a tutorial.
• The macro dispatch pattern already in place, as described in cross-engine UDF portability — grid function names differ on every engine.
• A test dataset with features on cell boundaries. Without one, the boundary bug ships.

Architecture context: where the grid key enters the DAG

The important structural point is that the grid does not replace the geometry test — it reduces how many rows need one. A join written as “match on cell, then verify with ST_Intersects only where the cell straddles a boundary” is both faster and exactly as correct as the geometry-only version. A join written as “match on cell” alone is faster and slightly wrong, which is fine for a heatmap and not fine for billing.

Choosing a grid

The practical decision is usually made for you by the engine. Where it is not, H3’s uniform neighbour distance is what makes it the default choice for anything involving proximity or aggregation — a hexagon’s six neighbours are all the same distance away, so “cells within one ring” means a consistent radius, which is not true for squares. S2 wins where prefix truncation matters, since a coarser cell is a prefix of a finer one, and geohash wins only where you need something every tool understands with no extension at all.

Configuration walkthrough

Wrap the functions before writing a single model against them. Grid function names differ on every engine and change between extension versions.

-- macros/grid/cell_for_point.sql
{% macro cell_for_point(geom, resolution) %}
  {{ return(adapter.dispatch('cell_for_point', 'spatial')(geom, resolution)) }}
{% endmacro %}

{% macro postgres__cell_for_point(geom, resolution) %}
  h3_lat_lng_to_cell({{ geom }}::point, {{ resolution }})
{% endmacro %}

{% macro snowflake__cell_for_point(geom, resolution) %}
  h3_point_to_cell_string({{ geom }}, {{ resolution }})
{% endmacro %}

{% macro duckdb__cell_for_point(geom, resolution) %}
  h3_latlng_to_cell(st_y({{ geom }}), st_x({{ geom }}), {{ resolution }})
{% endmacro %}

{% macro bigquery__cell_for_point(geom, resolution) %}
  s2_cellidfrompoint({{ geom }}, {{ resolution }})
{% endmacro %}

Set the resolution once, as a project var, so a change is one edit and every model agrees:

vars:
  grid_resolution: 9        # H3 r9 ≈ 0.1 km² per cell
  grid_resolution_coarse: 6 # for partitioning and rollups

Then staging attaches the key:

-- models/staging/stg_trip_pings.sql
select
    ping_id,
    trip_id,
    observed_at,
    geom,
    {{ cell_for_point('geom', var('grid_resolution')) }} as h3_cell,
    {{ cell_for_point('geom', var('grid_resolution_coarse')) }} as h3_cell_coarse
from {{ source('ops', 'trip_pings') }}

Picking a resolution from the data

Resolution is the one parameter that matters and the one most often copied from an example. Choose it by measuring occupancy: too coarse and every cell holds thousands of features so the join barely filters; too fine and the cell table for a polygon explodes.

-- analyses/cell_occupancy_by_resolution.sql
{% for r in [6, 7, 8, 9, 10] %}
select
    {{ r }} as resolution,
    count(distinct {{ cell_for_point('geom', r) }}) as cells,
    round(count(*)::numeric / count(distinct {{ cell_for_point('geom', r) }}), 1) as avg_features_per_cell
from {{ ref('stg_trip_pings') }}
{% if not loop.last %}union all{% endif %}
{% endfor %}

A rule of thumb worth having, then discarding once you have measured: for point data, aim for tens of features per cell, not thousands and not one. For polygon coverings, aim for a few hundred cells per polygon at most; beyond that the covering table costs more than the join saves.

Validation and testing

Grid keys are strings or integers, so the tests are ordinary — which is much of their appeal.

models:
  - name: stg_trip_pings
    columns:
      - name: h3_cell
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "length(h3_cell) = 15"   # H3 r9 string ids

The test that actually matters compares the grid join against the geometry join on a sample, because that is the assertion that the optimisation did not change the answer:

-- tests/assert_grid_join_matches_geometry_join.sql
with grid_result as (
    select p.ping_id, z.zone_id
    from {{ ref('stg_trip_pings') }} p
    join {{ ref('stg_zone_cells') }} z using (h3_cell)
    where p.ping_id in (select ping_id from {{ ref('sample_pings') }})
),
geometry_result as (
    select p.ping_id, z.zone_id
    from {{ ref('stg_trip_pings') }} p
    join {{ ref('stg_zones') }} z on st_intersects(p.geom, z.geom)
    where p.ping_id in (select ping_id from {{ ref('sample_pings') }})
)
select 'grid_only' as side, * from (select * from grid_result except select * from geometry_result) a
union all
select 'geometry_only', * from (select * from geometry_result except select * from grid_result) b

Rows in the geometry_only side are features the grid join lost — almost always boundary cases. Rows in grid_only are features it gained, which happens when a cell overlaps a zone the feature is not actually in. Both are expected in small numbers before the boundary handling is added, and both should be zero afterwards.

Building the covering, and keeping it honest

The polygon side needs a set of cells rather than one, and how that set is built decides whether the join over- or under-counts. Every grid library offers at least two coverings: one that returns cells whose centre falls inside the polygon, and one that returns every cell the polygon touches at all. The first under-covers — a thin sliver along the edge is lost — and the second over-covers, admitting cells that barely graze the shape.

-- models/staging/stg_zone_cells.sql
{{ config(materialized = 'table', cluster_by = ['h3_cell']) }}

select
    z.zone_id,
    c.h3_cell,
    -- an interior cell needs no further test; an edge cell does
    st_contains(z.geom, {{ cell_boundary('c.h3_cell') }}) as is_interior_cell
from {{ ref('stg_zones') }} as z
cross join lateral {{ polygon_to_cells('z.geom', var('grid_resolution')) }} as c(h3_cell)

Carrying is_interior_cell is what makes the two-stage join possible: rows matching an interior cell are final, and only rows matching an edge cell go through ST_Intersects. On typical administrative data, interior cells account for the large majority of matches, so the exact test runs on a small fraction of the volume while the answer stays identical to a pure geometry join.

The cost to watch is the size of the covering table itself. One zone at a fine resolution can produce tens of thousands of cells, and a country’s worth of zones can produce a covering larger than the geometry it came from. Two mitigations are usually enough: build the covering at a coarser resolution than the point index and accept a larger share of edge cells, or compact the covering into mixed resolutions, which every mature H3 and S2 implementation supports — a single coarse cell replaces its seven or four children when all of them are present.

Advanced patterns

Use a coarse cell as a partition key and a fine cell as a join key. Partitioning by r6 and joining on r9 gives pruning and selectivity from the same family of keys; the partitioning side is developed in partitioning geospatial tables with H3 and dbt.

Store the cell, not the geometry, in aggregate marts. A heatmap mart keyed by cell with a count is tiny, indexes trivially, and renders directly — the cell boundary can be reconstructed on demand for display.

Use k-ring expansion for proximity questions. “Everything within roughly 500 m” becomes “cells within k rings of this cell”, which is an IN list against an indexed column rather than a distance computation.

Keep the geometry column. The grid is an accelerator, not a replacement; the moment someone needs an exact area or a precise boundary, the geometry has to be there. Dropping it to save space is a decision people regret at the next requirement.

Watch for the pentagons. H3 has twelve pentagonal cells where the icosahedron’s vertices land. They break the “six neighbours” assumption, and code that hard-codes six will misbehave over them — rare in practice, since they sit mostly in ocean, but worth a comment in any macro that walks neighbours.

Where grid keys stop helping

It is worth naming the cases where the whole approach is the wrong tool, because a grid key is easy to add and hard to remove once models depend on it. Long linear features are the clearest example: a motorway or a river crosses hundreds of cells at any useful resolution, so its covering is large, the join fans out across it, and the deduplication afterwards costs more than a geometry predicate would have. Nearest-neighbour work is the second: k-ring expansion finds candidates but says nothing about ordering, so a query for the five closest depots still needs a distance calculation, and on PostGIS the KNN operator does that better on its own.

The third case is subtler. Where the analysis is about the shapes themselves — overlap area, shared boundary length, containment depth — the grid can only ever be a prefilter, because those quantities are properties of the geometry and cells cannot approximate them. Adding cell keys to such a pipeline buys a faster candidate set and nothing else, which is worth having only if the candidate set was the bottleneck.

Troubleshooting

FAQ

Related

• Writing H3 Index Macros for dbt Models — the macro layer in full, with tests.
• Choosing Between H3, S2 and Geohash in dbt — the decision, with the trade-offs measured.
• Aggregating Point Data to Hex Bins with dbt — the most common application.
• Partitioning Geospatial Tables with H3 and dbt — the same key used for physical layout.

Up: Part of Advanced Spatial Macros & UDF Patterns.