本系列文章已出版實體書籍：
「你的地圖會說話？WebGIS 與 JavaScript 的情感交織」（博碩文化）
WebGIS啟蒙首選✖五家地圖API✖近百個程式範例✖實用簡易口訣✖學習難度分級✖補充ES6小知識

今天邁入鐵人賽第十篇啦！(撒花～
接著還有二十篇要與大家繼續努力！

寫了那麼多JavaScript想必大家也有點倦了，
今天就來講WebGIS不可或缺的SQL好朋友！

空間查詢

假設我們有一個旅遊景點資料庫，有許多景點的點資料，我們想要把它秀在地圖上。
如果把它們全部秀出來的話，過多的點呈現除了影響圖台效能外，
還會令使用者在視覺上失焦。因此，我們通常會做基本的篩選，
像Google地圖搜尋列只會搜尋到畫面範圍的資訊，不知道大家有沒有注意到？
那麼，資料庫是怎麼篩選畫面中的點資料呢？
抑或是我想要畫一個圓，想要篩選範圍內的資料(buffer)，要怎麼做呢？
今天我們要用MSSQL Server來實現！
(version: SQL SERVER 2008 以上版本(含)，需支援 geomertry 資料屬性。)

資料庫儲存格式

資料庫存放地理空間資料格式，支援以下兩種格式，為開放式地理空間協會(OGC)所定義。

• WKB (well-known binary)
• WKT (well-known text)

WKT 格式

WKT就是以文字表示來儲存空間資訊的格式，最前面依照不同的資料型態分為:

• POINT 點
• LINESTRING 線
• POLYGON 面

還有幾種綜合點線面的組合:

• MULTIPOINT 多重點
• MULTILINESTRING 多重線
• MULTIPOLYGON 多重面
• GeometryCollection 地理空間資訊集合

↓ 用 維基百科wiki 的一張圖來解釋。

大家可能會好奇，為什麼不同種資料型態小括號的數量都不一樣？其實每一層小括號的意義都有所不同。

• 第一層小括號(最內層): 存放一個點或多個點，若存放多個點會把多個點連線。
• 第二層小括號: 如果第一層小括號有線，會把連線區域的內部填滿成面；如果內有多組第一層小括號的線段，會形成多個面，如果有重疊則會挖空重疊的部份。
• 最外層: Multi開頭的組合，於最外層還會加上一層小括號，表示多種組合的意思。

WKB 格式

從GIS Server匯出資料進SQL Server時，會以二進位的格式存放。結果如下 ↓

↓ 或者是我們拿到WKT，可以用geometry::STGeomFromText的方式轉成WKB

DECLARE @p geometry;
SET @p = geometry::STGeomFromText('POLYGON ((30 20, 45 40, 10 40, 30 20),(15 5, 40 10, 10 20, 5 10, 15 5))', 0);
SELECT @p AS geo

空間資訊欄位型別還有geometry跟geography之分。

• geography適用球體運算，使用經緯度座標，空間資訊代碼填4326。
• geometry適用平面運算，適用經過投影過後的座標，台灣常見的座標格式有TWD97二度分帶。

geometry::STGeomFromText(obj,srs)   -- obj: WKTString
geography::STGeomFromText(obj,srs)  -- srs: 座標系統，EX: 4326、3826、3857、0

座標系統及單位參考

到底有幾種座標系統？要怎麼查詢呢？由這個Query可以查詢座標系統及單位對照表

SELECT spatial_reference_id
   , well_known_text
   , unit_of_measure
   , unit_conversion_factor
FROM sys.spatial_reference_systems

↓ 查詢結果

面積

↓ 計算面積方式

SELECT @p.STArea() AS Area

↓ 舉一個例子，這邊用的是二度分帶座標，不特別寫座標系統。

Declare @p geometry;
SET @p = geometry::STGeomFromText('POLYGON ((281255.97250000015 2765700.5133999996, 281238 2765729.0083000008, 281240.62490000017 2765743.0083000008, 281242.9375 2765756.0083000008, 281232.34379999992 2765772.2583000008, 281215.375 2765798.7583000008, 281212.75930000003 2765797.2094, 281177.17169999983 2765776.1368000004, 281192.25289999973 2765734.4545000009, 281200.25260000024 2765719.6403, 281199.63779999968 2765719.2844999991, 281200.84049999993 2765717.6061000004, 281202.91610000003 2765715.0768999998, 281205.1529000001 2765712.6888999995, 281207.54090000037 2765710.4520999994, 281210.07010000013 2765708.3764999993, 281212.7297 2765706.4706999995, 281215.50810000021 2765704.7429000009, 281218.3936999999 2765703.2005000003, 281221.3739 2765701.8500999995, 281224.43609999958 2765700.6976999994, 281227.56730000023 2765699.7479, 281230.75370000023 2765699.0048999991, 281233.9819 2765698.4718999993, 281237.23790000007 2765698.1513, 281240.50810000021 2765698.0440999996, 281243.77830000035 2765698.1513, 281247.03450000007 2765698.4718999993, 281250.26269999985 2765699.0048999991, 281253.44909999985 2765699.7479, 281255.97250000015 2765700.5133999996))', 0)

SELECT @p.STArea() AS Area

↓ 結果

交集

Intersect(交集)，也就是面與面重疊的範圍。

↓　舉個例子

-- 面
DECLARE @a1 geometry;  
DECLARE @a2 geometry;  
SET @a1 = geometry::STGeomFromText('POLYGON((282402 2765483, 317348 2761734, 320177 2781022, 289425 2780566, 282402 2765483))', 0);  
SET @a2 = geometry::STGeomFromText('POLYGON ((283892 2772902, 304382 2772199, 292846 2782791, 283892 2772902))', 0);  
SELECT @a1
SELECT @a2

↓　這兩個面的空間資訊

在SQL Server SSMS管理工具中，如進行空間查詢，查詢結果會多一欄可以查看空間查詢結果

• Intersection 面交集的範圍

SELECT @a1.STIntersection(@a2) AS IntersectionResult

• Intersects 面是否交集

SELECT @a1.STIntersects(@a2) AS IsIntersect

↓ 查詢結果

↓ 空間查詢結果: 交疊的面

重疊

重疊(Within)與交集類似，但定義上卻更嚴謹，兩個比對的個體須完全重疊。

SELECT @a1.STWithin(@a2) AS IsWithin

↓　舉個例子

DECLARE @a1 geometry;  
DECLARE @a2 geometry;  
SET @a1 = geometry::STGeomFromText('POLYGON ((283892 2772902, 304382 2772199, 292846 2782791, 283892 2772902))', 0);  
SET @a2 = geometry::STGeomFromText('POLYGON ((283892 2772902, 304382 2772199, 292846 2782791, 283892 2772902))', 0);  
SELECT @a1.STWithin(@a2) AS IsWithin

↓ 查詢結果

包含

包含(Contains)則是可以判斷前者有沒有包含後者。

SELECT @a1.STContains(@a2) AS IsContain

↓　舉個例子

DECLARE @a geometry;  
DECLARE @p geometry; 
DECLARE @l geometry;   

SET @a = geometry::STGeomFromText('POLYGON ((283892 2772902, 304382 2772199, 292846 2782791, 283892 2772902))', 0);  
SET @p = geometry::STGeomFromText('POINT (294000 2776200)', 0);  
SET @l = geometry::STGeomFromText('LINESTRING (294000 2776200, 298000 2773400)', 0); 

SELECT @a.STContains(@p) AS IsContain 
SELECT @a.STContains(@l) AS IsContain 

↓ 查詢結果

可以看到Contains可以查詢面資料裡面有沒有包含其他點、線、面。

長度

長度(length)可以計算線段長度以及面的周長。

↓　舉個例子

DECLARE @p geometry; 
DECLARE @l geometry; 
SET @p = geometry::STGeomFromText('POLYGON ((283892 2772902, 304382 2772199, 292846 2782791, 283892 2772902))', 0);  
SET @l = geometry::STGeomFromText('LINESTRING (283892 2772902, 304382 2772199, 292846 2782791)', 0);  
SELECT  @p.STLength() AS [length]
SELECT  @l.STLength() AS [length]

↓ 查詢結果

距離

距離(Distance)計算物件(點、線、面)到另一個物件(點、線、面)之間的最短距離。

↓　舉個例子

DECLARE @a geometry; 
DECLARE @l geometry; 
DECLARE @p1 geometry; 
DECLARE @p2 geometry; 
SET @a = geometry::STGeomFromText('POLYGON ((283892 2772902, 304382 2772199, 292846 2782791, 283892 2772902))', 0);  
SET @l = geometry::STGeomFromText('LINESTRING (283892 2772902, 304382 2772199, 292846 2782791)', 0);  
SET @p1 = geometry::STGeomFromText('POINT (283892 2772902)', 0);  
SET @p2 = geometry::STGeomFromText('POINT (383892 2772902)', 0);  

SELECT  @l.STDistance(@a) AS Distance
SELECT  @l.STDistance(@p1) AS Distance

SELECT  @a.STDistance(@p1) AS Distance
SELECT  @a.STDistance(@p2) AS Distance

SELECT @p2.STDistance(@l) AS Distance
SELECT @p2.STDistance(@a) AS Distance
SELECT @p2.STDistance(@p1) AS Distance

↓ 查詢結果

可以看到不管是點、線、面，彼此之間都可以計算距離，如果彼此有交疊(Intersect)或者包含(Contains)，距離則為0。

常見錯誤

• 頭尾不相同(面資料)

面資料結構需要起始座標與終點座標相同，否則就會報錯。

-- 面資料
DECLARE @a1 geometry;  
DECLARE @a2 geometry;  

--  Error: 起始座標與終點座標不相同
SET @a1 = geometry::STGeomFromText('POLYGON((282402 2765483, 317348 2761734, 320177 2781022, 289425 2780566))', 0);  

--  正常
SET @a2 = geometry::STGeomFromText('POLYGON ((283892 2772902, 304382 2772199, 292846 2782791, 283892 2772902))', 0);  
SELECT @a1
SELECT @a2

• 方向錯誤

如果為geography格式資料，環的方向需要逆時針的方向，如果為順時針，會找到環外面的全部範圍，造成查詢錯誤。

↓　舉個例子

DECLARE @a1 geography;  
DECLARE @a2 geography;  

-- 順時針 
SET @a1 = geography::STGeomFromText('POLYGON((120.68683027895389 24.246634330503618,120.76373461443319 24.19090153130943,120.67653059116645 24.167724543536114,120.68683027895389 24.246634330503618))', 4326);  

-- 逆時針
SET @a2 = geography::STGeomFromText('POLYGON((120.68683027895389 24.246634330503618,120.67653059116645 24.167724543536114,120.76373461443319 24.19090153130943,120.68683027895389 24.246634330503618))', 4326);  
SELECT @a1
SELECT @a2

↓ 查詢結果

可以看出順時針查詢結果為外環範圍。

解決方式

IF @p.EnvelopeAngle() = 180 
BEGIN 
SET @p = @p.ReorientObject() 
END 

• 可以利用EnvelopeAngle查詢角度，如果為外環範圍必為180度。詳見微軟說明
• 利用ReorientObject可以將環的方向轉向。

今天簡單講解SQL Server空間查詢的方式，地理空間查詢的計算方式微軟都幫我們寫好了，只要呼叫是不是很簡單呀？介紹完GIS的SQL好朋友後，明天要繼續回到Javascript與WebGIS的懷抱啦！