第11章-空间测量函数
第11章：空间测量函数
11.1 测量函数概述
空间测量函数用于计算几何对象的各种度量属性，如距离、长度、面积、角度等。
11.1.1 测量函数分类
| 函数 | 说明 | 返回单位 |
|---|---|---|
| ST_Distance | 两点间距离 | 坐标单位或米 |
| ST_Length | 线的长度 | 坐标单位或米 |
| ST_Perimeter | 多边形周长 | 坐标单位或米 |
| ST_Area | 多边形面积 | 坐标单位²或米² |
| ST_Azimuth | 方位角 | 弧度 |
| ST_Angle | 三点角度 | 弧度 |
11.2 距离计算
11.2.1 ST_Distance
-- Geometry 距离（坐标单位）
SELECT ST_Distance(
ST_GeomFromText('POINT(0 0)'),
ST_GeomFromText('POINT(3 4)')
); -- 5
-- Geography 距离（米）
SELECT ST_Distance(
ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography,
ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)::geography
); -- 约 1067799 米
-- 使用椭球体或球体
-- use_spheroid = true（默认，更精确）
-- use_spheroid = false（更快）
SELECT ST_Distance(
ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography,
ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)::geography,
false -- 使用球体
);
-- 点到线的距离
SELECT ST_Distance(
ST_GeomFromText('POINT(1 1)'),
ST_GeomFromText('LINESTRING(0 0, 2 0)')
); -- 1
-- 点到多边形的距离
SELECT ST_Distance(
ST_GeomFromText('POINT(3 3)'),
ST_MakeEnvelope(0, 0, 2, 2)
); -- √2 ≈ 1.414
11.2.2 ST_3DDistance
-- 3D 距离计算
SELECT ST_3DDistance(
ST_GeomFromText('POINT Z(0 0 0)'),
ST_GeomFromText('POINT Z(1 1 1)')
); -- √3 ≈ 1.732
-- 3D 点到线的距离
SELECT ST_3DDistance(
ST_GeomFromText('POINT Z(1 1 1)'),
ST_GeomFromText('LINESTRING Z(0 0 0, 2 0 0)')
);
11.2.3 ST_MaxDistance
-- 计算两个几何之间的最大距离
SELECT ST_MaxDistance(
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
ST_GeomFromText('POLYGON((2 2, 3 2, 3 3, 2 3, 2 2))')
);
-- 应用：检查两个区域是否完全分离
SELECT
a.name,
b.name,
ST_Distance(a.geom, b.geom) AS min_distance,
ST_MaxDistance(a.geom, b.geom) AS max_distance
FROM districts a, districts b
WHERE a.id < b.id;
11.2.4 ST_HausdorffDistance
-- 豪斯多夫距离（衡量几何相似性）
SELECT ST_HausdorffDistance(
ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)'),
ST_GeomFromText('LINESTRING(0 0, 1 0.9, 2 0)')
);
-- 带密度参数
SELECT ST_HausdorffDistance(
geom_a,
geom_b,
0.5 -- 密度因子
);
-- 应用：比较两条道路的相似性
SELECT
a.name AS road_a,
b.name AS road_b,
ST_HausdorffDistance(a.geom, b.geom) AS similarity
FROM roads a, roads b
WHERE a.id < b.id AND ST_DWithin(a.geom, b.geom, 0.01);
11.2.5 ST_FrechetDistance
-- 弗雷歇距离（考虑方向的相似性度量）
SELECT ST_FrechetDistance(
ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)'),
ST_GeomFromText('LINESTRING(0 0, 1 0.5, 2 0)')
);
-- 应用：轨迹相似性分析
SELECT ST_FrechetDistance(track_a.geom, track_b.geom) AS trajectory_similarity
FROM gps_tracks track_a, gps_tracks track_b
WHERE track_a.device_id = 'device1' AND track_b.device_id = 'device2';
11.3 长度计算
11.3.1 ST_Length
-- Geometry 长度（坐标单位）
SELECT ST_Length(ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1)')); -- 2
-- Geography 长度（米）
SELECT ST_Length(
ST_SetSRID(ST_GeomFromText('LINESTRING(116.3 39.9, 116.5 39.9)'), 4326)::geography
);
-- 使用椭球体或球体
SELECT ST_Length(geom::geography, false) FROM roads; -- 球体计算
-- 计算道路总长度
SELECT SUM(ST_Length(geom::geography)) / 1000 AS total_length_km
FROM roads
WHERE road_type = '高速公路';
-- 按类型统计道路长度
SELECT
road_type,
SUM(ST_Length(geom::geography)) / 1000 AS length_km,
COUNT(*) AS count
FROM roads
GROUP BY road_type
ORDER BY length_km DESC;
11.3.2 ST_3DLength
-- 3D 线长度
SELECT ST_3DLength(
ST_GeomFromText('LINESTRING Z(0 0 0, 1 0 0, 1 1 0, 1 1 1)')
); -- 3
-- 应用：计算管道实际长度（考虑高程变化）
SELECT name, ST_3DLength(geom) AS actual_length
FROM pipelines;
11.3.3 ST_LengthSpheroid
-- 在指定椭球体上计算长度
SELECT ST_LengthSpheroid(
ST_GeomFromText('LINESTRING(116.3 39.9, 116.5 39.9)'),
'SPHEROID["WGS 84",6378137,298.257223563]'
);
11.3.4 ST_Perimeter
-- Geometry 周长（坐标单位）
SELECT ST_Perimeter(ST_MakeEnvelope(0, 0, 2, 2)); -- 8
-- Geography 周长（米）
SELECT ST_Perimeter(
ST_SetSRID(ST_MakeEnvelope(116.3, 39.8, 116.5, 40.0), 4326)::geography
);
-- 计算行政区边界长度
SELECT name, ST_Perimeter(geom::geography) / 1000 AS perimeter_km
FROM districts
ORDER BY perimeter_km DESC;
-- 周长面积比（形状指数）
SELECT
name,
ST_Perimeter(geom::geography) / 1000 AS perimeter_km,
ST_Area(geom::geography) / 1000000 AS area_km2,
ST_Perimeter(geom::geography) / SQRT(ST_Area(geom::geography)) AS shape_index
FROM districts;
11.4 面积计算
11.4.1 ST_Area
-- Geometry 面积（坐标单位²）
SELECT ST_Area(ST_MakeEnvelope(0, 0, 2, 2)); -- 4
-- Geography 面积（米²）
SELECT ST_Area(
ST_SetSRID(ST_MakeEnvelope(116.3, 39.8, 116.5, 40.0), 4326)::geography
);
-- 转换为平方公里
SELECT name, ST_Area(geom::geography) / 1000000 AS area_km2
FROM districts;
-- 使用椭球体或球体
SELECT ST_Area(geom::geography, false) FROM districts; -- 球体计算（更快）
-- 按区域统计
SELECT
province,
SUM(ST_Area(geom::geography)) / 1000000 AS total_area_km2,
COUNT(*) AS district_count
FROM districts
GROUP BY province;
11.4.2 面积比较与百分比
-- 计算重叠面积百分比
SELECT
a.name AS district_a,
b.name AS district_b,
ST_Area(ST_Intersection(a.geom, b.geom)::geography) AS overlap_area,
ST_Area(ST_Intersection(a.geom, b.geom)::geography) / ST_Area(a.geom::geography) * 100 AS overlap_pct_a
FROM districts a, districts b
WHERE a.id < b.id
AND ST_Intersects(a.geom, b.geom);
-- 计算土地利用占比
SELECT
land_type,
SUM(ST_Area(geom::geography)) / 1000000 AS area_km2,
SUM(ST_Area(geom::geography)) / (SELECT SUM(ST_Area(geom::geography)) FROM land_use) * 100 AS percentage
FROM land_use
GROUP BY land_type;
11.4.3 投影面积 vs 球面面积
-- 比较不同计算方法
SELECT
name,
ST_Area(geom) AS planar_area, -- 平面面积（度²，无意义）
ST_Area(ST_Transform(geom, 3857)) AS mercator_area, -- 墨卡托投影（变形）
ST_Area(geom::geography) AS geodetic_area -- 球面面积（准确）
FROM districts;
-- 对于小区域，可以使用投影坐标系
SELECT
name,
ST_Area(ST_Transform(geom, 4527)) AS projected_area_m2 -- CGCS2000 投影
FROM districts
WHERE province = '北京';
11.5 角度与方位
11.5.1 ST_Azimuth
-- 计算两点之间的方位角（北为0，顺时针，弧度）
SELECT degrees(ST_Azimuth(
ST_MakePoint(0, 0),
ST_MakePoint(1, 0)
)); -- 90度（东）
SELECT degrees(ST_Azimuth(
ST_MakePoint(0, 0),
ST_MakePoint(0, 1)
)); -- 0度（北）
SELECT degrees(ST_Azimuth(
ST_MakePoint(0, 0),
ST_MakePoint(1, 1)
)); -- 45度（东北）
-- 计算道路起点到终点的方向
SELECT
name,
degrees(ST_Azimuth(ST_StartPoint(geom), ST_EndPoint(geom))) AS direction
FROM roads;
-- 按方向分类
SELECT
name,
CASE
WHEN degrees(ST_Azimuth(ST_StartPoint(geom), ST_EndPoint(geom))) BETWEEN 315 AND 360
OR degrees(ST_Azimuth(ST_StartPoint(geom), ST_EndPoint(geom))) BETWEEN 0 AND 45 THEN '南北向'
WHEN degrees(ST_Azimuth(ST_StartPoint(geom), ST_EndPoint(geom))) BETWEEN 45 AND 135 THEN '东西向'
WHEN degrees(ST_Azimuth(ST_StartPoint(geom), ST_EndPoint(geom))) BETWEEN 135 AND 225 THEN '南北向'
ELSE '东西向'
END AS orientation
FROM roads;
11.5.2 ST_Angle
-- 计算三点形成的角度
SELECT degrees(ST_Angle(
ST_MakePoint(0, 0),
ST_MakePoint(1, 1), -- 顶点
ST_MakePoint(2, 0)
)); -- 返回顶点处的角度
-- 计算两条线的夹角
SELECT degrees(ST_Angle(
ST_GeomFromText('LINESTRING(0 0, 1 1)'),
ST_GeomFromText('LINESTRING(1 1, 2 0)')
));
-- 检测道路转角
WITH road_points AS (
SELECT
id,
(ST_DumpPoints(geom)).path[1] AS point_index,
(ST_DumpPoints(geom)).geom AS point
FROM roads
)
SELECT
r.id,
p1.point_index,
degrees(ST_Angle(p1.point, p2.point, p3.point)) AS turn_angle
FROM road_points p1
JOIN road_points p2 ON p1.id = p2.id AND p2.point_index = p1.point_index + 1
JOIN road_points p3 ON p1.id = p3.id AND p3.point_index = p1.point_index + 2
JOIN roads r ON p1.id = r.id
WHERE abs(degrees(ST_Angle(p1.point, p2.point, p3.point)) - 180) > 30; -- 大于30度的转角
11.6 其他测量函数
11.6.1 ST_MinimumClearance
-- 计算几何的最小净距（最小的自相交距离）
SELECT ST_MinimumClearance(
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0.1 0.1, 0 1, 0 0))')
);
-- 用于检测几何精度问题
SELECT id, ST_MinimumClearance(geom) AS clearance
FROM polygons
WHERE ST_MinimumClearance(geom) < 0.00001;
11.6.2 ST_LongestLine 和 ST_ShortestLine
-- 两个几何之间的最长连线
SELECT ST_AsText(ST_LongestLine(
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
ST_GeomFromText('POLYGON((3 3, 4 3, 4 4, 3 4, 3 3))')
));
-- 两个几何之间的最短连线
SELECT ST_AsText(ST_ShortestLine(
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
ST_GeomFromText('POLYGON((3 3, 4 3, 4 4, 3 4, 3 3))')
));
-- 应用：可视化最近点
SELECT
a.name AS from_poi,
b.name AS to_poi,
ST_ShortestLine(a.geom, b.geom) AS connection_line,
ST_Length(ST_ShortestLine(a.geom, b.geom)::geography) AS distance_m
FROM poi a, poi b
WHERE a.category = '医院' AND b.category = '学校'
AND a.id != b.id
ORDER BY distance_m
LIMIT 10;
11.6.3 ST_ClosestPoint
-- 获取 A 上距离 B 最近的点
SELECT ST_AsText(ST_ClosestPoint(
ST_GeomFromText('LINESTRING(0 0, 2 2)'),
ST_GeomFromText('POINT(0 2)')
)); -- POINT(1 1)
-- 应用：找到道路上最近的点
SELECT
p.name AS poi_name,
r.name AS road_name,
ST_AsText(ST_ClosestPoint(r.geom, p.geom)) AS nearest_point_on_road,
ST_Distance(p.geom, r.geom) AS distance
FROM poi p, roads r
WHERE ST_DWithin(p.geom, r.geom, 0.01)
ORDER BY distance;
11.7 测量精度注意事项
11.7.1 坐标系影响
-- 不同坐标系的测量差异
SELECT
'4326 Geography' AS method,
ST_Length(ST_SetSRID(ST_GeomFromText('LINESTRING(116.3 39.9, 116.5 39.9)'), 4326)::geography) AS length_m
UNION ALL
SELECT
'3857 Mercator',
ST_Length(ST_Transform(ST_SetSRID(ST_GeomFromText('LINESTRING(116.3 39.9, 116.5 39.9)'), 4326), 3857))
UNION ALL
SELECT
'4527 CGCS2000',
ST_Length(ST_Transform(ST_SetSRID(ST_GeomFromText('LINESTRING(116.3 39.9, 116.5 39.9)'), 4326), 4527));
11.7.2 精度建议
-- 小区域（城市级别）：使用投影坐标系
SELECT ST_Area(ST_Transform(geom, 4527)) / 1000000 AS area_km2 -- 高斯-克吕格投影
FROM districts WHERE province = '北京';
-- 大区域（省级、国家级）：使用 Geography 类型
SELECT ST_Area(geom::geography) / 1000000 AS area_km2
FROM provinces;
-- 全球范围：使用 Geography 类型，考虑球体/椭球体选择
SELECT ST_Distance(point_a::geography, point_b::geography, true) AS ellipsoid_dist,
ST_Distance(point_a::geography, point_b::geography, false) AS sphere_dist
FROM global_cities;
11.8 实际应用案例
11.8.1 服务范围分析
-- 医院服务范围分析
WITH hospital_coverage AS (
SELECT
h.name AS hospital,
h.geom,
ST_Buffer(h.geom::geography, 5000)::geometry AS service_area_5km
FROM poi h
WHERE h.category = '医院'
)
SELECT
h.hospital,
COUNT(p.id) AS population_covered,
SUM(p.population) AS total_population
FROM hospital_coverage h
LEFT JOIN residential_areas p ON ST_Intersects(h.service_area_5km, p.geom)
GROUP BY h.hospital;
11.8.2 路网密度分析
-- 计算区域内的道路密度
SELECT
d.name,
ST_Area(d.geom::geography) / 1000000 AS area_km2,
SUM(ST_Length(ST_Intersection(r.geom, d.geom)::geography)) / 1000 AS road_length_km,
SUM(ST_Length(ST_Intersection(r.geom, d.geom)::geography)) / 1000 /
(ST_Area(d.geom::geography) / 1000000) AS road_density_km_per_km2
FROM districts d
LEFT JOIN roads r ON ST_Intersects(d.geom, r.geom)
GROUP BY d.id, d.name, d.geom;
11.9 本章小结
本章详细介绍了 PostGIS 的空间测量函数：
- 距离计算：ST_Distance、ST_3DDistance、ST_HausdorffDistance
- 长度计算：ST_Length、ST_Perimeter、ST_3DLength
- 面积计算：ST_Area、面积比较
- 角度方位：ST_Azimuth、ST_Angle
- 其他测量：最短线、最近点
- 精度注意事项：坐标系选择、球面/椭球面计算
11.10 下一步
在下一章中，我们将学习栅格数据处理，包括：
- 栅格数据类型
- 栅格导入导出
- 栅格分析函数
- 矢量栅格转换
相关资源：
浙公网安备 33010602011771号