PostgreSQL 打造地理位置應用：輕量級 Cube + Earthdistance 實戰

前言：為什麼選擇資料庫端計算？

開發基於地理位置的應用（LBS）時，將距離計算下推至資料庫層是效能最優解：資料已在伺服器端、C/C++ 實現的擴充套件極快、且可利用 GIST 索引加速空間查詢。本文介紹 PostgreSQL 輕量級組合——cube 與 earthdistance，無需龐大的 PostGIS 即可快速構建「附近的人/事」功能。

一、兩種 Geo 方案比較

若你只需「兩點距離」、「半徑篩選」、「最近 N 筆」，Cube + Earthdistance 是最佳 CP 值之選。

二、環境建置與啟用擴充套件

-- 1. 建立資料庫（略）
-- 2. 啟用擴充套件（需 superuser 權限）
CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;
-- 兩指令共建立約 40 個函數與操作子

三、建立範例資料表

CREATE TABLE events (
  id        SERIAL PRIMARY KEY,
  name      VARCHAR(255),
  lat       DOUBLE PRECISION,  -- 緯度
  lng       DOUBLE PRECISION   -- 經度
);

使用 DOUBLE PRECISION 儲存經緯度，兼容 Node.js 等應用層無需自訂型別轉換。

四、核心函數速查表

大圓距離：球面上兩點最短路徑（黎曼圓弧長），符合真實導航距離。

五、實戰查詢範例

1. 依距離排序：找出最近的事件

SELECT
  id,
  name,
  earth_distance(
    ll_to_earth(:my_lat, :my_lng),
    ll_to_earth(lat, lng)
  ) AS dist_m
FROM events
ORDER BY dist_m ASC
LIMIT 10;

回傳最近 10 筆事件，距離單位為公尺。

2. 半徑篩選：找出 5 公里內的事件（搭配索引）

-- 建立 GIST 索引（關鍵加速步驟）
CREATE INDEX idx_events_geo ON events USING GIST (ll_to_earth(lat, lng));

-- 查詢：利用 earth_box 先粗略過濾，再精確計算
SELECT id, name
FROM events
WHERE earth_box(ll_to_earth(:my_lat, :my_lng), 5000) @> ll_to_earth(lat, lng)
  AND earth_distance(ll_to_earth(:my_lat, :my_lng), ll_to_earth(lat, lng)) <= 5000;

• @> 為 Cube 的「包含」操作子，配合 GIST 索引極速篩選候選集。
• 外層再用 earth_distance 精確剔除立方體角落誤差。

六、效能調優關鍵

1. 務必建立 GIST 索引：CREATE INDEX ... USING GIST (ll_to_earth(lat, lng));
2. 經緯度欄位使用 DOUBLE PRECISION，避免 NUMERIC 運算較慢。
3. 大量資料時，可考慮 CLUSTER 依索引重排資料頁，提升序列掃描效率。

七、總結

僅需兩行 CREATE EXTENSION、一個 GIST 索引、標準 DOUBLE PRECISION 欄位，即可在 PostgreSQL 內建強大的 LBS 查詢能力：

• ✅ 兩點距離（公尺級精度）
• ✅ 圓形範圍搜尋（索引加速）
• ✅ 依距離排序（Top-N）
• ✅ 零外部依賴、部署極簡

下次專案需要「附近的咖啡廳」、「派單給最近騎手」時，請優先評估 Cube + Earthdistance，輕量上手、效能驚人。