DuckDB Spatial Analytical SQL for GIS

Quadkey and Geohash Binning in SQL

Geohash and quadkey turn a position into a sortable string whose prefixes nest exactly, which makes them excellent join keys and tile identifiers and poor units of area — a distinction that decides whether a density map is showing your data or showing the latitude. This walkthrough, part of the spatial clustering and grid binning reference, covers deriving both keys in SQL, using the prefix property deliberately, and normalising for the area distortion rather than ignoring it.

Root-Cause Analysis: what these keys are good at and what they are not

  • Prefixes nest exactly. A geohash of length five is contained by its own four-character prefix, and a quadkey of length twelve by its eleven-character one. That makes rolling up across resolutions exact rather than approximate, which hexagons cannot offer.
  • They sort into spatial locality. Lexicographic order over either key is a space-filling curve, so a sorted table has spatial locality for free — which is why they double as write-order keys.
  • Cells are equal in degrees, not in metres. A cell at 60° north covers roughly half the ground area of one on the equator. Any count-per-cell comparison across latitudes is therefore comparing different-sized areas.
  • Neighbouring cells are not always adjacent in the key. Both curves have discontinuities, so two adjacent cells can have keys that differ substantially. Neighbour lookup needs a real neighbour function, not a key increment.
  • String keys are heavier than integer ones. A VARCHAR key hashes and compares more slowly than an integer pair and makes the aggregation hash table larger, which matters at scale.

The distinguishing question is whether the key is being used as an identifier or as an area. As an identifier it is excellent; as an area it needs a normalisation step that most implementations omit.

Geohash and quadkey compared Both nest by prefix and both distort area with latitude; geohash is the common interchange key, quadkey aligns with the tile pyramid. PROPERTY GEOHASH QUADKEY alphabet base 32 base 4 cell shape by length alternates wide and tall always square in Mercator aligns with map tiles no yes, exactly common as an interchange key yes less so area distortion with latitude yes yes

The last row is shared, and it is the one that decides whether a density map is honest.

Deterministic Configuration 

INSTALL spatial; LOAD spatial;

SET memory_limit = '4GB';
SET threads = 8;

-- Both keys are defined on geographic degrees, so the input must be lon/lat
-- even if the rest of the pipeline works in a projected frame.

Optimized Execution Pattern

The pattern is to derive the key once into a stored column, aggregate on it, and normalise by cell area whenever counts are compared across latitudes.

-- ANTI-PATTERN: counts per cell, compared across a country. The northern
-- cells cover less ground, so they look denser for no reason but latitude.
SELECT substr(geohash, 1, 6) AS cell, count(*) AS n
FROM observations GROUP BY cell ORDER BY n DESC;
-- PATTERN: derive once, aggregate, then divide by the cell's actual ground
-- area so the comparison is a density rather than an artefact of latitude.
CREATE OR REPLACE TABLE obs_binned AS
SELECT *,
       ST_GeoHash(ST_Point(lon, lat), 6) AS cell6    -- ~1.2 km × 0.6 km at 50°N
FROM observations;

SELECT cell6,
       count(*)                                              AS n,
       ST_Area(ST_Transform(ST_GeomFromGeoHash(cell6),
                            'EPSG:4326', 'EPSG:3035')) / 1e6 AS cell_km2,
       count(*) / (ST_Area(ST_Transform(ST_GeomFromGeoHash(cell6),
                            'EPSG:4326', 'EPSG:3035')) / 1e6) AS per_km2
FROM obs_binned
GROUP BY cell6
ORDER BY per_km2 DESC;

The normalisation is one extra expression and it changes which cells appear at the top of the list. Skipping it produces a map whose brightest areas are wherever the data is furthest from the equator, which is a finding about the projection rather than about the phenomenon.

Geohash cell size by length, at 50 degrees north Four characters is regional, five is town-level, six is a neighbourhood, seven is street level, and each character multiplies the cell count by 32. LENGTH APPROX CELL (50°N) SUITS 4 ~40 × 20 km a regional summary 5 ~5 × 5 km a town-level view 6 ~1.2 × 0.6 km a neighbourhood 7 ~150 × 75 m street level

Each extra character multiplies the cell count by 32, so the choice moves fast.

Using the prefix property deliberately

The exact nesting is the property that makes these keys worth their distortion, and it is worth using rather than merely knowing about. Because a shorter prefix is a strict parent of every longer key beginning with it, one stored column at the finest resolution supports every coarser aggregation by substr. There is no second column, no re-derivation, and no approximation.

That also makes range queries over a region cheap: every key within a parent cell is lexicographically between the parent prefix and the parent prefix with the last character incremented, so a BETWEEN on a string is a spatial restriction. It is not a substitute for a real spatial index, because it only covers whole cells, but as a coarse pre-filter on a sorted table it is close to free.

-- One stored column, every resolution. And a prefix range is a spatial
-- restriction that a plain B-tree index can serve.
SELECT substr(cell7, 1, 5) AS cell5, count(*)
FROM obs_binned
WHERE cell7 >= 'gcpvj' AND cell7 < 'gcpvk'      -- everything inside one parent cell
GROUP BY cell5;

Diagnostic Queries & Plan Validation

The check that a binning is honest is whether cell area varies across the result, and it is one column.

-- If min and max cell area differ materially, raw counts are not comparable
-- across the result and a normalisation is required rather than optional.
SELECT min(cell_km2) AS smallest, max(cell_km2) AS largest,
       max(cell_km2) / min(cell_km2) AS ratio
FROM (SELECT ST_Area(ST_Transform(ST_GeomFromGeoHash(cell6),
                                  'EPSG:4326', 'EPSG:3035')) / 1e6 AS cell_km2
      FROM obs_binned GROUP BY cell6);

A ratio near one means the extent is small enough that the distortion does not matter. A ratio of two or more means the raw counts are comparing different-sized areas, and any ranking built on them is partly a ranking of latitude.

Three uses, and whether the distortion matters Irrelevant as a join key and as a tile identifier; decisive as a unit for density. USE DISTORTION MATTERS? NOTE a join key between datasets no equality only a tile identifier no the tiles are the output a unit for density yes, decisively normalise, or use hexagons

These keys are for the first two rows. The third is where they get misused.

Integer keys where you can have them

A geohash is conventionally a string, but the underlying value is a bit sequence, and where the pipeline controls both ends an integer representation hashes faster, compares faster and keeps the aggregation hash table substantially smaller. On a grouping over hundreds of millions of rows that is a real saving rather than a micro-optimisation.

The reason strings persist is interoperability: an external system that expects a geohash expects the base-32 text, and converting at the boundary is cheaper than converting everywhere. The workable arrangement is therefore to store the integer form internally and render the string only where it leaves the system — which is the same rule that applies to geometry itself, for the same reason.

Geometry Validation & Fallback Routing

Where the area distortion is unacceptable and normalisation is not enough, the fallback is a tessellation that is equal-area by construction.

-- An equal-area grid on a projected frame: cells are square in metres, so
-- counts are directly comparable without any normalisation.
SELECT floor(ST_X(geom_3035) / 1000)::INT AS cx,
       floor(ST_Y(geom_3035) / 1000)::INT AS cy,
       count(*)                            AS per_km2      -- 1 km cells, so already a density
FROM (SELECT ST_Transform(geom, 'EPSG:4326', 'EPSG:3035') AS geom_3035 FROM observations)
GROUP BY cx, cy;

Frequently Asked Questions

Are geohash cells equal in area?

No — they are equal in degrees, so a cell at 60° north covers roughly half the ground area of one on the equator. Counting per cell across a range of latitudes therefore compares different-sized areas, and any resulting density map partly shows the latitude rather than the phenomenon.

How does the prefix property help?

It makes rolling up across resolutions exact and free: one stored column at the finest resolution supports every coarser aggregation with substr, with no second column and no approximation. It also turns a parent cell into a lexicographic range, which a plain B-tree index can serve as a coarse spatial pre-filter.

Geohash or quadkey?

Quadkey when the output is a tile pyramid, because its cells align exactly with map tiles. Geohash when the key is an interchange identifier, because it is the more widely expected one. They share the same nesting behaviour and the same distortion, so the choice is about what consumes the key.

Can I find neighbouring cells by incrementing the key?

No. Both curves have discontinuities, so two adjacent cells can have keys that differ substantially — a neighbour lookup needs a real neighbour function that decodes and re-encodes. Incrementing the key finds the next cell along the curve, which is usually but not always adjacent.

What length should I use?

Whatever gives cells you are willing to treat as uniform for the question. Check the resulting counts: mostly-empty cells mean the length is too long to be a summary, and a handful of cells holding everything means it is too short. Each additional character multiplies the cell count by thirty-two, so the useful range is narrow.

Should the key be a string or an integer?

An integer internally, wherever both ends of the pipeline are yours — it hashes and compares faster and keeps the aggregation hash table smaller. Render the base-32 string only at the boundary where an external system expects it, which is the same rule that applies to geometry.

Up: Spatial Clustering and Grid Binning in DuckDB