本文是 PostgreSQL overview 的 implementation-layer deep article。Overview 已說明 PG 在 OLTP 譜系的定位、本文聚焦 PostGIS extension — PG 變 GIS DB 的標配、跟 extension-ecosystem 是 單一 extension 細節 vs ecosystem 全景 的關係。

PostGIS 是 PG 的 GIS Specialization

PostGIS 是 PG 最成熟的 extension 之一（2001 年起、25 年歷史）、產業地位等同 OracleSpatial / SQL Server geography：

1CREATE EXTENSION postgis;

加完後 PG 多兩件事：

1. 空間型別：geometry（平面）/ geography（地球曲面）/ raster（柵格）
2. 1000+ 函式：ST_Distance / ST_Within / ST_Buffer / ST_Intersects 等

用 PostGIS 解的典型 workload：

• 「離我最近的 N 家店」（k-NN）
• 「半徑 1km 內的所有 POI」（radius query）
• 「兩個 polygon 是否重疊」（intersection）
• 「polyline 總長度」（measurement）
• 「行政區包含哪些 point」（containment）

Geometry vs Geography：選錯付學費

PostGIS 提供兩種空間型別、用途完全不同：

選 geography 的場景：

• 全球範圍 application（跨國 / 跨大陸）
• 距離精準度要求高（球面比平面誤差小）
• 不需要複雜空間運算（geography 函式較少）

選 geometry 的場景：

• 單一城市 / 國家內 application
• 需要完整 ST_* 函式（90% 函式只支援 geometry）
• 效能敏感

實務多數 production 選 geometry + 適合的 SRID（用 local projection）— 既快又精準。

SRID 跟 Projection：為什麼 4326 vs 3857 是 GIS 第一課

SRID（Spatial Reference System Identifier）定義「座標數字怎麼解讀」：

為什麼選 local projection（3826）而不是經緯度（4326）：

• 經緯度單位是 度、不是距離 — ST_Distance 直接算出來是「度」、不是「米」
• 距離計算需 ST_DistanceSphere 或 geography cast、計算 cost 高
• Local projection 是「平面投影」、ST_Distance 直接是米、ST_Area 直接是平方米

 1-- 4326 經緯度直接算 → 結果不是米
 2SELECT ST_Distance(
 3    ST_SetSRID(ST_MakePoint(121.5654, 25.0330), 4326),  -- 台北 101
 4    ST_SetSRID(ST_MakePoint(121.5170, 25.0478), 4326)   -- 台北車站
 5);  -- ~0.05（這是「度」）
 6
 7-- 轉 3826（台灣本地投影）才是米
 8SELECT ST_Distance(
 9    ST_Transform(ST_SetSRID(ST_MakePoint(121.5654, 25.0330), 4326), 3826),
10    ST_Transform(ST_SetSRID(ST_MakePoint(121.5170, 25.0478), 4326), 3826)
11);  -- ~5300（米）
12
13-- 或用 geography cast
14SELECT ST_Distance(
15    ST_SetSRID(ST_MakePoint(121.5654, 25.0330), 4326)::geography,
16    ST_SetSRID(ST_MakePoint(121.5170, 25.0478), 4326)::geography
17);  -- ~5300（米）

典型 schema 設計（台灣 application）：

 1CREATE TABLE pois (
 2    id SERIAL PRIMARY KEY,
 3    name TEXT,
 4    -- 儲存 4326（跟 Google Maps API 對齊）
 5    location_4326 geometry(Point, 4326),
 6    -- 預計算 3826（給距離 / 面積 query 用）
 7    location_3826 geometry(Point, 3826) GENERATED ALWAYS AS
 8        (ST_Transform(location_4326, 3826)) STORED
 9);
10
11CREATE INDEX idx_pois_location_3826 ON pois USING GIST (location_3826);

GiST 空間索引：R-tree 的 PG 實作

PostGIS 用 PG 內建 GiST 做空間索引（內部是 R-tree 變體）：

1CREATE INDEX idx_pois_geom ON pois USING GIST (location_3826);

GiST 對空間 query 加速的場景：

 1-- 範圍 query（box overlap）
 2SELECT * FROM pois
 3WHERE location_3826 && ST_MakeEnvelope(290000, 2760000, 305000, 2775000, 3826);
 4
 5-- 半徑 query（用 ST_DWithin 才走 index）
 6SELECT * FROM pois
 7WHERE ST_DWithin(location_3826, ST_SetSRID(ST_MakePoint(300000, 2770000), 3826), 1000);
 8
 9-- k-NN（PostGIS 2.0+ <-> operator）
10SELECT id, name, location_3826 <-> ST_SetSRID(ST_MakePoint(300000, 2770000), 3826) AS dist
11FROM pois
12ORDER BY location_3826 <-> ST_SetSRID(ST_MakePoint(300000, 2770000), 3826)
13LIMIT 10;

index 用沒用到的關鍵：

Production 寫法守則：能用 ST_DWithin 就不用 ST_Distance(...) < ?、語意一樣但 index 行為差很多。

ST_* 函式生態：產業級全套

PostGIS 1000+ 函式分類（典型用到的）：

Web tile 場景典型 query：

1-- 給定 z/x/y tile、找這個 tile 內的所有 POI
2SELECT id, name, ST_AsMVTGeom(location_3857, ST_TileEnvelope(z, x, y)) AS geom
3FROM pois
4WHERE location_3857 && ST_TileEnvelope(z, x, y);

ST_AsMVTGeom + ST_AsMVT 直接產 Mapbox Vector Tile binary、給前端 Leaflet / Mapbox GL JS 用。

5 個 Production 踩雷

Case 1：Geometry 用錯 SRID

情境：app 寫入時用 4326、query 時用 3826 ST_Transform、忘記給某個 column 設 SRID、index 失效。

修法：

 1-- 確認 SRID
 2SELECT ST_SRID(location) FROM pois LIMIT 1;
 3
 4-- 強 type 約束（column type 寫死 SRID）
 5ALTER TABLE pois ALTER COLUMN location TYPE geometry(Point, 4326)
 6USING ST_SetSRID(location, 4326);
 7
 8-- Check constraint 防錯
 9ALTER TABLE pois ADD CONSTRAINT chk_location_srid
10CHECK (ST_SRID(location) = 4326);

Case 2：Geography 不能用所有 ST_* 函式

情境：用 geography 想跑 ST_Buffer、報錯或結果不對。

ST_Buffer 對 geography 走 spheroid 近似、邊界 case 結果跟 geometry 不一致；很多函式（ST_Voronoi / ST_Delaunay 等）只支援 geometry。

修法：

• 簡單距離 query 用 geography
• 複雜空間運算用 geometry + 適合 projection
• 不確定哪些函式支援 geography、看 PostGIS docs Geography Support Functions 清單

Case 3：GiST index 不對 ST_Distance 生效

情境：query ST_Distance(location, ?) < 1000、EXPLAIN 顯示 full scan、加 index 也沒用。

ST_Distance 算完才 filter、planner 沒辦法用 GiST。

修法：

• 改 ST_DWithin(location, ?, 1000) — 語意一樣、會走 GiST
• 確認 index 是對 被 query 的 column 建的（不是 transform 後的 expression）

Case 4：CLUSTER on geom 後 BRIN 失效

情境：對 pois 跑 CLUSTER pois USING idx_pois_geom 想加速空間查、但同時對 created_at 用 BRIN index、BRIN 完全失效。

CLUSTER 重組 physical order 跟 GiST 對齊、created_at physical order correlation 從 1.0 變 0.0、BRIN range 沒選擇性。

修法：

• 不要 CLUSTER 大表（一次性、影響其他 column）
• 換 partition by time + GiST per-partition（取兩者）
• 看 index-selection 的 BRIN 段

Case 5：EWKB vs WKB 跨工具相容

情境：用 PostGIS export 給其他 GIS 工具（QGIS / Shapely / ogr2ogr）、resort 抱怨格式不對。

PostGIS 內部用 EWKB（Extended Well-Known Binary）— 多帶 SRID。多數 GIS 工具讀 WKB（標準）。

修法：

1-- Export 標準 WKB
2SELECT ST_AsBinary(geom) FROM pois;
3
4-- 或 GeoJSON（跨工具最相容）
5SELECT ST_AsGeoJSON(geom) FROM pois;
6
7-- 或 Shapefile via ogr2ogr
8-- ogr2ogr -f "ESRI Shapefile" output.shp PG:"..." -sql "SELECT * FROM pois"

跟專業 GIS DB 對比

選 PostGIS 的場景（90% GIS workload）：

• Application 已用 PG
• 需要完整 GIS 函式生態（路網 / 等高線 / 流域分析）
• 開源 / cost 敏感
• 跟 OGR / GDAL / QGIS 互通

選專業 GIS DB 的場景：

• 已綁定 Oracle / SQL Server license
• 極專業 GIS（3D 城市模型 / LIDAR / GPU 加速）
• 純 location app 不需 relational（MongoDB GeoJSON 足夠）

相關連結

• extension-ecosystem：其他 PG extension
• index-selection：GiST 跟其他 index 對比
• query-optimization：空間 query 的 EXPLAIN
• jsonb-deep-dive：POI metadata 用 JSONB 儲存

下一步

• 看 extension-ecosystem 探索其他 PG 擴展可能
• 回 PostgreSQL overview 看全圖