SQL Server 空間查詢操作指南

前言

在處理地理資訊系統(GIS)時，SQL Server 提供了一系列強大的空間查詢工具。本文將詳細介紹這些工具的使用方法，幫助您更有效地處理空間資料。

一、基本空間關係判斷

1. 包含關係

1
2
3
4
5
6
7
8
9
-- A.STContains(B)：判斷 A 是否包含 B
-- 最常用於判斷點位是否在某個範圍內
SELECT * FROM Points 
WHERE @polygon.STContains(point_geometry) = 1

-- A.STWithin(B)：判斷 A 是否在 B 內
-- 與 STContains 相反，從點位角度判斷
SELECT * FROM Points 
WHERE point_geometry.STWithin(@polygon) = 1

2. 相交關係

1
2
3
4
5
6
7
8
-- A.STIntersects(B)：判斷 A 和 B 是否相交
-- 適合判斷兩個範圍是否有重疊
SELECT * FROM Zones
WHERE zone1_geometry.STIntersects(zone2_geometry) = 1

-- A.STTouches(B)：判斷 A 和 B 是否僅在邊界相交
SELECT * FROM Zones
WHERE zone1_geometry.STTouches(zone2_geometry) = 1

3. 其他空間關係

1
2
3
4
5
6
7
8
9
10
11
-- A.STEquals(B)：判斷 A 和 B 是否完全相同
SELECT * FROM Geometry_Table
WHERE geometry1.STEquals(geometry2) = 1

-- A.STOverlaps(B)：判斷 A 和 B 是否部分重疊
SELECT * FROM Zones
WHERE zone1_geometry.STOverlaps(zone2_geometry) = 1

-- A.STDisjoint(B)：判斷 A 和 B 是否完全不相交
SELECT * FROM Zones
WHERE zone1_geometry.STDisjoint(zone2_geometry) = 1

二、距離相關方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 計算兩個空間物件間的距離
.STDistance(geometry)

-- 建立指定距離的緩衝區
.STBuffer(distance)

-- 實際應用範例：找出距離特定點位 500 公尺內的所有點
SELECT *
FROM Points
WHERE point_geometry.STDistance(@centerPoint) <= 500

-- 建立緩衝區後進行查詢
SELECT *
FROM Points
WHERE @polygon.STBuffer(100).STContains(point_geometry) = 1

三、幾何運算方法

1. 差集運算 (Difference)

1
2
-- A.STDifference(B)：從 A 中減去與 B 重疊的部分
SELECT @規劃範圍.STDifference(@禁制區) as 實際可用範圍

2. 聯集運算 (Union)

1
2
3
4
5
6
-- A.STUnion(B)：合併 A 和 B 的範圍
SELECT @範圍1.STUnion(@範圍2) as 合併範圍

-- 多筆資料的聯集
SELECT geometry::UnionAggregate(geometry欄位) as 總範圍
FROM 範圍表

3. 交集運算 (Intersection)

1
2
-- A.STIntersection(B)：取得 A 和 B 重疊的部分
SELECT @範圍1.STIntersection(@範圍2) as 重疊區域

4. 對稱差集 (Symmetric Difference)

1
2
-- A.STSymDifference(B)：取得兩個範圍不重疊的部分
SELECT @範圍1.STSymDifference(@範圍2) as 不重疊區域

四、資料格式轉換

1. 文字轉空間資料

1
2
3
4
5
-- 從WKT文字轉geometry
geometry::STGeomFromText('POINT(X Y)', SRID)

-- 專門轉換點位的方法
geometry::STPointFromText('POINT(X Y)', SRID)

2. 空間資料轉文字

1
2
3
4
5
-- 轉為WKT格式文字
.STAsText()

-- 轉為字串
.ToString()

五、實際應用範例

1. 基本範圍查詢

1
2
3
4
5
6
7
8
9
10
-- 範例一：找出特定區域內的所有點位
SELECT p.*
FROM Points p
WHERE @特定區域.STContains(p.geometry) = 1

-- 範例二：找出同時在A區域和B區域的點位
SELECT p.*
FROM Points p
WHERE @區域A.STContains(p.geometry) = 1
AND @區域B.STContains(p.geometry) = 1

2. 複合條件查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 範例一：找出在特定區域內且符合特定條件的點位
SELECT p.*
FROM Points p
JOIN Zones z ON z.geometry.STContains(p.geometry) = 1
WHERE 
    z.ZoneName = '特定區域'
    AND p.Type = '住宅'
    AND p.Status = '使用中'

-- 範例二：找出在多個區域內的點位並標示區域名稱
-- geometry 為不可比較型別不可放入 GROUP BY；先聚合，再 JOIN 回原表取完整欄位
SELECT 
    p.*,
    agg.所在區域
FROM Points p
JOIN (
    SELECT p2.Id,
           STRING_AGG(z.ZoneName, ',') as 所在區域,
           COUNT(*) as 區域數
    FROM Points p2
    JOIN Zones z ON z.geometry.STContains(p2.geometry) = 1
    GROUP BY p2.Id
    HAVING COUNT(*) > 1  -- 只保留位於多個區域的點位
) agg ON agg.Id = p.Id

3. 距離相關查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 範例一：找出距離特定點位500單位內的所有點
-- ⚠ SRID 0 的 geometry 型別：STDistance 回傳座標系統原生單位，不是公尺
-- 若需要公尺距離，應改用 geography 型別（EPSG:4326）或確認平面座標系單位
-- ⚠ WHERE p.geometry.STDistance(@中心點) <= 500 會繞過空間索引，導致全表掃描
-- 正確寫法：先用 STBuffer + STIntersects 做索引過濾，再用 STDistance 精確篩選
DECLARE @中心點 geometry = geometry::STPointFromText('POINT(330000 2730000)', 0)
DECLARE @緩衝區 geometry = @中心點.STBuffer(500)

SELECT 
    p.*,
    p.geometry.STDistance(@中心點) as 距離
FROM Points p
WHERE p.geometry.STIntersects(@緩衝區) = 1  -- 先走空間索引（bounding box 過濾）
  AND p.geometry.STDistance(@中心點) <= 500  -- 再精確篩選
ORDER BY 距離

-- 範例二：計算所有點位兩兩距離
-- ⚠ CROSS JOIN 為 O(n²)，資料量大時效能急遽惡化；僅適合小量資料
SELECT 
    p1.Id as 起點ID,
    p2.Id as 終點ID,
    p1.geometry.STDistance(p2.geometry) as 距離
FROM Points p1
CROSS JOIN Points p2
WHERE p1.Id < p2.Id  -- 避免重複組合
ORDER BY 距離

4. 範圍運算案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 範例一：計算多個區域的聯集範圍
-- ⚠ geometry::UnionAggregate() 需要 SQL Server 2012 或以上版本
WITH 合併範圍 AS (
    SELECT geometry::UnionAggregate(geometry) as 總範圍
    FROM Zones
    WHERE ZoneType = '服務區'
)
SELECT 
    總範圍,
    總範圍.STArea() as 總面積
FROM 合併範圍

-- 範例二：找出兩個區域的重疊部分
SELECT 
    @區域A.STIntersection(@區域B) as 重疊區域,
    @區域A.STIntersection(@區域B).STArea() as 重疊面積

5. 複雜範圍處理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 範例一：從規劃範圍中扣除多個禁制區
WITH 禁制區 AS (
    SELECT geometry::UnionAggregate(geometry) as 總禁制區
    FROM Zones
    WHERE ZoneType = '禁制區'
)
SELECT 
    @規劃範圍.STDifference(總禁制區) as 可用範圍,
    @規劃範圍.STDifference(總禁制區).STArea() as 可用面積
FROM 禁制區

-- 範例二：建立緩衝區並找出在緩衝區內的點位
-- ⚠ CROSS APPLY 內的 UnionAggregate 會對 ServicePoints 全表聚合，且對每列 Points 重複執行
-- 資料量大時效能極差。正確作法：先在外部一次算好聯集緩衝區，再做 STContains 過濾
-- 以下僅作示範，生產環境請先 DECLARE @緩衝區聯集 = (SELECT geometry::UnionAggregate(...) FROM ServicePoints WHERE ...)
SELECT p.*
FROM Points p
CROSS APPLY (
    SELECT geometry::UnionAggregate(geometry.STBuffer(100)) as 緩衝區
    FROM ServicePoints
    WHERE Type = '服務站'
) buffer
WHERE buffer.緩衝區.STContains(p.geometry) = 1

6. 統計分析案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 範例一：計算各區域內的點位數量
-- geometry 為不可比較型別，不可放入 GROUP BY；先聚合非空間欄位，再 JOIN 回原表取 geometry
SELECT 
    z.ZoneName,
    agg.點位數量,
    z.geometry.STArea() as 區域面積,
    agg.點位數量 * 1.0 / z.geometry.STArea() as 點位密度
FROM Zones z
JOIN (
    SELECT z2.ZoneId, COUNT(*) as 點位數量
    FROM Zones z2
    JOIN Points p ON z2.geometry.STContains(p.geometry) = 1
    GROUP BY z2.ZoneId
) agg ON agg.ZoneId = z.ZoneId

-- 範例二：計算點位的分佈情況
-- ⚠ 自我 JOIN 為 O(n²)；且 WHERE p1.geometry.STDistance(p2.geometry) <= 1000 繞過空間索引
-- 如需處理大量資料，應改為：以 STBuffer(1000) 預先縮小候選集，再計算距離統計
SELECT 
    p1.Id as 中心點ID,
    COUNT(*) as 周圍點位數量,
    AVG(p1.geometry.STDistance(p2.geometry)) as 平均距離,
    MIN(p1.geometry.STDistance(p2.geometry)) as 最短距離,
    MAX(p1.geometry.STDistance(p2.geometry)) as 最長距離
FROM Points p1
JOIN Points p2 ON p1.Id != p2.Id
WHERE p1.geometry.STDistance(p2.geometry) <= 1000
GROUP BY p1.Id

7. 資料驗證案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 範例一：檢查點位是否在合法範圍內
SELECT p.*
FROM Points p
LEFT JOIN LegalZones z ON z.geometry.STContains(p.geometry) = 1
WHERE z.Id IS NULL  -- 找出不在任何合法區域內的點位

-- 範例二：檢查兩個點位是否過於接近
SELECT 
    p1.Id as 點位1,
    p2.Id as 點位2,
    p1.geometry.STDistance(p2.geometry) as 距離
FROM Points p1
JOIN Points p2 ON p1.Id < p2.Id
WHERE p1.geometry.STDistance(p2.geometry) < 10  -- 距離小於10公尺

8. 空間索引最佳化案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 範例一：使用空間索引的查詢寫法
SELECT p.*
FROM Points p WITH(INDEX(空間索引名稱))
WHERE 
    p.geometry.STIntersects(@查詢範圍) = 1
    AND p.Type = '特定類型'

-- 範例二：建立空間索引
CREATE SPATIAL INDEX [空間索引名稱]
ON Points(geometry)
USING GEOMETRY_GRID
WITH (
    BOUNDING_BOX = (xmin=320000, ymin=2720000, xmax=340000, ymax=2750000),
    GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM),
    CELLS_PER_OBJECT = 16,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

六、效能最佳化建議

索引寫法決定能不能走索引。 STIntersects()、STContains()、STWithin() 能觸發空間索引；STDistance() 直接放 WHERE 子句則不行——必須先用 STBuffer 建緩衝區再呼叫 STIntersects，做完 bounding-box 過濾後才在外層用 STDistance 精確篩選。

非空間條件先過濾。 WHERE Type = '住宅' AND point_geometry.STIntersects(@zone) = 1 比反過來快得多；SQL Server 會先走一般 B-tree 索引縮小候選集，再跑空間索引。

自我 JOIN 的代價是 O(n²)。 10,000 筆點位的全配對距離是 1 億次計算。若只需「每個點找 1000 單位內的鄰居」，應先用 STBuffer 限制半徑，不要無條件 CROSS JOIN 再用 STDistance 過濾。

UnionAggregate 在 CROSS APPLY 裡代價驚人。 對每一列外表都重新聚合一次全表——應在 CTE 或子查詢中預先算好一次，再拿結果做 STContains。

SRID 不一致會導致靜默錯誤，不是執行期例外。 STDistance、STIntersects 在 SRID 不符時回傳 NULL 或 0，查詢不會報錯但結果完全錯誤；建立資料時就應確認 SRID，不要等出問題再查。

結語

空間查詢裡最常踩的坑集中在三個地方：索引寫法（STDistance 放 WHERE 就繞過索引）、型別混用（geometry 的距離單位不是公尺）、以及 O(n²) 的自我 JOIN。搞清楚這三點，其他的查詢組合方式都能照 SQL Server 的一般調校邏輯處理。

參考資料