試用 SpatiaLite（二）：進一步測試

Tue, 15 Jan 2013 14:08 之前試過 SpatiaLite 的基本指令，現在我會創建兩個表，一個儲存一些點，另一個儲存多邊形，然後進行操作。在點的表裡面，有兩點在富亨邨裡面，另一點則是九龍坑山。在多邊形的表裡面，我儲存了富亨邨的範圍。它們皆以平面及 WGS 84 為基準儲存。

創建點表：

spatialite> CREATE TABLE PointTable(
id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL);
spatialite> SELECT AddGeometryColumn('PointTable', 'Geometry', 4326, 'POINT', 'XY');
spatialite> SELECT CreateSpatialIndex('PointTable', 'Geometry');

加入資料：

spatialite> INSERT INTO PointTable(Name, Geometry) VALUES ("Cloudy Hill", MakePoint(114.17052,22.475837,4326));
spatialite> INSERT INTO PointTable(Name, Geometry) VALUES ("Fu Heng Sports Centre", MakePoint(114.17134, 22.45822,4326));
spatialite> INSERT INTO PointTable(Name, Geometry) VALUES ("Fu Heng Neighbourhood Community Centre", MakePoint(114.17192, 22.45978,4326));

創建多邊形表：

spatialite> CREATE TABLE PolygonTable(
id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL);
spatialite> SELECT AddGeometryColumn('PolygonTable', 'Geometry', 4326, 'POLYGON', 'XY');
spatialite> SELECT CreateSpatialIndex('PolygonTable', 'Geometry');

加入資料：

spatialite> insert into PolygonTable(Name, Geometry) VALUES ("Fu Heng Estate", ST_GeomFromText("POLYGON ((114.172035 22.456101, 114.172215 22.457397, 114.172390 22.458743, 114.173090 22.460372, 114.173073 22.460731, 114.172988 22.460920, 114.172693 22.461163, 114.172263 22.461304, 114.171984 22.461336, 114.171558 22.461314, 114.171220 22.461232, 114.170882 22.461051, 114.170536 22.460751, 114.170297 22.460357, 114.170056 22.459269, 114.170026 22.457333, 114.169745 22.456726, 114.170751 22.456232, 114.170952 22.456165, 114.172035 22.456101))" ,4326));

利用 ST_Within 選取在富亨邨內的點：

spatialite> SELECT a.id, a.Name, b.Name FROM PointTable a JOIN PolygonTable b ON ST_Within(a.Geometry, b.Geometry);
2|Fu Heng Sports Centre|Fu Heng Estate
3|Fu Heng Neighbourhood Community Centre|Fu Heng Estate

利用 ST_Within 選取在富亨邨外的點及拉一條直線：

spatialite> SELECT a.id, a.Name, AsText(ST_ShortestLine(a.Geometry, b.Geometry)) FROM PointTable a JOIN PolygonTable b ON ST_Within(a.Geometry, b.Geometry) = 0;
1|Cloudy Hill|LINESTRING(114.17052 22.475837, 114.171558 22.461314)

將富亨邨輸出為 KML：

spatialite> SELECT AsKml("Fu Heng Estate", "description", Geometry) FROM PolygonTable;
<Placemark><name>Fu Heng Estate</name><description>description</description><Polygon><outerBoundaryIs><LinearRing><coordinates>114.1720349999999,22.456101 114.1722149999999,22.457397 114.1723899999999,22.45874299999999 114.17309,22.460372 114.173073,22.46073099999999 114.172988,22.46092 114.1726929999999,22.46116299999999 114.172263,22.46130399999999 114.1719839999999,22.46133599999999 114.171558,22.461314 114.17122,22.46123199999999 114.170882,22.461051 114.1705359999999,22.46075099999999 114.170297,22.46035699999999 114.170056,22.45926899999999 114.1700259999999,22.45733299999999 114.169745,22.456726 114.1707509999999,22.456232 114.170952,22.45616499999999 114.1720349999999,22.456101</coordinates></LinearRing></outerBoundaryIs></Polygon></Placemark>

利用 Google Earth 打開：

將富亨邨的經緯度數據，轉成 UTM 座標：

spatialite> SELECT AsText(ST_Transform(Geometry,32650)) FROM PolygonTable;
POLYGON((208953.438359 2486057.474384, 208974.683325 2486200.706368, 208995.517737 2486349.487494, 209071.004741 2486528.60177, 209070.004524 2486568.407811, 209061.646822 2486589.511938, 209031.777666 2486617.006648, 208987.794009 2486633.463344, 208959.131436 2486637.550767, 208915.218906 2486635.941392, 208880.242496 2486627.513759, 208845.058955 2486608.118139, 208808.802586 2486575.554312, 208783.367602 2486532.368361, 208756.274535 2486412.299078, 208749.134927 2486197.870821, 208718.928648 2486131.168617, 208821.489912 2486074.483122, 208842.048155 2486066.669576, 208953.438359 2486057.474384))

計算富亨邨的面積，以平方米為單位（先要將它轉成 UTM 計算）：

spatialite> SELECT ST_Area(ST_Transform(Geometry,32650)) FROM PolygonTable;
135880.53978552

計算富亨邨的中心點：

spatialite> SELECT AsText(ST_Centroid(Geometry)) FROM PolygonTable;
POINT(114.171362 22.458814)

在 Google Earth 上標示：

計算富亨邨和九龍坑山的最接近距離：

spatialite> SELECT a.Name, b.Name, ST_Distance(ST_Transform(a.Geometry,32650), ST_Transform(b.Geometry,32650)) FROM PointTable a JOIN PolygonTable b ON ST_Within(a.Geometry, b.Geometry) = 0;
Cloudy Hill|Fu Heng Estate|1612.81591309232

計算富亨邨和九龍坑山的最大距離：

spatialite> SELECT a.Name, b.Name, ST_MaxDistance(ST_Transform(a.Geometry,32650), ST_Transform(b.Geometry,32650)) FROM PointTable a JOIN PolygonTable b ON ST_Within(a.Geometry, b.Geometry) = 0;
Cloudy Hill|Fu Heng Estate|2192.47093811504

計算不同點之間的距離：

spatialite> SELECT a.Name, b.Name, ST_Distance(ST_Transform(a.Geometry,32650), ST_Transform(b.Geometry,32650)) FROM PointTable a JOIN PointTable b ON a.id != b.id;
Cloudy Hill|Fu Heng Sports Centre|1953.93589287626
Cloudy Hill|Fu Heng Neighbourhood Community Centre|1785.08117583597
Fu Heng Sports Centre|Cloudy Hill|1953.93589287626
Fu Heng Sports Centre|Fu Heng Neighbourhood Community Centre|182.891290344748
Fu Heng Neighbourhood Community Centre|Cloudy Hill|1785.08117583597
Fu Heng Neighbourhood Community Centre|Fu Heng Sports Centre|182.891290344748

計算不同點之間的角度，例如由富亨鄰里社區中心到富亨體育館的角度為 200 度：

spatialite> SELECT a.Name, b.Name, Degrees(ST_Azimuth(a.Geometry, b.Geometry)) FROM PointTable a JOIN PointTable b ON a.id != b.id;
Cloudy Hill|Fu Heng Sports Centre|177.335036974734
Cloudy Hill|Fu Heng Neighbourhood Community Centre|175.017017424819
Fu Heng Sports Centre|Cloudy Hill|357.335036974734
Fu Heng Sports Centre|Fu Heng Neighbourhood Community Centre|20.3948760820543
Fu Heng Neighbourhood Community Centre|Cloudy Hill|355.017017424819
Fu Heng Neighbourhood Community Centre|Fu Heng Sports Centre|200.394876082054

利用 R* Index，找出在 22.46N 114.17E 和 22.44N 114.18E 內的點。由於用了 R* Index，所以在大量數據時，效率會高於 ST_Within，但就只能用長方形查詢：

spatialite> SELECT id, Name FROM PointTable WHERE ROWID IN (SELECT pkid FROM idx_PointTable_Geometry WHERE pkid MATCH RTreeIntersects(114.17,22.46,114.18,22.44));
2|Fu Heng Sports Centre
3|Fu Heng Neighbourhood Community Centre

也可利用 R* Index 找出範圍內的多邊形：

spatialite> SELECT id, Name FROM PolygonTable WHERE ROWID IN (SELECT pkid FROM idx_PolygonTable_Geometry WHERE pkid MATCH RTreeIntersects(114.17,22.46,114.18,22.44));
1|Fu Heng Estate

利用長方形框起富亨邨：

spatialite> SELECT AsText(ST_Envelope(Geometry)) FROM PolygonTable WHERE id = 1;
POLYGON((114.169745 22.456101, 114.17309 22.456101, 114.17309 22.461336, 114.169745 22.461336, 114.169745 22.456101))

意義跟上一句相近：

spatialite> SELECT ST_MinX(Geometry),ST_MaxY(Geometry),ST_MaxX(Geometry),ST_MinY(Geometry) FROM PolygonTable WHERE id = 1;
114.169745|22.461336|114.17309|22.456101

利用這長方形，配合 R* Index，找出富亨邨內的點，但由於用了長方形，可能有些其他邨外的點跌入此範圍：

spatialite> SELECT a.id, a.Name FROM PointTable a LEFT JOIN PolygonTable b WHERE b.id = 1 AND a.ROWID IN (SELECT pkid FROM idx_PointTable_Geometry WHERE pkid MATCH RTreeIntersects(ST_MinX(b.Geometry),ST_MaxY(b.Geometry),ST_MaxX(b.Geometry),ST_MinY(b.Geometry)));
2|Fu Heng Sports Centre
3|Fu Heng Neighbourhood Community Centre

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

本文連結