ClickHouse (OSS) + Superset — Korean Administrative-Boundary (Sigungu) Map Lab / 한국 행정구역(시군구) 지도 랩

---

A hands-on lab that implements coordinate → district reverse geocoding (ClickHouse Polygon Dictionary) and a deck.gl Polygon choropleth (Superset) entirely on local Docker. It demonstrates the full pipeline where ClickHouse classifies & aggregates and Superset visualizes, and ends with a weather-station dashboard that overlays 3,000 points on per-district aggregates.

Key trap: ClickHouse geo functions (pointInPolygon, geoToH3, polygon dictionary) assume WGS84 longitude/latitude in degrees (EPSG:4326). Feeding EPSG:5179 (UTM-K, meters) coordinates produces a silently wrong result with no error. So data must be loaded in 4326.

Author: Ken Lee (ClickHouse SA) · License: MIT

Stack

Service	Image	Port (host)	Notes
ClickHouse	clickhouse/clickhouse-server:25.5	8123 (HTTP), 9000 (native)	default user, no password (local lab)
Superset	apache/superset:4.1.1 + clickhouse-connect	8088	SQLite metadata (volume-persisted), admin/admin

korea-geo/
├── docker-compose.yml          # clickhouse + superset stack (network: korea-geo)
├── superset/
│   ├── Dockerfile              # official image + clickhouse-connect
│   ├── requirements-local.txt  # clickhouse-connect
│   ├── superset_config.py      # SECRET_KEY / MAPBOX_API_KEY / SQLite metadata
│   └── bootstrap.sh            # db upgrade → create-admin → init → gunicorn
├── data/sig_4326.geojson       # 251 sigungu boundaries (WGS84) — southkorea/southkorea-maps
├── ddl/
│   ├── 01_schema.sql              # DB + 2 tables + Polygon Dictionary
│   ├── 02_validate.sql            # load / reverse-geocoding / coordinate-trap checks
│   ├── 03_points_choropleth.sql   # random points → classify → aggregate → update metric
│   └── 04_weather_stations.sql    # 3,000 stations + per-district aggregates + choropleth join
├── scripts/
│   ├── load_geo.py                # geojson → ClickHouse (clickhouse-connect)
│   ├── setup_superset.py          # Superset DB connection + sig_map dataset (auto)
│   └── setup_dashboard.py         # station datasets + 4 charts + dashboard (auto, idempotent)
└── README.md

Two data representations are kept separate because each tool needs a different format: - geospatial.sig_polygons — full MultiPolygon [polygon][ring][point] → Polygon Dictionary source (accurate classification) - geospatial.sig_map — one outer ring per row, coordinates as [[lon,lat],...] JSON → deck.gl visualization

Run order (reproduce)

cd usecase/korea-geo

# 0) (optional) Mapbox token for deck.gl basemap tiles. Without it, only polygons render.
export MAPBOX_API_KEY="pk.xxxxx"

# 1) Bring up the stack (superset image builds on first run)
docker compose up -d --build

# 2) Health checks
curl 'http://localhost:8123/?query=SELECT%20version()'      # -> 25.5.x
curl -I http://localhost:8088/health                         # -> HTTP/1.1 200 OK

# 3) Schema + dictionary
docker exec -i kg-clickhouse clickhouse-client --multiquery < ddl/01_schema.sql

# 4) Load data (host python; install driver)
python3 -m pip install --user clickhouse-connect
python3 scripts/load_geo.py
docker exec kg-clickhouse clickhouse-client --query "SYSTEM RELOAD DICTIONARY geospatial.sig_dict"

# 5) Validate
docker exec -i kg-clickhouse clickhouse-client --multiquery < ddl/02_validate.sql

# 6) (optional) points → classify → aggregate → update choropleth metric
docker exec -i kg-clickhouse clickhouse-client --multiquery < ddl/03_points_choropleth.sql

# 7) Register the ClickHouse connection + sig_map dataset in Superset
python3 scripts/setup_superset.py

Inside the ClickHouse container localhost:9000 is a self-reference, so the dictionary SOURCE HOST is localhost. From the Superset container, ClickHouse is reached by the compose service name clickhouse:8123 (see URI below).

Verified results (measured — ClickHouse 25.5.11.15 / Superset 4.1.1)

• [x] curl SELECT version() → 25.5.11.15
• [x] Superset /health → HTTP 200, admin (admin/admin) login works
• [x] sig_map rows / distinct sigungu → rings=325, sigungu=251
• [x] sig_polygons (dictionary source) = 251
• [x] sig_dict reverse geocoding correct (table below)
• [x] utmk_bad = 0, wgs84_ok = 1 — coordinate-system trap reproduced
• [x] Superset ClickHouse connection test → 200 OK
• [x] deck.gl Polygon chart-data query → 325 rows (coordinates JSON + name + MAX(metric))

Reverse geocoding (dictGet('geospatial.sig_dict','name',(lon,lat))):

Input (lon, lat)	Result	Expected
(127.289, 36.480)	세종시 (Sejong)	Sejong (admin capital) ✓
(126.9780, 37.5665)	종로구 (Jongno-gu)	central Seoul ✓
(129.0756, 35.1796)	연제구 (Yeonje-gu)	near Busan ✓

Coordinate-system trap (geo-function smoke test):

geoToH3(127.289, 36.480, 7)                       = 608482148894113791
pointInPolygon((127.289, 36.480), Korea bbox)     = 1   ← WGS84 OK
pointInPolygon((612656, 1791892), Korea bbox)     = 0   ← 5179 meters = silently wrong

The same Sejong location is inside the box (1) in 4326 (degrees) but outside (0) in 5179 (meters) — proof that skipping reprojection yields a wrong result with no error.

Weather-station dashboard — point visualization + per-district aggregation

Place 3,000 random weather stations nationwide and build a dashboard that ① visualizes the points, ② aggregates per district, and ③ overlays both. Swap weather_stations for real station/sensor/store lon·lat and it becomes a production dashboard as-is.

docker exec -i kg-clickhouse clickhouse-client --multiquery < ddl/04_weather_stations.sql
python3 scripts/setup_dashboard.py

• geospatial.weather_stations — 3,000 stations. Only bbox-random points that the dictionary classifies into a sigungu (= on land) are kept. Each gets a synthetic temp_c (colder as latitude rises + noise), elevation_m, and sigungu_code/sigungu_name (the classification result).
• geospatial.sig_station_agg — per-district station_count / avg_temp / avg_elev.
• geospatial.sig_station_map — sig_map (polygon rings) ⨝ aggregates → for the deck.gl Polygon choropleth (zero-station districts kept via LEFT JOIN).

Measured: 3,000 stations across 217 districts; temperature range -0.7 ~ 17.9 °C (≈3 °C in northern Gangwon, ≈9 °C in the south). Top districts (large rural counties — natural under uniform random): 인제군 53 · 홍천군 49 · 안동시 48 · 의성군 44 · 평창군 41 … All 4 Superset charts + dashboard auto-created; every chart-data API call returned 200/data.

Endpoints (admin/admin):

Item	URL
Dashboard "전국 관측소 현황"	http://localhost:8088/superset/dashboard/1/
Overlay map (deck.gl Multi: choropleth + station points)	http://localhost:8088/explore/?slice_id=3
Station distribution (deck.gl Scatter)	http://localhost:8088/explore/?slice_id=1
Stations per district (deck.gl Polygon choropleth)	http://localhost:8088/explore/?slice_id=2
Per-district TOP (Table)	http://localhost:8088/explore/?slice_id=4

Layout: overlay map on top (station points + district shading), choropleth + TOP table below. Points/polygons render without a token; basemap tiles appear only when MAPBOX_API_KEY is set.

setup_dashboard.py is idempotent — re-running after re-generating stations (04_weather_stations.sql) or swapping in real data updates the same charts/dashboard instead of duplicating them.

Build a deck.gl Polygon chart manually (UI)

1. http://localhost:8088 → log in admin / admin.
2. Charts → + Chart → dataset sig_map (or sig_station_map) → deck.gl Polygon: - Polygon Column: coordinates - line_type / Polygon Encoding: json - Metric: MAX(metric) (or MAX(station_count)) - pick a linear color scheme + opacity → Run.
3. Visual check: districts shaded over the peninsula, with the Sejong polygon centered in Chungcheong — visual proof the coordinate system is right.

Superset DB connection URI (uses the service name):

clickhousedb://default:@clickhouse:8123/geospatial

Troubleshooting (including issues actually hit during this build)

• Superset can't find the clickhousedb dialect → clickhouse-connect not installed. Add to superset/requirements-local.txt, then docker compose build superset.
• Connects but host not found → set the URI host to the compose service name clickhouse, not localhost.
• test_connection returns an HTML redirect → the endpoint needs a trailing slash (/api/v1/database/test_connection/). Handled in setup_superset.py.
• ALTER ... UPDATE errors with UNKNOWN_IDENTIFIER → ClickHouse mutations don't support correlated subqueries. 03_points_choropleth.sql works around it with a flat dictionary (sig_counts_dict) + dictGetOrDefault.
• Dictionary empty / lookup fails → run SYSTEM RELOAD DICTIONARY geospatial.sig_dict after loading data. LIFETIME(0) never auto-reloads.
• Reverse geocoding all wrong/blank → suspect un-reprojected (5179) data. load_geo.py blocks this by checking the first coordinate's magnitude before loading.
• Superset REST filter returns 400 "Not a valid rison" / duplicate charts on re-run → names containing parentheses break the rison filter. setup_dashboard.py wraps filter values in single quotes (find_id()) so lookups match and re-runs update in place.
• Dashboard panel says "no chart definition associated with this component" → charts created via API must be explicitly linked to the dashboard; setup_dashboard.py PUTs dashboards:[id] on each chart.

If your source needs reprojection (EPSG:5179 → 4326)

This lab's data (southkorea/southkorea-maps) is already WGS84, so no conversion is needed. For a 5179 (meters) source, convert before loading:

ogr2ogr -t_srs EPSG:4326 data/sig_4326.geojson sig_5179.shp
# or
python -c "import geopandas as gpd; gpd.read_file('sig_5179.shp').to_crs(4326).to_file('data/sig_4326.geojson', driver='GeoJSON')"

Teardown / scope

docker compose down            # containers only
docker compose down -v         # also drop volumes (ClickHouse data + Superset metadata)

• This lab works at the sigungu (251) level. Eup/myeon/dong (thousands, vertex explosion → needs simplification) is out of scope for the first pass.
• Data source: southkorea/southkorea-maps kostat/2013 sigungu GeoJSON (WGS84, simplified).

---

korea-geo/
├── docker-compose.yml          # clickhouse + superset 스택 (network: korea-geo)
├── superset/
│   ├── Dockerfile              # 공식 이미지 + clickhouse-connect
│   ├── requirements-local.txt  # clickhouse-connect
│   ├── superset_config.py      # SECRET_KEY / MAPBOX_API_KEY / SQLite 메타DB
│   └── bootstrap.sh            # db upgrade → create-admin → init → gunicorn
├── data/sig_4326.geojson       # 시군구 251개 경계 (WGS84) — southkorea/southkorea-maps
├── ddl/
│   ├── 01_schema.sql              # DB + 테이블 2종 + Polygon Dictionary
│   ├── 02_validate.sql            # 적재/리버스지오코딩/좌표계 함정 검증
│   ├── 03_points_choropleth.sql   # 랜덤 포인트 → 분류 → 집계 → metric 갱신
│   └── 04_weather_stations.sql    # 관측소 3,000개 + 시군구별 집계 + choropleth 조인
├── scripts/
│   ├── load_geo.py                # geojson → ClickHouse (clickhouse-connect)
│   ├── setup_superset.py          # Superset DB연결 + sig_map 데이터셋 자동 생성
│   └── setup_dashboard.py         # 관측소 데이터셋 + 차트 4종 + 대시보드 자동 생성(멱등)
└── README.md

cd usecase/korea-geo

# 0) (선택) deck.gl 베이스맵 타일용 Mapbox 토큰. 없으면 폴리곤만 렌더된다.
export MAPBOX_API_KEY="pk.xxxxx"

# 1) 스택 기동 (superset 이미지는 최초 1회 빌드)
docker compose up -d --build

# 2) 헬스 확인
curl 'http://localhost:8123/?query=SELECT%20version()'      # -> 25.5.x
curl -I http://localhost:8088/health                         # -> HTTP/1.1 200 OK

# 3) 스키마 + 딕셔너리 생성
docker exec -i kg-clickhouse clickhouse-client --multiquery < ddl/01_schema.sql

# 4) 데이터 적재 (호스트 python; 드라이버 설치)
python3 -m pip install --user clickhouse-connect
python3 scripts/load_geo.py
docker exec kg-clickhouse clickhouse-client --query "SYSTEM RELOAD DICTIONARY geospatial.sig_dict"

# 5) 검증
docker exec -i kg-clickhouse clickhouse-client --multiquery < ddl/02_validate.sql

# 6) (선택) 포인트 → 분류 → 집계 → choropleth metric 갱신
docker exec -i kg-clickhouse clickhouse-client --multiquery < ddl/03_points_choropleth.sql

# 7) Superset에 ClickHouse 연결 + 데이터셋 자동 등록
python3 scripts/setup_superset.py

geoToH3(127.289, 36.480, 7)                  = 608482148894113791
pointInPolygon((127.289, 36.480), 한반도bbox)  = 1   ← WGS84 정상
pointInPolygon((612656, 1791892), 한반도bbox)  = 0   ← 5179 미터값 = 조용히 틀림

docker exec -i kg-clickhouse clickhouse-client --multiquery < ddl/04_weather_stations.sql
python3 scripts/setup_dashboard.py

clickhousedb://default:@clickhouse:8123/geospatial

ogr2ogr -t_srs EPSG:4326 data/sig_4326.geojson sig_5179.shp
# 또는
python -c "import geopandas as gpd; gpd.read_file('sig_5179.shp').to_crs(4326).to_file('data/sig_4326.geojson', driver='GeoJSON')"

docker compose down            # 컨테이너만
docker compose down -v         # 볼륨(ClickHouse 데이터 + Superset 메타DB)까지 삭제