Geospatial Analytics with ClickHouse — H3 Indexing Lab

---

A hands-on, end-to-end laboratory for geospatial analytics on ClickHouse, centered on H3 hexagonal indexing. Built around a synthetic 5 million-trip NYC ride-hailing dataset, it walks you through every geo primitive ClickHouse ships with — hex indexing, distance calculations, point-in-polygon, k-ring neighbor search, polygon-to-hex coverage, side-by-side comparison with Geohash and S2, and real-time aggregation via AggregatingMergeTree + Materialized Views.

🎯 Why this lab

ClickHouse is one of the very few OLAP databases that ships H3 (Uber's hexagonal spatial index), S2 (Google's quadrilateral index), and Geohash as first-class native functions. Mobility, IoT, retail-foot-traffic, and ad-tech teams already use all three at scale. This lab shows how to combine them with MATERIALIZED columns and AggregatingMergeTree to turn raw lat/lon points into queryable geo-analytic surfaces.

📊 Dataset

A synthetic NYC ride-hailing platform: - Trips: 5,000,000 (30 days, May 2026) - Hotspots: 15 named POIs (Times Square, JFK, LGA, …) used as cluster centers for weighted sampling - Service zones: 7 polygons (Midtown, Downtown, Williamsburg, JFK/LGA airports, etc.) - Each trip stores pickup_lon/lat, dropoff_lon/lat, plus MATERIALIZED H3 columns at res 6 / 8 / 10 - Distribution is realistic: pickups cluster around hotspots with σ ≈ 800 m jitter; rush-hour surge multipliers (1.5× morning, 2.0× evening); fares scale with great-circle distance

📁 File Structure

ch-geo-analytics/
├── README.md                     # This file
├── 01-schema.sql                 # trips + service_zones + hotspots tables
├── 02-load.sql                   # 5M synthetic trips with weighted hotspot sampling
├── 03-h3-basics.sql              # H3 fundamentals: encoding, hierarchy, k-ring, distance
├── 04-demand-heatmap.sql         # Multi-resolution hex aggregation, top-N, OD flows
├── 05-distance-and-routing.sql   # greatCircle / geo / h3PointDist / h3Line / hex steps
├── 06-spatial-search.sql         # K-ring, pointInPolygon, OD matrix, hybrid pre-filter
├── 07-indexing-systems.sql       # H3 vs Geohash vs S2 side-by-side
├── 08-materialized-views.sql     # AggregatingMergeTree for real-time demand aggregates
└── 99-cleanup.sql                # Drop everything

🚀 Quick Start

cd usecase/ch-geo-analytics

# Sequential — uses the running ClickHouse from your local setup
for f in 01-schema.sql 02-load.sql 03-h3-basics.sql \
         04-demand-heatmap.sql 05-distance-and-routing.sql \
         06-spatial-search.sql 07-indexing-systems.sql \
         08-materialized-views.sql; do
    echo "▶ Running $f"
    clickhouse-client --multiquery --queries-file "$f"
done

Or against a Docker container (e.g. the one from extensions/pg-clickhouse-lab in clickhouse-managed-postgres-hols):

for f in 01-schema.sql 02-load.sql 03-h3-basics.sql \
         04-demand-heatmap.sql 05-distance-and-routing.sql \
         06-spatial-search.sql 07-indexing-systems.sql \
         08-materialized-views.sql; do
    cat "$f" | docker exec -i pgch-clickhouse clickhouse-client --multiquery
done

📖 Lab Walkthrough

01 — Schema (01-schema.sql)

Creates the geo_analytics database and three tables:

CREATE TABLE geo_analytics.trips (
    trip_id        UInt64,
    pickup_lon     Float64,
    pickup_lat     Float64,
    dropoff_lon    Float64,
    dropoff_lat    Float64,
    -- ... timestamps, fare, surge, payment, vehicle_type
    pickup_h3_r6   UInt64 MATERIALIZED geoToH3(pickup_lon, pickup_lat, 6),
    pickup_h3_r8   UInt64 MATERIALIZED geoToH3(pickup_lon, pickup_lat, 8),
    pickup_h3_r10  UInt64 MATERIALIZED geoToH3(pickup_lon, pickup_lat, 10),
    dropoff_h3_r8  UInt64 MATERIALIZED geoToH3(dropoff_lon, dropoff_lat, 8),
    INDEX idx_pickup_h3_r8 pickup_h3_r8 TYPE minmax GRANULARITY 4
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(pickup_ts)
ORDER BY  (pickup_h3_r8, pickup_ts, trip_id);

The MATERIALIZED H3 columns are the foundation: each is computed once at INSERT and lives on disk, so demand-heatmap queries scan only the relevant hex columns and never re-encode raw lat/lon.

02 — Synthetic data (02-load.sql)

Generates 5 M trips with weighted hotspot sampling. Implementation detail worth understanding:

-- 100-bucket lookup table: bucket[i] maps to a hotspot index by cumulative weight.
-- Each row does an O(1) array lookup instead of a per-row correlated subquery.
arrayMap(
    i -> arrayFirstIndex(
             cum -> cum >= (i + 1) / 100.0,
             arrayCumSum(arrayMap(w -> w / arraySum(hs_weights), hs_weights))
         ),
    range(100)
) AS bucket_lookup

Bias: 15 hotspots with weights [10, 9, 9, 7, 6, ...] produce realistic Manhattan-heavy distribution.

03 — H3 fundamentals (03-h3-basics.sql)

Function	Purpose
geoToH3(lon, lat, res)	encode point → UInt64 hex index
h3ToGeo(h3)	hex → center lat/lon
h3ToString(h3)	UInt64 → 15-char hex string (e.g. 88f05ab6adfffff)
h3EdgeLengthM(res), h3HexAreaM2(res)	reference table per resolution
h3ToParent(h3, res), h3ToChildren(h3, res)	hierarchy navigation
h3kRing(h3, k)	all hexes within k rings (= 3k² + 3k + 1 cells)
h3Distance(a, b)	hex-grid steps (integer)
h3PointDistM(lat1, lon1, lat2, lon2)	meters between hex centers
h3IsValid, h3IsPentagon, h3GetBaseCell	introspection

Hierarchy quirk: H3 hex nesting is approximate. The res-10 hex of a raw point does NOT always have the res-8 hex of that same point as its h3ToParent — they can disagree near hex boundaries. For consistent roll-ups, store only the finest resolution and derive coarser ones via h3ToParent().

04 — Demand heatmap (04-demand-heatmap.sql)

The killer pattern: aggregate millions of trips by hex.

SELECT h3ToString(pickup_h3_r8) AS hex,
       h3ToGeo(pickup_h3_r8)    AS center_lat_lon,
       count()                  AS pickups
FROM   geo_analytics.trips
GROUP  BY pickup_h3_r8
ORDER  BY pickups DESC LIMIT 10;

Covered: - City-wide overview at res 6 (~3.2 km cells) - Block-level heatmap at res 8 (~460 m cells) - Multi-resolution roll-up via h3ToParent from a single stored r10 column - Hourly rank × hex (window function) - Pickup vs dropoff imbalance (driver supply/demand) - h3ToGeoBoundary → polygon overlay for Leaflet/Mapbox/deck.gl

05 — Distance & routing (05-distance-and-routing.sql)

Three distance flavors: - greatCircleDistance(lon, lat, lon, lat) — spherical, fastest - geoDistance(lon, lat, lon, lat) — WGS84 ellipsoid, more accurate - h3PointDistM(lat, lon, lat, lon) — hex center to hex center (note: lat-first) - h3Distance(h3a, h3b) — hex grid steps (integer)

Sub-queries include trip-length distribution, fare-per-km outlier detection, hex-step vs meter correlation, and round-trip (same-hex pickup/dropoff) detection.

06 — Spatial search (06-spatial-search.sql)

Three patterns: 1. K-ring filter — WHERE pickup_h3_r8 IN h3kRing(center, k) for "all trips within ~k * edge meters of center" 2. Point-in-polygon — pointInPolygon((lon, lat), polygon) for arbitrary service zones, including a full Origin-Destination matrix between every pair of zones 3. Hybrid pre-filter — h3PolygonToCells(polygon, res) → IN () hex pre-filter, then pointInPolygon() on survivors. Trades a fast hex-IN filter for a slower per-row geometry test.

h3PolygonToCells gotcha: takes Array(Tuple(LAT, LON)) — opposite of geoToH3(lon, lat, res) and pointInPolygon((lon, lat), poly). If you store polygons as (lon, lat), swap on the way in with arrayMap(p -> (p.2, p.1), polygon).

07 — H3 vs Geohash vs S2 (07-indexing-systems.sql)

	H3	Geohash	S2
Cell shape	Hexagon	Rectangle	Quadrilateral
Storage	UInt64	String (variable length)	UInt64
Hierarchy	16 resolutions	Prefix nesting	30 levels
Equal-area	Yes (almost)	No (varies by latitude)	Yes (within tolerance)
Best for	Mobility, demand heatmaps, k-ring neighbor analysis	String-prefix searches, URL params, geocoding APIs	Global services, Hilbert-curve coverage

ClickHouse provides: - geohashEncode(lon, lat, precision) / geohashDecode(s) / geohashesInBox(...) - geoToS2(lon, lat) / s2ToGeo(s2) / s2CapContains, s2CapUnion, s2CellsIntersect

08 — Real-time aggregation (08-materialized-views.sql)

Pre-aggregate trips by (hour_bucket, hex_r8) so dashboard queries scan ~72k rows instead of 5 M:

CREATE TABLE demand_by_hex_hour (
    hour_bucket  DateTime,
    hex_r8       UInt64,
    pickups      AggregateFunction(count),
    revenue      AggregateFunction(sum, Decimal(8, 2)),
    avg_fare     AggregateFunction(avg, Decimal(8, 2)),
    unique_users AggregateFunction(uniq, UInt32)
) ENGINE = AggregatingMergeTree() ORDER BY (hour_bucket, hex_r8);

CREATE MATERIALIZED VIEW demand_by_hex_hour_mv TO demand_by_hex_hour AS
SELECT toStartOfHour(pickup_ts), pickup_h3_r8,
       countState(), sumState(fare_amount),
       avgState(fare_amount), uniqState(user_id)
FROM   trips
GROUP  BY ...

Queries use countMerge, sumMerge, avgMerge, uniqMerge to read aggregate state. The lab also includes a live-INSERT test that proves the MV picks up new trips automatically.

🔑 Gotchas worth remembering

	Note
Coordinate order	geoToH3(lon, lat, res) and pointInPolygon((lon, lat), poly) are lon-first. h3PointDistM(lat, lon, lat, lon) and h3PolygonToCells([(lat, lon), …], res) are lat-first.
Named-tuple polygons	h3PolygonToCells requires unnamed Tuple(Float64, Float64). Strip column names with arrayMap(p -> (p.1, p.2), polygon).
H3 hierarchy is approximate	The res-10 hex of a point may not roll up to the res-8 hex of that same point. Store the finest resolution and derive coarser ones via h3ToParent() for consistent results.
system.numbers is infinite	Use numbers(N) table function instead of system.numbers LIMIT N — count() on the former terminates, the latter does not.
INSERT column order	The INSERT column list and SELECT projection list must align positionally. A swap silently produces wrong values.

🔍 Additional resources

• H3 documentation — Uber's H3 reference (resolution table, geometry, API)
• ClickHouse geo functions reference
• ClickHouse H3 functions reference
• S2 Geometry library
• Geohash specification

📝 Verification

All scripts verified end-to-end against ClickHouse 26.5.1.882 from an empty state:

Script	Lines of output	Errors
01-schema.sql	4	0
02-load.sql	8	0
03-h3-basics.sql	54	0
04-demand-heatmap.sql	161	0
05-distance-and-routing.sql	39	0
06-spatial-search.sql	39	0
07-indexing-systems.sql	39	0
08-materialized-views.sql	21	0

---

Happy Mapping! 🗺️

For questions, see the main clickhouse-hols README.

---

ch-geo-analytics/
├── README.md                     # 이 문서
├── 01-schema.sql                 # trips + service_zones + hotspots 테이블
├── 02-load.sql                   # 500만 트립 합성 데이터 (가중치 샘플링)
├── 03-h3-basics.sql              # H3 기본: 인코딩, 계층, k-ring, 거리
├── 04-demand-heatmap.sql         # 다중 해상도 hex 집계, top-N, OD flow
├── 05-distance-and-routing.sql   # greatCircle / geo / h3PointDist / h3Line / hex 단계
├── 06-spatial-search.sql         # K-ring, pointInPolygon, OD 매트릭스, 하이브리드 사전 필터
├── 07-indexing-systems.sql       # H3 vs Geohash vs S2 비교
├── 08-materialized-views.sql     # AggregatingMergeTree로 실시간 수요 집계
└── 99-cleanup.sql                # 모든 객체 삭제

cd usecase/ch-geo-analytics

# 순차 실행 — 로컬에서 실행 중인 ClickHouse 활용
for f in 01-schema.sql 02-load.sql 03-h3-basics.sql \
         04-demand-heatmap.sql 05-distance-and-routing.sql \
         06-spatial-search.sql 07-indexing-systems.sql \
         08-materialized-views.sql; do
    echo "▶ Running $f"
    clickhouse-client --multiquery --queries-file "$f"
done

for f in 01-schema.sql 02-load.sql 03-h3-basics.sql \
         04-demand-heatmap.sql 05-distance-and-routing.sql \
         06-spatial-search.sql 07-indexing-systems.sql \
         08-materialized-views.sql; do
    cat "$f" | docker exec -i pgch-clickhouse clickhouse-client --multiquery
done

CREATE TABLE demand_by_hex_hour (
    hour_bucket  DateTime,
    hex_r8       UInt64,
    pickups      AggregateFunction(count),
    revenue      AggregateFunction(sum, Decimal(8, 2)),
    ...
) ENGINE = AggregatingMergeTree() ORDER BY (hour_bucket, hex_r8);

CREATE MATERIALIZED VIEW demand_by_hex_hour_mv TO demand_by_hex_hour AS
SELECT toStartOfHour(pickup_ts), pickup_h3_r8,
       countState(), sumState(fare_amount), ...
FROM trips GROUP BY ...