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
-- 範例一：找出在特定區域內且符合特定條件的點位
SELECT p.*
FROM Points p
JOIN Zones z ON z.geometry.STContains(p.geometry) = 1
WHERE 
    z.ZoneName = '特定區域'
    AND p.Type = '住宅'
    AND p.Status = '使用中'

-- 範例二：找出在多個區域內的點位並標示區域名稱
SELECT 
    p.*,
    STRING_AGG(z.ZoneName, ',') as 所在區域
FROM Points p
JOIN Zones z ON z.geometry.STContains(p.geometry) = 1
GROUP BY p.Id, p.geometry, p.Name  -- 需要列出所有要顯示的欄位
HAVING COUNT(*) > 1  -- 只顯示位於多個區域的點位

3. 距離相關查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 範例一：找出距離特定點位500公尺內的所有點
DECLARE @中心點 geometry = geometry::STPointFromText('POINT(330000 2730000)', 0)

SELECT 
    p.*,
    p.geometry.STDistance(@中心點) as 距離
FROM Points p
WHERE p.geometry.STDistance(@中心點) <= 500
ORDER BY 距離

-- 範例二：找出兩個點位間最短距離的路徑
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
-- 範例一：計算多個區域的聯集範圍
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
-- 範例一：從規劃範圍中扣除多個禁制區
WITH 禁制區 AS (
    SELECT geometry::UnionAggregate(geometry) as 總禁制區
    FROM Zones
    WHERE ZoneType = '禁制區'
)
SELECT 
    @規劃範圍.STDifference(總禁制區) as 可用範圍,
    @規劃範圍.STDifference(總禁制區).STArea() as 可用面積
FROM 禁制區

-- 範例二：建立緩衝區並找出在緩衝區內的點位
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
-- 範例一：計算各區域內的點位數量
SELECT 
    z.ZoneName,
    COUNT(*) as 點位數量,
    z.geometry.STArea() as 區域面積,
    COUNT(*) * 1.0 / z.geometry.STArea() as 點位密度
FROM Zones z
JOIN Points p ON z.geometry.STContains(p.geometry) = 1
GROUP BY z.ZoneId, z.ZoneName, z.geometry

-- 範例二：計算點位的分佈情況
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]

六、效能最佳化建議

  1. 建立適當的空間索引
  2. 優先過濾非空間條件
  3. 避免在大量資料上進行複雜的空間運算
  4. 適當使用 CTE 或子查詢來優化查詢結構
  5. 確保所有幾何物件使用相同的空間參考系統(SRID)

結語

空間查詢方法提供了強大的工具來處理地理資訊數據。透過適當的使用這些方法，我們可以有效地進行空間分析、範圍規劃等作業。在實務應用中，記得考慮效能影響，並選擇最適合的方法來達成目標。

參考資料