試用 SpatiaLite（四）：郵筒資料庫

在之前測試的時候，我用的是自己輸入的資料，這次我就嘗試從政府地圖下載資料，這次選用的是香港郵筒資料。它提供的是 CSV 格式，裡面提供了以 HK1980 為基準的格網座標。下載後先打開，轉換成 UTF-8 格式，然後再以視窗系統上的 Spatialite_gui 匯入到資料庫中。匯入之後，要先將欄名改回英文，也要刪除不需用的欄。

從這裡下載 CSV 格式的檔案：
hkpost10

在這裡匯入：
hkpost11

選取合適的 Text Separator、Column Separator 之類，Encoding 為 UTF-8：
hkpost12

由於表本身並沒有 Geometry 欄，所以要手動加入。由於本身的是 HK1980 格網座標，所以有一欄是 HK1980。另一欄是以 WGS84 經緯度儲存，以方便搜尋。

spatialite> SELECT AddGeometryColumn('hkpost', 'geometry_hk1980', 2326, 'POINT', 'XY');
spatialite> SELECT CreateSpatialIndex('hkpost', 'geometry_hk1980');

spatialite> SELECT AddGeometryColumn('hkpost', 'geometry_wgs84', 4326, 'POINT', 'XY');
spatialite> SELECT CreateSpatialIndex('hkpost', 'geometry_wgs84');

由於不能利用 SQL 直接將 Northing 和 Easting 欄轉到新的 Geometry 欄，所以我寫了一段 PHP 程式去處理，原理是利用程式讀出每一行，再更新該行。為了令 PHP 也支援 SpatitaLite，需先在 php.ini 作出修改，方法可參閱這裡：
hkpost13

那段 PHP 程式就如以下所示：
<?php
$db = new SQLite3('db.sqlite');
$db->loadExtension('libspatialite.so');
$db->exec("SELECT InitSpatialMetadata()");

$sql = "SELECT * FROM hkpost;";
$rs = $db->query($sql);
while ($row = $rs->fetchArray()){
	$sql2 = "UPDATE hkpost SET geometry_hk1980 = MakePoint(" .$row["EASTING"] . "," . $row["NORTHING"] .", 2326), geometry_wgs84 = st_transform(MakePoint(" .$row["EASTING"] . "," . $row["NORTHING"] .", 2326),4326)  WHERE PK_UID = " . $row["PK_UID"] .";";
	$db->query($sql2);
}

$db->close();
?>

完成之後，原本的 Northing 和 Easting 欄便可刪除。之後便可進行測試。

整個 geometry_wgs84 的郵筒分佈：
hkpost1

檢視 Geometry 的點資料：
hkpost2

查詢最接近 22.4518N 114.1689E 的郵筒和距離（以米為單位）：
spatialite> SELECT CHI_NAME, CHI_ADDRESS
ST_Distance(geometry_hk1980, ST_Transform(MakePoint(114.1689, 22.4518, 4326), 2326)) as distance
FROM hkpost
ORDER BY distance ASC;

hkpost3

查詢距離 22.4518N 114.1689E 最遠的郵筒和距離，果然是大澳是最遠：
spatialite> SELECT CHI_NAME, CHI_ADDRESS
ST_Distance(geometry_hk1980, ST_Transform(MakePoint(114.1689, 22.4518, 4326), 2326)) as distance
FROM hkpost
ORDER BY distance DESC;

hkpost4

選出全香港距離最遠的郵筒組合，在六十幾萬個組合中，得出結果是西貢跟大澳：
spatialite> SELECT a.chi_name, a.chi_Address, b.chi_name,  b.chi_Address,
ST_Distance(a.Geometry_hk1980,b.Geometry_hk1980) as distance 
FROM hkpost a 
JOIN hkpost b 
ON a.pk_uid > b.pk_uid 
ORDER BY distance desc;

hkpost5

全港各區的郵筒分佈：
spatialite> SELECT chi_district, count(*) as count FROM hkpost GROUP BY chi_district ORDER BY count DESC;
中西區	115
油尖旺區	97
觀塘區	80
元朗區	73
東區	71
沙田區	69
西貢區	67
南區	64
灣仔區	64
葵青區	58
屯門區	57
離島區	56
北區	53
深水埗區	53
大埔區	46
荃灣區	45
黃大仙區	39
九龍城區	29

利用 GROUP BY 和 ST_Union 去組合各區的點資料：
spatialite> SELECT chi_district, ST_Union(geometry_wgs84) FROM hkpost GROUP BY chi_district;

hkpost7

以下意義類同的查詢，顯示出利用 R* Index，即第一組查詢，佔用的 CPU 資源低得多：
spatialite> SELECT CHI_NAME, CHI_ADDRESS, astext(geometry_wgs84) FROM hkpost WHERE ROWID IN (SELECT pkid FROM idx_hkpost_geometry_wgs84 WHERE pkid MATCH RTreeIntersects(114.1669,22.3061,114.1760,22.2976));
編號196郵箱|柯士甸道37號∕廟街|POINT(114.169943 22.303236)
編號81郵箱|尖沙咀廣東道54-66號∕海防道|POINT(114.168865 22.298147)
編號52郵箱|油麻地佐敦道37號∕上海街|POINT(114.169586 22.305514)
編號211郵箱|廣東道393號廣東道政府合署外|POINT(114.167774 22.303516)
編號GVR郵箱|加連威老道郵政局|POINT(114.172633 22.299424)
編號3郵箱|柯士甸路 25號 / 柯士甸道|POINT(114.174403 22.303221)
編號595郵箱|廣東道海防道54-66號|POINT(114.168898 22.298087)
編號437郵箱|油麻地佐敦道10號 / 德興街|POINT(114.172351 22.305537)
編號2郵箱|金馬倫道 11號/ 加拿芬道|POINT(114.173413 22.298972)
編號27郵箱|尖沙咀彌敦道172-174號∕山林道|POINT(114.171926 22.302515)
編號26郵箱|油麻地佐敦道10號 / 德興街|POINT(114.172221 22.30542)
編號28郵箱|尖沙咀彌敦道99號∕海防道|POINT(114.172013 22.298069)
編號552郵箱|佐敦港鐵站近E出口|POINT(114.171965 22.304369)
編號455郵箱|尖沙咀廣東道中港城平台第一座|POINT(114.16748 22.300183)
編號128郵箱|尖沙咀彌敦道美麗華酒店門口|POINT(114.172104 22.300154)
CPU Time: user 0.000000 sys 0.000000

spatialite> SELECT CHI_NAME, CHI_ADDRESS, astext(geometry_wgs84) FROM hkpost WHERE ST_Within(geometry_wgs84, PolygonFromText("POLYGON((114.1669 22.3061, 114.1760 22.3061, 114.1760 22.2976, 114.1669 22.2976, 114.1669 22.3061))", 4326));
編號196郵箱|柯士甸道37號∕廟街|POINT(114.169943 22.303236)
編號81郵箱|尖沙咀廣東道54-66號∕海防道|POINT(114.168865 22.298147)
編號52郵箱|油麻地佐敦道37號∕上海街|POINT(114.169586 22.305514)
編號211郵箱|廣東道393號廣東道政府合署外|POINT(114.167774 22.303516)
編號GVR郵箱|加連威老道郵政局|POINT(114.172633 22.299424)
編號3郵箱|柯士甸路 25號 / 柯士甸道|POINT(114.174403 22.303221)
編號595郵箱|廣東道海防道54-66號|POINT(114.168898 22.298087)
編號437郵箱|油麻地佐敦道10號 / 德興街|POINT(114.172351 22.305537)
編號2郵箱|金馬倫道 11號/ 加拿芬道|POINT(114.173413 22.298972)
編號27郵箱|尖沙咀彌敦道172-174號∕山林道|POINT(114.171926 22.302515)
編號26郵箱|油麻地佐敦道10號 / 德興街|POINT(114.172221 22.30542)
編號28郵箱|尖沙咀彌敦道99號∕海防道|POINT(114.172013 22.298069)
編號552郵箱|佐敦港鐵站近E出口|POINT(114.171965 22.304369)
編號455郵箱|尖沙咀廣東道中港城平台第一座|POINT(114.16748 22.300183)
編號128郵箱|尖沙咀彌敦道美麗華酒店門口|POINT(114.172104 22.300154)
CPU Time: user 0.024001 sys 0.000000

利用 R* Index 的距離功能查詢，但有可能會有距離比我們要求的大的情況出現，需要用 distance <= 500 去過濾（如第二組查詢）：
spatialite> SELECT CHI_NAME, CHI_ADDRESS, astext(geometry_wgs84), ST_Distance(geometry_hk1980, ST_Transform(MakePoint(114.1669, 22.3061, 4326), 2326)) as distance FROM hkpost WHERE ROWID IN (SELECT pkid FROM idx_hkpost_geometry_hk1980 WHERE pkid MATCH RTreeDistWithin(x(ST_Transform(MakePoint(114.1669, 22.3061, 4326), 2326)),y(ST_Transform(MakePoint(114.1669, 22.3061, 4326), 2326)),500)) ORDER BY distance ASC;
編號212郵箱|油麻地渡船街文英街文英樓|POINT(114.167172 22.307118)|116.18388840508
編號52郵箱|油麻地佐敦道37號∕上海街|POINT(114.169586 22.305514)|284.245849629527
編號211郵箱|廣東道393號廣東道政府合署外|POINT(114.167774 22.303516)|299.986834612673
編號125郵箱|油麻地上海街168-170號∕西貢街|POINT(114.169756 22.307135)|315.794725948703
編號196郵箱|柯士甸道37號∕廟街|POINT(114.169943 22.303236)|445.980469925132
編號KCL郵箱|九龍中央郵政局|POINT(114.170964 22.309439)|558.6265078879
CPU Time: user 0.000000 sys 0.000000

spatialite> SELECT CHI_NAME, CHI_ADDRESS, astext(geometry_wgs84), ST_Distance(geometry_hk1980, ST_Transform(MakePoint(114.1669, 22.3061, 4326), 2326)) as distance FROM hkpost WHERE ROWID IN (SELECT pkid FROM idx_hkpost_geometry_hk1980 WHERE pkid MATCH RTreeDistWithin(x(ST_Transform(MakePoint(114.1669, 22.3061, 4326), 2326)),y(ST_Transform(MakePoint(114.1669, 22.3061, 4326), 2326)),500)) AND distance <= 500 ORDER BY distance ASC;
編號212郵箱|油麻地渡船街文英街文英樓|POINT(114.167172 22.307118)|116.18388840508
編號52郵箱|油麻地佐敦道37號∕上海街|POINT(114.169586 22.305514)|284.245849629527
編號211郵箱|廣東道393號廣東道政府合署外|POINT(114.167774 22.303516)|299.986834612673
編號125郵箱|油麻地上海街168-170號∕西貢街|POINT(114.169756 22.307135)|315.794725948703
編號196郵箱|柯士甸道37號∕廟街|POINT(114.169943 22.303236)|445.980469925132
CPU Time: user 0.000000 sys 0.000000

內部連結：
【目錄】地理／地理資訊系統／空間資料庫／大地測量內部連結

本文連結