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值。