iT邦幫忙

第 11 屆 iT 邦幫忙鐵人賽

DAY 22
1
AI & Data

後端前進PostgreSQL系列 第 22

PostGIS 分析空間資料 (4) - 建立欄位、GiST索引

ALTER TABLE 建立 Geography 欄位

ALTER TABLE adoption_gov_animals ADD COLUMN geog_point geography(POINT, 4326); 

上方 SQL 語法ALTER TABLE關鍵字,可以修改已建立好資料表,把 geog_point 欄位加入adoption_gov_animals (之前匯入行政院農業委員會動物認養的資料) 資料表中,欄位的資料類型是 geography

Update 資料

我想在前端地圖上顯示動物位置的功能,所以打算目前先把動物所在位置設定收容所位置,每筆動物資料加入經緯度並轉成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);

太好了接下來我們就可以來查詢地點的相關功能了!


上一篇
PostGIS 分析空間資料 (3) - 資料類型
下一篇
PostGIS 分析空間資料 (5) - 資料空間的查詢 尋找你的鄰居
系列文
後端前進PostgreSQL30

尚未有邦友留言

立即登入留言