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 的結果呈現在地圖上!