PostGIS中常用查詢sql有哪些

# PostGIS中常用查詢SQL有哪些

PostGIS作為PostgreSQL的空間數據庫擴展，為地理空間數據存儲和分析提供了強大支持。以下是PostGIS中常用的查詢SQL分類整理，涵蓋基礎查詢、空間關系判斷、幾何處理等核心功能。

---

## 一、基礎空間查詢

### 1. 空間表結構查詢
```sql
-- 查詢包含空間數據的表
SELECT f_table_name, f_geometry_column 
FROM geometry_columns;

-- 查看空間參考系統(SRID)
SELECT ST_SRID(geom) FROM table_name LIMIT 1;

2. 幾何屬性查詢

-- 獲取幾何類型（點/線/面等）
SELECT ST_GeometryType(geom) FROM table_name;

-- 計算幾何面積（適用于面）
SELECT ST_Area(geom) FROM table_name;

-- 計算幾何長度（適用于線）
SELECT ST_Length(geom) FROM line_table;

二、空間關系判斷

1. 包含關系

-- 查詢包含某點的多邊形（如行政區劃包含點位）
SELECT * FROM polygons 
WHERE ST_Contains(geom, ST_SetSRID(ST_MakePoint(lon, lat), 4326));

2. 相交判斷

-- 查詢與某區域相交的所有道路
SELECT * FROM roads 
WHERE ST_Intersects(geom, ST_Buffer(ST_MakePoint(lon, lat), 0.01));

3. 距離查詢

-- 查詢500米范圍內的POI點（需地理坐標系）
SELECT * FROM poi_points 
WHERE ST_DWithin(
  geom, 
  ST_SetSRID(ST_MakePoint(lon, lat), 4326),
  500
);

三、幾何處理與分析

1. 緩沖區分析

-- 創建100米緩沖區
SELECT ST_Buffer(geom, 100) FROM line_features;

-- 地理坐標系下的緩沖區（單位：米）
SELECT ST_Buffer(geom::geography, 100)::geometry FROM points;

2. 幾何簡化

-- 簡化幾何（道格拉斯-普克算法）
SELECT ST_Simplify(geom, 0.001) FROM complex_polygons;

3. 幾何合并/切割

-- 合并多個幾何（UNION）
SELECT ST_Union(geom) FROM multi_polygons;

-- 幾何差異計算
SELECT ST_Difference(poly1, poly2) FROM polygons;

四、空間連接查詢

1. 屬性+空間連接

-- 統計每個區域內的點數量
SELECT a.region_id, COUNT(b.*) 
FROM regions a LEFT JOIN points b 
ON ST_Contains(a.geom, b.geom)
GROUP BY a.region_id;

2. 最近鄰查詢

-- 查詢每個點最近的5個設施點
SELECT a.id, 
(SELECT array_agg(b.id ORDER BY ST_Distance(a.geom, b.geom) LIMIT 5)
FROM points a, facilities b;

五、空間索引優化

1. 創建空間索引

CREATE INDEX idx_table_geom ON table_name USING GIST(geom);

2. 強制使用索引查詢

-- 使用索引加速距離查詢
SELECT * FROM points 
WHERE geom && ST_Expand(ST_MakePoint(lon, lat), 0.01)
AND ST_Distance(geom, ST_MakePoint(lon, lat)) < 100;

六、坐標系轉換

1. 動態坐標轉換

-- WGS84轉Web墨卡托（3857）
SELECT ST_Transform(geom, 3857) FROM table_name;

-- 地理坐標與幾何坐標互轉
SELECT geom::geography FROM table_name;

七、實用空間函數

1. 生成網格

-- 創建1km×1km網格
SELECT ST_MakeEnvelope(
  x, y, x+1000, y+1000, 26918) 
FROM generate_series(0, 50000, 1000) x,
     generate_series(0, 30000, 1000) y;

2. 路徑分析

-- 計算兩點間最短路徑（需pgRouting擴展）
SELECT * FROM pgr_dijkstra(
  'SELECT id, source, target, cost FROM edges',
  source_node, target_node
);

通過掌握這些常用SQL，可以高效處理90%以上的空間數據分析場景。實際應用中建議結合空間索引和查詢優化技巧提升性能。 “`

注：本文示例基于PostGIS 3.x版本，部分函數在舊版本中可能有所不同。所有坐標參數應根據實際數據調整SRID值。