Designing a Spatial ETL Pipeline in PostGIS
Author: Konstantinos Chantsaris | Source: Medium | Published: 2026-07-13
한 줄 요약
서로 다른 정부기관이 그린 지도를 융합해 그리스를 수천 개의 도시 micro-fragment로 쪼개는 과정에서, CRS 불일치·spatial sliver·누락된 함수 인덱스·잘못된 분모라는 공간 데이터 특유의 함정을 하나씩 통과한 기록.
핵심 주장/내용
- 왜 공간 DB인가: Point 기하와 복잡한 polygon/multipolygon을 텍스트 행에 맞추는 것은 그만한 가치가 없는 복잡도를 더한다. 기하를 이해하는 DB는 “이 점이 이 폴리곤 안에 있나”(
ST_Contains), “두 폴리곤이 겹치면 정확한 교집합 면적은”(ST_Area(ST_Intersection(...))) 같은 질의 로직을 가능하게 한다. 평면 통계 데이터가 쓸모없어지는 게 아니라, 평면 인구통계와 기하 도형을 결합하는 것이 물리적 공간에 대한 질문에 답하게 해준다 - MVP를 벗어나는 시점: 초기에는 Python 추출 스크립트가 하드코딩된 크레덴셜이 흩어진 단일 파일이었고, 핵심 DB 객체가 pgAdmin 세션 히스토리의 임기응변 SQL로만 존재했다 — 컨테이너가 사라지면 프로젝트도 사라진다. 빠른 MVP로 아이디어를 실제로 보고 약점을 일찍 드러내는 단계는 값지지만, 오래 머물 단계는 아니다
- 아키텍처 정리: 모듈러 설계(
data/sources/에 소스별 파일,data/queries/), 모든 스크립트를extract()/transform()/load()로 표준화, 6개 파이프라인 스텝, 의존 순서를 제어하는 단일 오케스트레이션 스크립트에--only/--skip플래그(데이터 성격상 일부만 몇 년에 한 번 갱신되므로 스텝 독립 실행이 필요), 의존성 충돌이 나는 OSM 파서는 Conda 환경에 격리하고 subprocess로 트리거 - 함정 1 — CRS(좌표계): 일반 데이터에서 27은 그냥 숫자지만 공간 데이터에서 좌표는 CRS 없이는 아무 의미도 없고, 원본과 다른 계를 쓰면 데이터가 깨진다. 세 소스가 세 좌표계였다 — ELSTAT은 EPSG:2100(그리스 그리드, 미터), OSM은 EPSG:4326(WGS 84, 도), GSIS ArcGIS API는 EPSG:3857(Web Mercator 미터). 3857 미터를 4326 도로 잘못 받아들여 QGIS에서 좌표가 붕괴했고(200만 도를 찍으려다 소프트웨어가 빈 화면), ELSTAT은 GeoPandas에서
to_crs로 재투영, GSIS는 ArcGIS API 요청 페이로드에'outSR': '4326'을 직접 넣어 서버가 애초에 올바른 좌표를 반환하게 고쳤다 - 함정 2 — spatial sliver: 두 정부 지도가 같은 존을 담고 있지 않았다(다른 ID, 다른 경계). 코뮌 단위 평균 같은 지표는 지역화된 정확한 데이터를 파괴하므로
ST_Intersection으로 코뮌 ID와 정확한 지역 가격을 모두 보존하는 수천 개 micro-fragment를 만들었다. 그 결과 미세한 slivers가 생겼고, EDA에서 km²당 인구밀도가 수억으로 나오는 걸 발견 — 150m² sliver가 20km² 부모와 정확히 같은 인구를 상속했던 것- 두 선택지 중 인구를 작은 조각에 보간하는 것은 실제 데이터 대신 추측을 하는 것이라 프로젝트 무결성을 망친다고 판단 → Two-Tier 데이터 모델: Tier 1(fragment 레벨)은 실제 지역화된 물리 지표만(m²당 가격, 기하, 편의시설 수), Tier 2(코뮌 레벨)는 공식 ELSTAT 경계 기준 materialized view로 인구 계산을 정확히 유지
- 함정 3 — 누락된 공간 인덱스:
ST_DWithin같은 공간 조인은 수학적으로 무겁다. SQL 재작성 중 인프라 테이블의CREATE INDEX가 빠졌고, 초 단위여야 할 쿼리가 한 시간 넘게 출력 없이 얼어붙었다. 게다가 터미널을 닫아도 쿼리가 죽지 않고 서버 사이드에서 orphan 커넥션으로 한 시간 더 돌았다 —pg_stat_activity로 찾아pg_terminate_backend()를 해야 했다- 일반 GIST 인덱스로는 부족했다. 쿼리가 실세계 곡률 거리를 미터로 계산하려고 기하를
::geography로 캐스트하므로 그 표현식 위의 functional index가 필요:CREATE INDEX ... USING GIST ((geometry::geography))
- 일반 GIST 인덱스로는 부족했다. 쿼리가 실세계 곡률 거리를 미터로 계산하려고 기하를
- 함정 4 — 잘못된 분모: 15분 도시 측정을 위해 fragment마다 500m 도보 반경 catchment 안의 편의시설을 셌는데, 초기 SQL이 편의시설 수를 500m 원의 면적이 아니라 fragment 자신의 물리적 면적으로 나눴다. 13,862개 fragment 중 91% 이상이 500m 원보다 작아서 밀도 지표가 km²당 수백만에 도달(100m²로 50개 상점을 나누는 격).
ST_Buffer로 500m 원을 생성하고ST_Area로 그 정확한 면적을 계산해 나누는 것으로 해결
주요 수치 / 사실
- 융합한 데이터: ELSTAT 공식 지리 경계 6,138개 코뮌, GSIS 정부 가격 폴리곤 12,505개, OpenStreetMap POI 132,000개 이상, ELSTAT 건물 센서스 구조 분석
- 결과 micro-fragment 13,862개
- 정리된 공간 웨어하우스의 힘: 상관 분석에 Python으로 내보낼 필요 없이 PostGIS 쿼리에서 직접 계산. 라이브 공간 선형 회귀 R² = 0.209, km²당 편의시설 1개 증가마다 부동산 가치 €8.51/m² 증가. 공실률과 인구 사이 강한 음의 상관(-0.60) 확인
- 다음 단계: 비지도 ML로 그리스 micro-fragment가 High Gentrification Risk, Transit Deserts, Balanced 15-Minute Cities 같은 도시 프로파일 클러스터로 자연스럽게 나뉘는지 확인
관련 위키
- Spatial Data Engineering
- Data Pipeline Fundamentals
- Query Optimization
- Data Quality and Validation
- Data Modeling
Source: 원문 보기