ALTER TABLE adoption_gov_animals ADD COLUMN geog_point geography(POINT, 4326);
上方 SQL 語法ALTER TABLE
關鍵字,可以修改已建立好資料表,把 geog_point
欄位加入adoption_gov_animals
(之前匯入行政院農業委員會動物認養的資料) 資料表中,欄位的資料類型是 geography
。
我想在前端地圖上顯示動物位置的功能,所以打算目前先把動物所在位置設定收容所位置,每筆動物資料加入經緯度並轉成geography格式,存放於欄位中。
UPDATE adoption_gov_animals
SET geog_point =
ST_SetSRID(
ST_MakePoint(longitude,latitude),4326
)::geography;
這一個方法 ST_SetSRID
由PostGIS 支援的方法,輸出結果是 geometry
類型,但需求是轉換成 geography
的資料類型,才能符合我們設定的欄位類型,為了轉換資料類型,我們使用PostgreSQL 特有的雙冒號語法 :: 轉換想要的類型。
把這整張表(adoption_gov_animals
) 的經緯度欄位轉換成geography
格式,並寫入 geog_point
欄位中。
這裡有兩個東西我們需要準備的!就是要去查收容所的座標(我們欄位裡面沒有longitude ,latitude 經緯度)
先查詢公開資料的收容所相關欄位,分別是 pkid ,name, address
--只用收容所地址查詢,列出不重複的項目。我匯入的資料共32筆
SELECT DISTINCT shelter_address
FROM adoption_gov_animals
ORDER BY shelter_address;
-- 用收容所相關欄位 pkid ,name, address 發現多一筆! 共33筆
SELECT DISTINCT shelter_pkid,shelter_name, shelter_address
FROM adoption_gov_animals
ORDER BY shelter_name;
但我們最主要是要去找地址的座標,所以比較沒有差,但手癢想看看這公開資料的狀態,本來還以為資料會很髒,但是也還好!
多的那一筆我用肉眼查看, 屏東縣公立犬貓中途之家
原本名稱 屏東縣流浪動物收容所
所以出現兩筆地址都相同
這裡手動加入,可以去這個網站用地址查座標
https://www.tgos.tw/TGOS/Web/Address/TGOS_Address.aspx
輸入地址後按下查詢,會出現下面的畫面 小視窗中點選座標頁籤在選擇WGS84 下方就有座標囉!
UPDATE adoption_gov_animals
SET geog_point =
ST_SetSRID(
ST_MakePoint(121.533012,25.042385),4326
)::geography
WHERE shelter_pkid = 77;
其他以此類推!就可以把空間資料存入geog_point
欄位中囉!
在開始查詢分析空間資料前,最好可以先為欄位加上索引,以便加速計算。
我們用 GiST 式的索引,這樣才可以比較空間的大小距離。
CREATE INDEX adoption_gov_animals_idx ON adoption_gov_animals USING GIST (geog_point);
太好了接下來我們就可以來查詢地點的相關功能了!