第10章-空间分析函数
第10章：空间分析函数
10.1 空间分析概述
空间分析函数是 PostGIS 最强大的功能之一，用于执行各种几何运算和空间分析操作。
10.1.1 函数分类
┌─────────────────────────────────────────────────────────────┐
│ 空间分析函数分类 │
├─────────────────────────────────────────────────────────────┤
│ 几何叠加操作 │
│ ├── ST_Intersection - 求交 │
│ ├── ST_Union - 求并 │
│ ├── ST_Difference - 求差 │
│ ├── ST_SymDifference - 对称差 │
│ └── ST_Split - 分割 │
│ │
│ 几何生成操作 │
│ ├── ST_Buffer - 缓冲区 │
│ ├── ST_ConvexHull - 凸包 │
│ ├── ST_ConcaveHull - 凹包 │
│ └── ST_OffsetCurve - 偏移曲线 │
│ │
│ 几何简化与修复 │
│ ├── ST_Simplify - 简化 │
│ ├── ST_SimplifyPreserveTopology - 保持拓扑的简化 │
│ ├── ST_MakeValid - 修复无效几何 │
│ └── ST_Snap - 捕捉 │
│ │
│ 几何变换 │
│ ├── ST_Translate - 平移 │
│ ├── ST_Scale - 缩放 │
│ ├── ST_Rotate - 旋转 │
│ └── ST_Affine - 仿射变换 │
│ │
└─────────────────────────────────────────────────────────────┘
10.2 几何叠加操作
10.2.1 ST_Intersection（求交）
-- 两个多边形的交集
SELECT ST_AsText(ST_Intersection(
ST_MakeEnvelope(0, 0, 2, 2),
ST_MakeEnvelope(1, 1, 3, 3)
));
-- POLYGON((1 1,1 2,2 2,2 1,1 1))
-- 线与多边形的交集
SELECT ST_AsText(ST_Intersection(
ST_GeomFromText('LINESTRING(0 0.5, 3 0.5)'),
ST_MakeEnvelope(1, 0, 2, 1)
));
-- LINESTRING(1 0.5,2 0.5)
-- 计算道路在某区域内的部分
SELECT r.name, ST_AsText(ST_Intersection(r.geom, d.geom)) AS road_in_district
FROM roads r, districts d
WHERE d.name = '东城区'
AND ST_Intersects(r.geom, d.geom);
-- 计算重叠面积
SELECT
a.name AS area_a,
b.name AS area_b,
ST_Area(ST_Intersection(a.geom, b.geom)::geography) AS overlap_area_m2
FROM districts a, districts b
WHERE a.id < b.id
AND ST_Intersects(a.geom, b.geom);
10.2.2 ST_Union（求并）
-- 两个几何的并集
SELECT ST_AsText(ST_Union(
ST_MakeEnvelope(0, 0, 2, 2),
ST_MakeEnvelope(1, 1, 3, 3)
));
-- POLYGON((0 0,0 2,1 2,1 3,3 3,3 1,2 1,2 0,0 0))
-- 聚合合并多个几何
SELECT ST_Union(geom) AS merged_geom
FROM districts
WHERE province = '北京';
-- 合并某类别的所有 POI 缓冲区
SELECT category, ST_Union(ST_Buffer(geom::geography, 500)::geometry) AS service_area
FROM poi
GROUP BY category;
-- ST_UnaryUnion：合并单个几何集合（去除重叠）
SELECT ST_UnaryUnion(ST_Collect(geom)) FROM overlapping_polygons;
-- 使用 ST_MemUnion 处理大量几何（内存效率更高）
SELECT ST_MemUnion(geom) FROM large_polygon_set;
10.2.3 ST_Difference（求差）
-- 从 A 中减去 B
SELECT ST_AsText(ST_Difference(
ST_MakeEnvelope(0, 0, 3, 3),
ST_MakeEnvelope(1, 1, 2, 2)
));
-- POLYGON((0 0,0 3,3 3,3 0,0 0),(1 1,2 1,2 2,1 2,1 1))
-- 从区域中挖去湖泊
UPDATE land_areas SET geom = ST_Difference(geom, (
SELECT ST_Union(geom) FROM lakes WHERE ST_Intersects(lakes.geom, land_areas.geom)
));
-- 计算 A 独有的部分
SELECT
a.name,
ST_Area(ST_Difference(a.geom, COALESCE(ST_Union(b.geom), 'POLYGON EMPTY'::geometry))::geography) AS unique_area
FROM districts a
LEFT JOIN other_areas b ON ST_Intersects(a.geom, b.geom)
GROUP BY a.id, a.name, a.geom;
10.2.4 ST_SymDifference（对称差）
-- 对称差：(A - B) ∪ (B - A)
SELECT ST_AsText(ST_SymDifference(
ST_MakeEnvelope(0, 0, 2, 2),
ST_MakeEnvelope(1, 1, 3, 3)
));
-- 返回两个不重叠的部分
-- 找出两个版本数据的差异
SELECT ST_SymDifference(old_version.geom, new_version.geom) AS changed_area
FROM old_version, new_version
WHERE old_version.id = new_version.id;
10.2.5 ST_Split（分割）
-- 用线分割多边形
SELECT (ST_Dump(ST_Split(
ST_MakeEnvelope(0, 0, 2, 2),
ST_GeomFromText('LINESTRING(0 1, 2 1)')
))).geom AS split_result;
-- 用点分割线
SELECT (ST_Dump(ST_Split(
ST_GeomFromText('LINESTRING(0 0, 2 2)'),
ST_GeomFromText('POINT(1 1)')
))).geom AS split_result;
-- 用道路分割区域
SELECT d.name, (ST_Dump(ST_Split(d.geom, r.geom))).geom AS sub_area
FROM districts d, roads r
WHERE ST_Intersects(d.geom, r.geom)
AND r.name = '长安街';
10.3 缓冲区分析
10.3.1 ST_Buffer
-- 基本缓冲区
SELECT ST_Buffer(ST_MakePoint(0, 0), 1);
-- Geography 类型缓冲区（米为单位）
SELECT ST_Buffer(
ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)::geography,
1000 -- 1000米
)::geometry;
-- 缓冲区样式
-- quad_segs: 四分之一圆的段数
SELECT ST_Buffer(ST_MakePoint(0, 0), 1, 'quad_segs=2'); -- 八边形
SELECT ST_Buffer(ST_MakePoint(0, 0), 1, 'quad_segs=8'); -- 32边形（更圆）
-- 端点样式（线缓冲区）
SELECT ST_Buffer(ST_GeomFromText('LINESTRING(0 0, 1 0)'), 0.2, 'endcap=round'); -- 圆头
SELECT ST_Buffer(ST_GeomFromText('LINESTRING(0 0, 1 0)'), 0.2, 'endcap=flat'); -- 平头
SELECT ST_Buffer(ST_GeomFromText('LINESTRING(0 0, 1 0)'), 0.2, 'endcap=square'); -- 方头
-- 连接样式
SELECT ST_Buffer(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)'), 0.2, 'join=round'); -- 圆角
SELECT ST_Buffer(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)'), 0.2, 'join=mitre'); -- 尖角
SELECT ST_Buffer(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)'), 0.2, 'join=bevel'); -- 斜角
-- 单侧缓冲区
SELECT ST_Buffer(ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1)'), 0.2, 'side=left');
SELECT ST_Buffer(ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1)'), 0.2, 'side=right');
-- 负缓冲区（内缩）
SELECT ST_Buffer(ST_MakeEnvelope(0, 0, 4, 4), -0.5);
-- 服务区分析
SELECT poi.name, ST_Buffer(poi.geom::geography, 1000)::geometry AS service_area
FROM poi
WHERE category = '医院';
-- 多环缓冲区
WITH buffers AS (
SELECT generate_series(500, 2000, 500) AS radius
)
SELECT radius, ST_Buffer(ST_MakePoint(116.4, 39.9)::geography, radius)::geometry AS buffer
FROM buffers;
10.3.2 ST_OffsetCurve（偏移曲线）
-- 生成平行线（只对线有效）
SELECT ST_OffsetCurve(ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1)'), 0.1); -- 左偏移
SELECT ST_OffsetCurve(ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1)'), -0.1); -- 右偏移
-- 道路中心线生成边线
SELECT
ST_OffsetCurve(geom, road_width / 2) AS left_edge,
ST_OffsetCurve(geom, -road_width / 2) AS right_edge
FROM roads;
10.4 几何简化
10.4.1 ST_Simplify（道格拉斯-普克简化）
-- 基本简化
SELECT ST_NPoints(geom), ST_NPoints(ST_Simplify(geom, 0.001)) AS simplified_points
FROM complex_polygons;
-- 不同容差的效果
SELECT
ST_NPoints(geom) AS original,
ST_NPoints(ST_Simplify(geom, 0.0001)) AS simplified_0001,
ST_NPoints(ST_Simplify(geom, 0.001)) AS simplified_001,
ST_NPoints(ST_Simplify(geom, 0.01)) AS simplified_01
FROM districts;
-- 按地图缩放级别简化
SELECT
CASE zoom_level
WHEN 5 THEN ST_Simplify(geom, 0.1)
WHEN 10 THEN ST_Simplify(geom, 0.01)
WHEN 15 THEN ST_Simplify(geom, 0.001)
ELSE geom
END AS simplified_geom
FROM districts;
10.4.2 ST_SimplifyPreserveTopology
-- 保持拓扑关系的简化
SELECT ST_SimplifyPreserveTopology(geom, 0.001) FROM complex_polygons;
-- 与 ST_Simplify 的区别
-- ST_Simplify 可能产生无效几何（自相交）
-- ST_SimplifyPreserveTopology 保证结果有效
SELECT
ST_IsValid(ST_Simplify(geom, 0.01)) AS simplify_valid,
ST_IsValid(ST_SimplifyPreserveTopology(geom, 0.01)) AS preserve_valid
FROM complex_polygons;
10.4.3 ST_SimplifyVW (Visvalingam-Whyatt)
-- Visvalingam-Whyatt 简化算法
-- 基于面积的简化，视觉效果通常更好
SELECT ST_SimplifyVW(geom, 0.001) FROM complex_polygons;
-- 比较不同算法
SELECT
'Douglas-Peucker' AS algorithm, ST_AsText(ST_Simplify(geom, 0.01))
FROM test_line
UNION ALL
SELECT
'Visvalingam-Whyatt', ST_AsText(ST_SimplifyVW(geom, 0.01))
FROM test_line;
10.5 几何修复
10.5.1 ST_MakeValid
-- 修复无效几何
SELECT ST_AsText(ST_MakeValid(
ST_GeomFromText('POLYGON((0 0, 2 0, 0 2, 2 2, 0 0))') -- 自相交的多边形
));
-- 批量修复
UPDATE spatial_data
SET geom = ST_MakeValid(geom)
WHERE NOT ST_IsValid(geom);
-- 查找并修复无效几何
SELECT id, ST_IsValidReason(geom) AS reason
FROM spatial_data
WHERE NOT ST_IsValid(geom);
10.5.2 ST_Snap（捕捉）
-- 将几何 A 捕捉到几何 B（在容差范围内）
SELECT ST_Snap(
ST_GeomFromText('LINESTRING(0 0, 1.01 1.01)'),
ST_GeomFromText('POINT(1 1)'),
0.1 -- 容差
);
-- 修复拓扑间隙
UPDATE polygons p1
SET geom = ST_Snap(p1.geom, p2.geom, 0.0001)
FROM polygons p2
WHERE p1.id != p2.id
AND ST_DWithin(p1.geom, p2.geom, 0.001);
10.5.3 ST_SnapToGrid
-- 将坐标捕捉到网格
SELECT ST_AsText(ST_SnapToGrid(
ST_GeomFromText('POINT(1.123456 2.654321)'),
0.01 -- 网格大小
));
-- POINT(1.12 2.65)
-- 指定原点和不同轴的网格大小
SELECT ST_SnapToGrid(geom, 0, 0, 0.001, 0.001) FROM poi;
-- 减少精度以减小数据量
UPDATE spatial_data
SET geom = ST_SnapToGrid(geom, 0.000001);
10.6 几何变换
10.6.1 ST_Translate（平移）
-- 平移几何
SELECT ST_AsText(ST_Translate(
ST_GeomFromText('POINT(0 0)'),
1, 2 -- dx, dy
));
-- POINT(1 2)
-- 3D 平移
SELECT ST_Translate(geom, 100, 200, 50) FROM spatial_3d;
-- 批量平移
UPDATE spatial_data
SET geom = ST_Translate(geom, offset_x, offset_y);
10.6.2 ST_Scale（缩放）
-- 相对于原点缩放
SELECT ST_AsText(ST_Scale(
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
2, 2 -- x_factor, y_factor
));
-- 相对于指定点缩放
SELECT ST_Scale(
geom,
2, 2,
ST_Centroid(geom) -- 以质心为中心缩放
) FROM polygons;
-- 3D 缩放
SELECT ST_Scale(geom, 2, 2, 0.5) FROM spatial_3d;
10.6.3 ST_Rotate（旋转）
-- 相对于原点旋转（弧度）
SELECT ST_Rotate(
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
pi() / 4 -- 45度
);
-- 相对于指定点旋转
SELECT ST_Rotate(
geom,
pi() / 2, -- 90度
ST_Centroid(geom) -- 以质心为中心旋转
) FROM polygons;
-- 使用 ST_RotateX, ST_RotateY, ST_RotateZ 进行 3D 旋转
SELECT ST_RotateZ(geom, pi() / 4) FROM spatial_3d;
10.6.4 ST_Affine（仿射变换）
-- 通用仿射变换
-- ST_Affine(geom, a, b, c, d, e, f, g, h, i, xoff, yoff, zoff)
-- [a b c xoff] [x]
-- [d e f yoff] * [y]
-- [g h i zoff] [z]
-- [0 0 0 1] [1]
-- 2D 仿射变换
SELECT ST_Affine(
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
cos(pi()/4), -sin(pi()/4), -- a, b
sin(pi()/4), cos(pi()/4), -- d, e
10, 20 -- xoff, yoff
); -- 旋转45度并平移(10,20)
-- 镜像变换
SELECT ST_Affine(geom, -1, 0, 0, 1, 0, 0) FROM polygons; -- 沿Y轴镜像
SELECT ST_Affine(geom, 1, 0, 0, -1, 0, 0) FROM polygons; -- 沿X轴镜像
10.6.5 ST_TransScale
-- 平移和缩放的组合
SELECT ST_TransScale(
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
10, 20, -- 平移 (dx, dy)
2, 2 -- 缩放 (xfactor, yfactor)
);
10.7 高级分析
10.7.1 ST_ClusterDBSCAN
-- DBSCAN 聚类
SELECT
ST_ClusterDBSCAN(geom, eps := 0.01, minpoints := 3) OVER () AS cluster_id,
*
FROM poi;
-- 分析聚类结果
WITH clusters AS (
SELECT
ST_ClusterDBSCAN(geom, eps := 0.005, minpoints := 5) OVER () AS cluster_id,
geom
FROM poi
)
SELECT
cluster_id,
COUNT(*) AS point_count,
ST_ConvexHull(ST_Collect(geom)) AS cluster_boundary
FROM clusters
WHERE cluster_id IS NOT NULL
GROUP BY cluster_id;
10.7.2 ST_ClusterKMeans
-- K-Means 聚类
SELECT
ST_ClusterKMeans(geom, 5) OVER () AS cluster_id,
*
FROM poi;
-- 计算聚类中心
WITH clusters AS (
SELECT
ST_ClusterKMeans(geom, 5) OVER () AS cluster_id,
geom
FROM poi
)
SELECT
cluster_id,
ST_Centroid(ST_Collect(geom)) AS cluster_center,
COUNT(*) AS point_count
FROM clusters
GROUP BY cluster_id;
10.7.3 ST_Subdivide
-- 将大几何分割为小块（提高查询效率）
SELECT (ST_Dump(ST_Subdivide(geom, 256))).geom AS sub_geom
FROM large_polygons;
-- 创建分割后的表
CREATE TABLE subdivided_districts AS
SELECT id, name, (ST_Dump(ST_Subdivide(geom, 256))).geom AS geom
FROM districts;
CREATE INDEX idx_subdivided_geom ON subdivided_districts USING GIST (geom);
-- 使用分割后的表进行查询
SELECT DISTINCT d.name
FROM subdivided_districts d, poi p
WHERE ST_Intersects(d.geom, p.geom);
10.7.4 ST_VoronoiPolygons（泰森多边形）
-- 生成泰森多边形
SELECT (ST_Dump(ST_VoronoiPolygons(ST_Collect(geom)))).geom AS voronoi
FROM poi
WHERE category = '医院';
-- 带边界的泰森多边形
SELECT (ST_Dump(ST_VoronoiPolygons(
ST_Collect(geom),
0.0, -- 容差
(SELECT ST_Envelope(ST_Collect(geom)) FROM poi) -- 边界
))).geom AS voronoi
FROM poi;
-- 服务区划分
SELECT
p.name AS hospital,
ST_Intersection(v.voronoi, d.geom) AS service_area
FROM (
SELECT id, name, geom FROM poi WHERE category = '医院'
) p,
(
SELECT (ST_Dump(ST_VoronoiPolygons(ST_Collect(geom)))).geom AS voronoi
FROM poi WHERE category = '医院'
) v,
districts d
WHERE ST_Intersects(p.geom, v.voronoi);
10.8 本章小结
本章详细介绍了 PostGIS 的空间分析函数：
- 几何叠加：ST_Intersection、ST_Union、ST_Difference
- 缓冲区分析：ST_Buffer、ST_OffsetCurve
- 几何简化：ST_Simplify、ST_SimplifyPreserveTopology
- 几何修复：ST_MakeValid、ST_Snap
- 几何变换：ST_Translate、ST_Scale、ST_Rotate
- 高级分析：聚类、分割、泰森多边形
10.9 下一步
在下一章中，我们将学习空间测量函数，包括：
- 距离计算
- 面积计算
- 长度计算
- 角度测量
相关资源：
浙公网安备 33010602011771号