PostGIS：使用 PostgreSQL 的地理資訊系統

PostgreSQL 應該是目前唯一有提供地理資訊系統來當作型別的資料庫了，只要加上 PostGIS 的功能，就能夠輕易地把某個座標加入資料庫，並輕易地透過 SQL 指令算出兩點之間距離，甚至是列出某座標方圓x公里內的其他座標。

感謝網友觀月唯補充：MSSQL 也有地理資訊系統當作型別。

嗯，功能大致上是懂了，不過到底是 PostGIS 是資料庫，還是 PostgreSQL 是資料庫？

其實我第一次聽到這兩個名詞也搞得霧煞煞，總之 PostgreSQL(一套資料庫) + OpenGIS(地理資訊系統) = PostGIS

安裝 PostgreSQL

這裡一樣以 Debian 系統為例。

首先安裝 postgresql：

sudo apt-get install postgresql

接著安裝 postgis (可以先用 apt-cache search postgis 搜尋一下最新的版本是多少再安裝)：

sudo apt-get install postgresql-9.4-postgis-2.1

安裝過程中會自動幫我們建立 postgres 這個使用者，我們現在要先更改這隻使用者的密碼。

首先，先切換到 postgres 使用者，並開啟 psql (PostgreSQL 的介面)

su postgres -c psql template1

接著在 psql 裡面輸入

ALTER USER postgres WITH PASSWORD '<你的密碼>';

接著用 \q 離開，回到 bash 後繼續修改 postgres 密碼：

sudo passwd postgres

並輸入密碼兩次。

聽起來有點複雜，補個圖 XD

在資料庫裡匯入 PostGIS 的資料

剛剛安裝完 PostgreSQL 和 PostGIS 後，我們現在要建立一個含有 GIS 資訊的資料庫。例如我們就建立一個 mydb 好了。

首先切換到 postgres 帳號：

su postgres

接著新增資料庫：

createdb mydb

並讓資料庫支援 PL/pgsql 語言：

createlang plpgsql mydb

接著要匯入三個 GIS 資料到資料庫裡面(可能因為版本不同所以目錄不同，匯入前可以先 cd 找一下)：

psql -d mydb -f /usr/share/postgresql/9.4/contrib/postgis-2.1/postgis.sql
psql -d mydb -f /usr/share/postgresql/9.4/contrib/postgis-2.1/spatial_ref_sys.sql
psql -d mydb -f /usr/share/postgresql/9.4/contrib/postgis-2.1/postgis_comments.sql

到這裡你就成功建立一個有 PostGIS 的資料庫了。但繼續之前要再介紹一下 SRID 這個東西。

SRID 是什麼？

SRID，Spatial Reference System Identifier，空間參考識別碼，不同的識別碼定義了不同的空間參考系統。

這裡我們只會用到兩個：4326 跟 900913。當你要用經緯度的時候就用 4326，當你要用公尺的時候就用 900913。

好了，SRID 暫時理解到這裡就可以了，如果想要知道更多有關 SRID 的資料，可以到 SpatialReference.org 看看。

建立含地理資訊系統的欄位

有了資料庫後，也要有個欄位是符合 PostGIS 的。首先我們切換到 postgres 帳號並進到 psql 裡面，原則上之後進 psql 都用這個方法：

su postgres -c "psql -d mydb"

建立一個 sequence：

CREATE SEQUENCE points_id_seq;

建立一個 points 表格，並讓 id 隨著剛剛的 sequence 自動增加、還有一個 name 欄位：

CREATE TABLE points ( id INTEGER PRIMARY KEY DEFAULT nextval('points_id_seq'), name VARCHAR(40) );

在 points 表格裡面增加 location 欄位，使用 4326 的 SRID：

SELECT AddGeometryColumn('points', 'location', 4326, 'POINT', 2);

接著建立索引：

CREATE INDEX points_location_idx on points using GIST (location);

到這邊，我們就有一張表格可以丟座標了。

插入座標到表格

接下來我們會對表格操作，插入座標、查詢等等。如果對 SQL 不熟，不知道自己在幹嘛的話，可以安裝 phppgadmin，操作方法類似 phpmyadmin，就是一套 Web-based 的 PostgreSQL 介面：

apt-get install phppgadmin

一樣先進到 psql 裡面：

su postgres -c "psql -d mydb"

接著新增座標，例如臺北101：

INSERT INTO points(name, location) VALUES ('Taipei 101', ST_GeomFromText('POINT(121.5646380 25.0339138)', 4326));

再新增一點臺科大：

INSERT INTO points(name, location) VALUES ('ntust', ST_GeomFromText('POINT(121.5394628 25.0130727)', 4326));

我們使用了 ST_GeomFromText 的函式，他可以把文字轉成座標。另外因為我們使用經緯度，所以使用 4326 這個 SRID。這樣就完成座標的插入了。

查詢資料

把資料新增進去後，接下來就是查詢了。像剛剛提到的查詢兩點距離可以這樣下：

SELECT ST_Distance( ST_Transform((SELECT location FROM points WHERE name='Taipei 101'), 900913), ST_Transform((SELECT location FROM points WHERE name='ntust'), 900913) )as distance;

這樣就能算出臺科大到臺北 101 的距離了。

也可以試著查詢和臺北101相距 4000 公尺內的點(排除掉自己)：

SELECT * FROM points WHERE ST_Distance( ST_Transform(location, 900913), ST_Transform((SELECT location FROM points WHERE name='Taipei 101'), 900913) ) < 4000 and name<>'Taipei 101';

由於我們只加入兩點，所以應該只會看到臺科大出現在結果裡。

這次 PostGIS 先介紹到這裡，下次會介紹如何搭配 OpenStreetMap 和 Leaflet 把 PostGIS 的結果呈現在地圖上！