PostGIS 地理空間：從座標系統到空間查詢的完整實戰 | PostgreSQL

PostGIS 是 PostgreSQL 最重要的擴展之一，為關聯式資料庫加入完整的地理空間功能。無論是「找出附近 1 公里的餐廳」、「判斷設備是否在圍欄內」還是「計算行政區面積」，PostGIS 都能用 SQL 直接完成。本文從座標系統、空間型別到索引與查詢模式，帶你掌握 PostGIS 的完整實戰知識。

GIS 基礎概念

地理資訊系統（GIS）處理的核心問題是：如何在數位環境中表示、儲存、查詢與分析地球表面上的空間資料。

向量資料 vs 柵格資料：

向量資料（Vector）                柵格資料（Raster）
┌─────────────────────┐          ┌─────────────────────┐
│  以幾何圖形表示       │          │  以像素格網表示       │
│  點(Point)：城市位置  │          │  衛星影像、高程模型   │
│  線(LineString)：道路 │          │  每個 Cell 有屬性值   │
│  面(Polygon)：行政區  │          │  [1][2][3][4]       │
│  精確邊界，支援計算    │          │  [5][6][7][8]       │
└─────────────────────┘          └─────────────────────┘

PostGIS 主要處理向量資料（也支援 Raster），這是大多數應用場景的需求。

座標系統與 SRID

地球是不規則橢球體，將其投影到平面地圖必然產生形變。不同的投影方式適合不同用途：

SRID	名稱	說明	單位
4326	WGS 84	全球通用，GPS 標準	度（Degree）
3857	Web Mercator	Google Maps / OpenStreetMap	公尺
3826	TWD97 / TM2	台灣二度分帶坐標系	公尺
4269	NAD83	北美地區常用	度

SRID 的重要性——不同 SRID 的幾何物件不能直接比較，必須用 ST_Transform() 轉換：

-- 查詢 PostGIS 支援的座標系統
SELECT COUNT(*) FROM spatial_ref_sys;
-- 通常包含數千個座標系統定義

-- 查詢特定座標系統資訊
SELECT srid, srtext FROM spatial_ref_sys WHERE srid = 4326;

Geometry vs Geography

PostGIS 提供兩種核心空間型別，選擇正確的型別對計算精確度至關重要：

比較項目	Geometry	Geography
計算模型	平面（Euclidean）	球面（Spherical）
距離單位	依 SRID 而定（可能是度或公尺）	公尺
支援函式	完整（數百個）	較少（常用函式）
計算速度	快	較慢（球面三角計算）
跨區域精確度	低（小範圍尚可）	高
支援的 SRID	任意	僅 4326（WGS 84）

Geometry（平面計算）
  台北 → 東京：sqrt((139.7-121.5)² + (35.7-25.0)²) = 21.57 度
  → 需要手動換算公尺（誤差隨緯度變化）

Geography（球面計算）
  台北 → 東京：基於 Haversine 公式的大圓距離
  → 直接輸出：~2,104 公里（精確）

選擇建議：

資料集中在台灣本島 → Geometry + SRID 3826（TWD97），公尺計算精確且快
全球範圍、需要精確公尺距離 → Geography
Google Maps / Leaflet 格式（WGS 84 經緯度） → Geography 或 Geometry(4326)

安裝 PostGIS

# Ubuntu / Debian
sudo apt install postgresql-17-postgis-3

# macOS
brew install postgis

-- 啟用擴展
CREATE EXTENSION IF NOT EXISTS postgis;

-- 確認版本
SELECT PostGIS_Version();
-- 結果：3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

-- 若需要 Raster 支援
CREATE EXTENSION IF NOT EXISTS postgis_raster;

建立空間資料表

-- 建立包含空間欄位的表格
CREATE TABLE locations (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    geom        geometry(Point, 4326),   -- WGS 84 經緯度點
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 不同幾何型別的欄位宣告
CREATE TABLE spatial_features (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT,
    point_2d    geometry(Point, 4326),        -- 二維點
    point_3d    geometry(PointZ, 4326),       -- 三維點（含高程）
    line        geometry(LineString, 4326),   -- 線段
    polygon     geometry(Polygon, 4326),      -- 多邊形
    location    geography(Point, 4326)        -- Geography 型別
);

插入與匯出空間資料

-- 使用 WKT（Well-Known Text）格式插入
INSERT INTO locations (name, category, geom) VALUES
    ('台北101', 'landmark', ST_GeomFromText('POINT(121.5645 25.0338)', 4326)),
    ('台北車站', 'transport', ST_GeomFromText('POINT(121.5168 25.0478)', 4326)),
    ('故宮博物院', 'museum', ST_GeomFromText('POINT(121.5485 25.1023)', 4326));

-- 使用 ST_MakePoint（更高效）
INSERT INTO locations (name, geom)
VALUES ('大安森林公園', ST_SetSRID(ST_MakePoint(121.5350, 25.0299), 4326));

-- 使用 GeoJSON 格式
INSERT INTO locations (name, geom)
VALUES (
    '淡水老街',
    ST_SetSRID(
        ST_GeomFromGeoJSON('{"type":"Point","coordinates":[121.4497,25.1699]}'),
        4326
    )
);

-- 使用 EWKT（含 SRID 資訊）
INSERT INTO locations (name, geom)
VALUES ('信義區', 'SRID=4326;POINT(121.5656 25.0323)');

-- 匯出為各種格式
SELECT name, ST_AsText(geom) AS wkt FROM locations;          -- WKT
SELECT name, ST_AsGeoJSON(geom) AS geojson FROM locations;   -- GeoJSON
SELECT name, ST_AsEWKT(geom) AS ewkt FROM locations;         -- EWKT

-- 批次匯出為 GeoJSON FeatureCollection（前端直接可用）
SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(
        json_build_object(
            'type', 'Feature',
            'geometry', ST_AsGeoJSON(geom)::json,
            'properties', json_build_object('name', name, 'category', category)
        )
    )
) FROM locations;

空間索引

空間物件無法像數字那樣在 B-tree 上排序，PostGIS 使用 GiST 索引（R-tree 變體）解決這個問題。

-- 建立 GiST 空間索引（最常用）
CREATE INDEX idx_locations_geom ON locations USING gist(geom);

-- 線上環境用 CONCURRENTLY 避免鎖表
CREATE INDEX CONCURRENTLY idx_locations_geom ON locations USING gist(geom);

-- Geography 型別的索引
CREATE INDEX idx_locations_geo ON locations USING gist(location);

-- Partial Index（只索引特定類別）
CREATE INDEX idx_restaurants_geom ON locations USING gist(geom)
    WHERE category = 'restaurant';

GiST 索引的查詢流程：

過濾階段：用外包矩形（Bounding Box）快速排除不相關物件
精化階段：對候選物件進行精確幾何計算

R-tree 結構示意

Level 0（Root）：涵蓋所有物件的最大外包矩形
Level 1（中間節點）：[北部區域 MBR] [南部區域 MBR]
Level 2（葉節點）：[台北市][基隆市]  [台南市][高雄市]
      ↓                         ↓
  實際幾何物件 TID           實際幾何物件 TID

核心空間函式

ST_Distance — 計算距離

-- Geography：計算球面距離（單位：公尺，精確）
SELECT
    a.name, b.name,
    round(ST_Distance(a.geom::geography, b.geom::geography)::numeric) || ' 公尺' AS distance
FROM locations a, locations b
WHERE a.name = '台北101' AND b.name = '台北車站';

-- KNN 查詢：找出距離最近的 N 個地點（利用 <-> 運算子 + GiST 索引）
SELECT name, category,
       ST_Distance(geom::geography, ST_MakePoint(121.5645, 25.0338)::geography) AS distance_m
FROM locations
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(121.5645, 25.0338), 4326)
LIMIT 10;

ST_DWithin — 範圍內查詢

-- 找出距離台北101 一公里內的所有地點
SELECT name, category,
       round(ST_Distance(geom::geography, ST_MakePoint(121.5645, 25.0338)::geography)::numeric) AS distance_m
FROM locations
WHERE ST_DWithin(
    geom::geography,
    ST_MakePoint(121.5645, 25.0338)::geography,
    1000  -- 1000 公尺 = 1 公里
)
ORDER BY distance_m;

ST_Within / ST_Contains — 包含查詢

-- 查詢哪些地點在信義區內
SELECT l.name AS location_name
FROM locations l
JOIN districts d ON ST_Within(l.geom, d.geom)
WHERE d.name = '信義區';

-- 查詢哪個行政區包含台北101
SELECT d.name AS district_name
FROM districts d
JOIN locations l ON l.name = '台北101'
WHERE ST_Contains(d.geom, l.geom);

ST_Intersects — 相交查詢

-- 找出與特定矩形範圍相交的所有地點
SELECT name
FROM locations
WHERE ST_Intersects(
    geom,
    ST_MakeEnvelope(121.50, 25.02, 121.57, 25.07, 4326)
);

-- 統計各行政區的地點數
SELECT d.name AS district, COUNT(l.id) AS location_count
FROM districts d
JOIN locations l ON ST_Intersects(d.geom, l.geom)
GROUP BY d.name
ORDER BY location_count DESC;

ST_Buffer — 緩衝區分析

-- 建立 500 公尺緩衝區（Geography 精確公尺計算）
SELECT name,
       ST_Buffer(geom::geography, 500)::geometry AS buffer_500m
FROM locations
WHERE name = '台北101';

-- 合併多個點的緩衝區
SELECT ST_Union(ST_Buffer(geom::geography, 300)::geometry)
FROM locations
WHERE category = 'restaurant'
  AND ST_DWithin(geom::geography, ST_MakePoint(121.5645, 25.0338)::geography, 2000);

ST_Transform — 座標系統轉換

-- 從 WGS84 (4326) 轉換為 TWD97 (3826)
SELECT name,
       ST_AsText(geom) AS wgs84,
       ST_AsText(ST_Transform(geom, 3826)) AS twd97
FROM locations WHERE name = '台北101';

-- 不同 SRID 的表格間進行空間查詢
SELECT l.name
FROM locations l              -- SRID=4326
JOIN districts_twd97 d        -- SRID=3826
    ON ST_Within(ST_Transform(l.geom, 3826), d.geom);

實戰查詢模式

模式一：附近搜尋（Nearby Search）

-- 標準附近搜尋：距離使用者位置最近的餐廳
WITH user_location AS (
    SELECT ST_SetSRID(ST_MakePoint(121.5645, 25.0338), 4326) AS geom
)
SELECT
    r.name, r.address, r.rating,
    round(ST_Distance(r.geom::geography, ul.geom::geography)::numeric) AS distance_m
FROM restaurants r, user_location ul
WHERE ST_DWithin(r.geom::geography, ul.geom::geography, 1000)
ORDER BY r.geom <-> ul.geom
LIMIT 20;

模式二：地理圍欄（Geofencing）

-- 建立圍欄
CREATE TABLE geofences (
    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     TEXT NOT NULL,
    boundary geometry(Polygon, 4326),
    active   BOOLEAN DEFAULT TRUE
);

-- 檢查設備是否在圍欄內（IoT 場景）
SELECT d.device_id, g.name AS geofence_name,
       CASE WHEN ST_Within(d.current_location, g.boundary)
            THEN '在圍欄內' ELSE '不在圍欄內' END AS status
FROM devices d
CROSS JOIN geofences g
WHERE g.active = TRUE AND d.device_id = 'DEVICE_001';

-- 偵測進入圍欄事件
SELECT d.device_id, g.name AS entered_geofence
FROM devices d
JOIN geofences g
    ON ST_Within(d.current_location, g.boundary)
       AND NOT ST_Within(d.previous_location, g.boundary)
WHERE g.active = TRUE;

模式三：軌跡分析

-- GPS 軌跡表
CREATE TABLE gps_tracks (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device_id   TEXT NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL,
    location    geometry(Point, 4326)
);

-- 建立路徑線段並計算總距離
SELECT
    device_id,
    ST_MakeLine(location ORDER BY recorded_at) AS track_line,
    round(ST_Length(ST_MakeLine(location ORDER BY recorded_at)::geography)::numeric / 1000, 2) AS total_km
FROM gps_tracks
WHERE device_id = 'DEVICE_001'
  AND recorded_at >= NOW() - INTERVAL '24 hours'
GROUP BY device_id;

模式四：空間聚合

-- 統計各行政區的商店密度
SELECT
    d.name AS district,
    COUNT(s.id) AS store_count,
    round(ST_Area(d.geom::geography)::numeric / 1000000, 2) AS area_km2,
    round(COUNT(s.id)::numeric / (ST_Area(d.geom::geography) / 1000000), 2) AS density_per_km2
FROM districts d
LEFT JOIN stores s ON ST_Within(s.geom, d.geom)
GROUP BY d.id, d.name, d.geom
ORDER BY density_per_km2 DESC;

效能優化

空間索引的正確使用

-- 大多數 ST_* 函式已自動使用 && (Bounding Box) 作為索引過濾
-- 以下查詢會自動利用空間索引
EXPLAIN (ANALYZE, BUFFERS)
SELECT name FROM locations
WHERE ST_DWithin(geom::geography, ST_MakePoint(121.5, 25.0)::geography, 1000);

CLUSTER 按空間排序

-- 按照空間索引順序重組資料，提升空間查詢的 I/O 效率
CLUSTER locations USING idx_locations_geom;
ANALYZE locations;

簡化複雜多邊形

-- Douglas-Peucker 演算法簡化（加速渲染與傳輸）
SELECT
    ST_NPoints(geom) AS original,
    ST_NPoints(ST_SimplifyPreserveTopology(geom, 0.001)) AS simplified
FROM districts WHERE name = '台北市';

切割大型多邊形

-- 超大多邊形降低索引效率，ST_Subdivide 將其切割
CREATE TABLE districts_subdivided AS
SELECT id, name,
       (ST_Subdivide(geom, 128)).* AS geom
FROM districts;

CREATE INDEX idx_districts_sub_geom ON districts_subdivided USING gist(geom);

與前端地圖整合

-- API 端點：GET /api/restaurants/nearby?lat=25.03&lng=121.56&radius=500
SELECT
    id, name, address, rating,
    ST_AsGeoJSON(geom)::json AS geometry,
    round(ST_Distance(geom::geography, ST_MakePoint($lng, $lat)::geography)::numeric) AS distance_m
FROM restaurants
WHERE ST_DWithin(geom::geography, ST_MakePoint($lng, $lat)::geography, $radius)
ORDER BY geom <-> ST_SetSRID(ST_MakePoint($lng, $lat), 4326)
LIMIT 50;

// Leaflet 配合 PostGIS GeoJSON 輸出
const map = L.map('map').setView([25.0338, 121.5645], 14);
L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png').addTo(map);

fetch('/api/districts/geojson')
    .then(res => res.json())
    .then(geojson => {
        L.geoJSON(geojson, {
            style: () => ({ fillColor: '#3388ff', weight: 2, fillOpacity: 0.3 }),
            onEachFeature: (feature, layer) => {
                layer.bindPopup(feature.properties.name);
            }
        }).addTo(map);
    });

版本演進

PostGIS 版本	重要功能改進
2.0	Raster 支援正式整合
2.2	KNN 查詢的 Geography 支援
2.3	並行查詢支援
3.0	Raster 分離為獨立 Extension
3.4	GEOS 3.12 整合、Coverage 函式集

總結

PostGIS 讓 PostgreSQL 成為一個功能完整的地理資訊系統——你可以在同一個資料庫中同時處理業務邏輯與空間分析，不需要額外的 GIS 伺服器。

關鍵要點：

Geography 型別提供精確的球面距離計算（單位公尺），適合全球範圍應用
Geometry + 適當投影（如 TWD97）在區域範圍內計算更快
GiST 空間索引是所有空間查詢效能的基礎，務必建立
ST_DWithin 是最常用的範圍查詢函式，自動利用空間索引
輸出 GeoJSON 格式可直接與 Leaflet、Google Maps 等前端地圖整合

下一篇，我們將探討 TimescaleDB 時序資料——從連續聚合、壓縮策略到即時監控儀表板的完整實戰。

PostGIS 地理空間：從座標系統到空間查詢的完整實戰 | PostgreSQL

GIS 基礎概念

座標系統與 SRID

Geometry vs Geography

安裝 PostGIS

建立空間資料表

插入與匯出空間資料

空間索引

核心空間函式

ST_Distance — 計算距離

ST_DWithin — 範圍內查詢

ST_Within / ST_Contains — 包含查詢

ST_Intersects — 相交查詢

ST_Buffer — 緩衝區分析

ST_Transform — 座標系統轉換

實戰查詢模式

模式一：附近搜尋（Nearby Search）

模式二：地理圍欄（Geofencing）

模式三：軌跡分析

模式四：空間聚合

效能優化

空間索引的正確使用

CLUSTER 按空間排序

簡化複雜多邊形

切割大型多邊形

與前端地圖整合

版本演進

總結

AI 自動化入門包

相關文章

PostGIS 地理空間：從座標系統到空間查詢的完整實戰 | PostgreSQL

GIS 基礎概念

座標系統與 SRID

Geometry vs Geography

安裝 PostGIS

建立空間資料表

插入與匯出空間資料

空間索引

核心空間函式

ST_Distance — 計算距離

ST_DWithin — 範圍內查詢

ST_Within / ST_Contains — 包含查詢

ST_Intersects — 相交查詢

ST_Buffer — 緩衝區分析

ST_Transform — 座標系統轉換

實戰查詢模式

模式一：附近搜尋（Nearby Search）

模式二：地理圍欄（Geofencing）

模式三：軌跡分析

模式四：空間聚合

效能優化

空間索引的正確使用

CLUSTER 按空間排序

簡化複雜多邊形

切割大型多邊形

與前端地圖整合

版本演進

總結

AI 自動化入門包

訂閱 BenzHub 電子報

相關文章