使用 SQL PostGIS 計算距離

簡單認識 PostGIS

PostGIS 是地理圖資的管理模組，透過這個功能可以非常輕易的計算出點與點距離、線、面等不同的空間運用，減少程式碼上的計算來增加效能，更為強大的是可以做空間運算。

本篇介紹使用而不介紹安裝方式，但在安裝 PostgreSQL 時便需要將這個模組一起安裝近來才能夠使用這個強大而方便的功能。

資料格式

在使用 node.js 的 sequelize 設定， model 可以設定成 geometry 的資料格式，而這一類型的資料是在平面上計算座標，對於地球圓形體而言，在長距離的計算下是會有所誤差的，因此還會有 geography 另一種圓形表體座標系統的出現，這次只認識 geometry。

sequelize.org

有做了簡單的介紹，可惜的是， sequelize 的資料套件並未對此部分做到最新的更新，在部分的使用上必須仰賴 QueryInterface 來完成正常顯示。

model

// 格式
DataTypes.GEOMETRY;
DataTypes.GEOMETRY("POINT");
DataTypes.GEOMETRY("POINT", 4326);

migrations

geometry: {
  type: Sequelize.GEOMETRY;
}

有趣的是在資料庫不同雖然是同樣的資料，在呈現上也有所差異，第一張為 PostgreSQL，第二張為MySQL。

PostgreSQL

MySQL

在 JSON 的呈現上，是如 sequelize 範例一樣

const point = { type: 'Point', coordinates: [39.807222,-76.984722]};

SRID

SRID 全名 Spatial Reference System Identifier，記不住，翻譯過來中文為空間參考識別碼。

這次我使用的只有 WGS 84 的 EPSG: 4326，是為了表示經緯度而使用，另外還有 EPSG: 900913 的距離測量系統，這次不會用到

參考: stackexchange

使用 sequelize 寫入地理位置

由於 PostGIS 是一個非常活耀的專案，所以版本上的更新也非常的快速，在不同的版本間可能會有語法上的差異或者汰換，最好還是以官方文件為主。

從表單取得經緯度利用 QueryInterface 將語法組裝後才寫入。

const location = sequelize.literal(`ST_GeomFromText('POINT(${req.user.lng} ${req.user.lat})')`)

在經緯度的表示上必須看使用的資料系統，所以有時候會是 latitude 在前，有時候在後。
wiki

計算距離 ST_DistanceSphere 、 ST_Distance_Sphere，前者為 PostgreSQL v11 以上使用，後者為舊版本或者 mySQL 使用。

const location = sequelize.literal(`ST_GeomFromText('POINT(${req.user.lng} ${req.user.lat})')`)

// PostgreSQL
const distance = sequelize.fn('ST_DistanceSphere', sequelize.literal('geometry'), location)
// MySQL
const distance = sequelize.fn('ST_Distance_Sphere', sequelize.literal('geometry'), location)

Model.findAll({
  where: sequelize.where(distance, { [Op.lte]: 10000 }), // 距離為公尺
})

這樣便可以透過 sequelize 完成簡單的寫入以及查詢範圍內的資料。

heroku Postgres PostGIS 設定

同樣的在 Heroku 使用 postgres 時也可以設定 postgis 的功能，連結。

但在設定上有些小麻煩的是，我們必須在本地也安裝 Postgres，並且設定好本地路徑的連結，否則會連不上後面的動作，都設定完成後可以參考 heroku 這篇說明。

windows 安裝後，路徑設定，參考 pg:psql might not work on windows?，set PATH=%PATH%;C:\Program Files\PostgreSQL\11\bin\ 或者 C:\Program Files\PostgreSQL\11

在 terminal 上，指令 local setup

set DATABASE_URL=postgres://$(whoami)

在 whoami 裡打上，本地資料庫的名稱。

接著便可以開始設定遠端 heroku 上的 PostgreSQL

heroku pg:psql
=> create extension postgis;

如果沒有意外這樣就完成了設定，可以將 geometry 的資料放入資料庫中了。

額外參考資料: microsoft