第07章-几何访问函数
第07章：几何访问函数
7.1 几何访问函数概述
几何访问函数用于获取几何对象的属性、组成部分和坐标信息。这些函数不修改原始几何，只是提取和返回信息。
7.1.1 函数分类
┌─────────────────────────────────────────────────────────────┐
│ 几何访问函数分类 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 类型和属性函数 │
│ ├── GeometryType - 获取几何类型 │
│ ├── ST_GeometryType - 获取几何类型（OGC 格式） │
│ ├── ST_Dimension - 获取几何维度 │
│ ├── ST_CoordDim - 获取坐标维度 │
│ ├── ST_NDims - 获取坐标维度数 │
│ └── ST_SRID - 获取空间参考 ID │
│ │
│ 坐标访问函数 │
│ ├── ST_X - 获取 X 坐标 │
│ ├── ST_Y - 获取 Y 坐标 │
│ ├── ST_Z - 获取 Z 坐标 │
│ ├── ST_M - 获取 M 值 │
│ └── ST_Zmflag - 获取 ZM 标志 │
│ │
│ 边界和范围函数 │
│ ├── ST_Boundary - 获取边界 │
│ ├── ST_Envelope - 获取外接矩形 │
│ ├── ST_XMin/XMax - 获取 X 范围 │
│ └── ST_YMin/YMax - 获取 Y 范围 │
│ │
│ 组件访问函数 │
│ ├── ST_NumGeometries - 获取子几何数量 │
│ ├── ST_GeometryN - 获取第 N 个子几何 │
│ ├── ST_NumPoints - 获取点数 │
│ ├── ST_PointN - 获取第 N 个点 │
│ └── ST_DumpPoints - 拆解所有点 │
│ │
└─────────────────────────────────────────────────────────────┘
7.2 类型和属性函数
7.2.1 GeometryType 和 ST_GeometryType
-- GeometryType：返回大写类型名（PostGIS 原生）
SELECT GeometryType(ST_MakePoint(0, 0)); -- POINT
SELECT GeometryType(ST_MakeLine(ST_MakePoint(0, 0), ST_MakePoint(1, 1))); -- LINESTRING
SELECT GeometryType(ST_MakeEnvelope(0, 0, 1, 1)); -- POLYGON
-- ST_GeometryType：返回 OGC 格式（带 ST_ 前缀）
SELECT ST_GeometryType(ST_MakePoint(0, 0)); -- ST_Point
SELECT ST_GeometryType(ST_MakeLine(ST_MakePoint(0, 0), ST_MakePoint(1, 1))); -- ST_LineString
-- 带 Z/M 的类型
SELECT GeometryType(ST_MakePoint(0, 0, 0)); -- POINT
SELECT GeometryType(ST_GeomFromText('POINT Z(0 0 0)')); -- POINT
SELECT GeometryType(ST_GeomFromText('POINT M(0 0 0)')); -- POINTM
SELECT GeometryType(ST_GeomFromText('POINT ZM(0 0 0 0)')); -- POINT
-- 在查询中使用
SELECT id, name, GeometryType(geom) AS type
FROM spatial_data
WHERE GeometryType(geom) IN ('POINT', 'MULTIPOINT');
-- 按类型分组统计
SELECT GeometryType(geom) AS type, COUNT(*) AS count
FROM spatial_data
GROUP BY GeometryType(geom);
7.2.2 ST_Dimension
-- 获取几何维度（拓扑维度，不是坐标维度）
-- 点: 0, 线: 1, 面: 2
SELECT ST_Dimension(ST_MakePoint(0, 0)); -- 0
SELECT ST_Dimension(ST_MakeLine(ST_MakePoint(0, 0), ST_MakePoint(1, 1))); -- 1
SELECT ST_Dimension(ST_MakeEnvelope(0, 0, 1, 1)); -- 2
-- 几何集合返回最大维度
SELECT ST_Dimension(ST_Collect(
ST_MakePoint(0, 0),
ST_MakeEnvelope(1, 1, 2, 2)
)); -- 2 (多边形的维度)
7.2.3 ST_CoordDim 和 ST_NDims
-- ST_CoordDim：获取坐标维度
SELECT ST_CoordDim(ST_MakePoint(0, 0)); -- 2 (XY)
SELECT ST_CoordDim(ST_MakePoint(0, 0, 0)); -- 3 (XYZ)
SELECT ST_CoordDim(ST_GeomFromText('POINT M(0 0 0)')); -- 3 (XYM)
SELECT ST_CoordDim(ST_GeomFromText('POINT ZM(0 0 0 0)')); -- 4 (XYZM)
-- ST_NDims：等同于 ST_CoordDim
SELECT ST_NDims(ST_MakePoint(0, 0)); -- 2
SELECT ST_NDims(ST_GeomFromText('POINT Z(0 0 0)')); -- 3
-- 检查是否为 3D 几何
SELECT * FROM spatial_data WHERE ST_CoordDim(geom) >= 3;
7.2.4 ST_SRID
-- 获取空间参考 ID
SELECT ST_SRID(ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)); -- 4326
SELECT ST_SRID(ST_MakePoint(0, 0)); -- 0（未设置）
-- 检查 SRID 是否正确
SELECT id, name FROM poi WHERE ST_SRID(geom) != 4326;
-- 统计 SRID 分布
SELECT ST_SRID(geom) AS srid, COUNT(*) AS count
FROM spatial_data
GROUP BY ST_SRID(geom);
7.2.5 ST_IsValid 和 ST_IsValidReason
-- 检查几何是否有效
SELECT ST_IsValid(ST_MakeEnvelope(0, 0, 1, 1)); -- true
-- 无效几何示例（自相交）
SELECT ST_IsValid(ST_GeomFromText('POLYGON((0 0, 2 0, 0 2, 2 2, 0 0))')); -- false
-- 获取无效原因
SELECT ST_IsValidReason(ST_GeomFromText('POLYGON((0 0, 2 0, 0 2, 2 2, 0 0))'));
-- 返回: Self-intersection[1 1]
-- 详细的有效性信息
SELECT ST_IsValidDetail(ST_GeomFromText('POLYGON((0 0, 2 0, 0 2, 2 2, 0 0))'));
-- 返回: (f, Self-intersection, POINT(1 1))
-- 查找所有无效几何
SELECT id, name, ST_IsValidReason(geom) AS reason
FROM spatial_data
WHERE NOT ST_IsValid(geom);
7.2.6 ST_IsSimple 和 ST_IsClosed
-- ST_IsSimple：检查几何是否简单（无自相交）
SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)')); -- true
SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(0 0, 1 1, 0 1, 1 0)')); -- false（自相交）
-- ST_IsClosed：检查线是否闭合
SELECT ST_IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)')); -- false
SELECT ST_IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0, 0 0)')); -- true
-- 多边形边界总是闭合的
SELECT ST_IsClosed(ST_Boundary(ST_MakeEnvelope(0, 0, 1, 1))); -- true
7.2.7 ST_IsRing 和 ST_IsCollection
-- ST_IsRing：检查线是否为环（简单且闭合）
SELECT ST_IsRing(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0, 0 0)')); -- true
SELECT ST_IsRing(ST_GeomFromText('LINESTRING(0 0, 1 1, 0 1, 1 0, 0 0)')); -- false（自相交）
-- ST_IsCollection：检查是否为几何集合
SELECT ST_IsCollection(ST_MakePoint(0, 0)); -- false
SELECT ST_IsCollection(ST_Collect(ST_MakePoint(0, 0), ST_MakePoint(1, 1))); -- true
SELECT ST_IsCollection(ST_GeomFromText('MULTIPOINT((0 0), (1 1))')); -- true
7.2.8 ST_IsEmpty
-- 检查几何是否为空
SELECT ST_IsEmpty(ST_GeomFromText('POINT EMPTY')); -- true
SELECT ST_IsEmpty(ST_MakePoint(0, 0)); -- false
-- 创建空几何
SELECT ST_GeomFromText('POINT EMPTY');
SELECT ST_GeomFromText('LINESTRING EMPTY');
SELECT ST_GeomFromText('POLYGON EMPTY');
SELECT ST_GeomFromText('GEOMETRYCOLLECTION EMPTY');
-- 过滤非空几何
SELECT * FROM spatial_data WHERE NOT ST_IsEmpty(geom);
7.3 坐标访问函数
7.3.1 ST_X、ST_Y、ST_Z、ST_M
-- 获取点的坐标
SELECT ST_X(ST_MakePoint(116.4, 39.9)); -- 116.4
SELECT ST_Y(ST_MakePoint(116.4, 39.9)); -- 39.9
-- 3D 点
SELECT ST_Z(ST_MakePoint(116.4, 39.9, 45.5)); -- 45.5
-- 带 M 值的点
SELECT ST_M(ST_MakePointM(116.4, 39.9, 100.0)); -- 100.0
-- 4D 点
WITH p AS (SELECT ST_GeomFromText('POINT ZM(116.4 39.9 45.5 100)') AS geom)
SELECT ST_X(geom), ST_Y(geom), ST_Z(geom), ST_M(geom) FROM p;
-- 只对 POINT 类型有效，其他类型返回 NULL
SELECT ST_X(ST_MakeLine(ST_MakePoint(0, 0), ST_MakePoint(1, 1))); -- NULL
-- 获取所有 POI 的坐标
SELECT id, name, ST_X(geom) AS longitude, ST_Y(geom) AS latitude
FROM poi
WHERE GeometryType(geom) = 'POINT';
7.3.2 ST_XMin/XMax/YMin/YMax/ZMin/ZMax
-- 获取边界框范围
SELECT
ST_XMin(geom) AS x_min,
ST_XMax(geom) AS x_max,
ST_YMin(geom) AS y_min,
ST_YMax(geom) AS y_max
FROM (SELECT ST_MakeEnvelope(116, 39, 117, 40) AS geom) t;
-- 获取复杂几何的范围
SELECT
ST_XMin(ST_Collect(geom)) AS min_lon,
ST_XMax(ST_Collect(geom)) AS max_lon,
ST_YMin(ST_Collect(geom)) AS min_lat,
ST_YMax(ST_Collect(geom)) AS max_lat
FROM poi;
-- 3D 几何的 Z 范围
SELECT
ST_ZMin(geom) AS z_min,
ST_ZMax(geom) AS z_max
FROM (SELECT ST_GeomFromText('LINESTRING Z(0 0 0, 1 1 10, 2 2 5)') AS geom) t;
-- M 值范围
SELECT
ST_MMin(geom) AS m_min,
ST_MMax(geom) AS m_max
FROM (SELECT ST_GeomFromText('LINESTRING M(0 0 0, 1 1 100, 2 2 50)') AS geom) t;
7.3.3 ST_Zmflag
-- 获取几何的 ZM 标志
-- 返回值: 0=2D, 1=M, 2=Z, 3=ZM
SELECT ST_Zmflag(ST_MakePoint(0, 0)); -- 0 (2D)
SELECT ST_Zmflag(ST_GeomFromText('POINT Z(0 0 0)')); -- 2 (Z)
SELECT ST_Zmflag(ST_GeomFromText('POINT M(0 0 0)')); -- 1 (M)
SELECT ST_Zmflag(ST_GeomFromText('POINT ZM(0 0 0 0)')); -- 3 (ZM)
-- 按 ZM 标志分组
SELECT
ST_Zmflag(geom) AS zm_flag,
CASE ST_Zmflag(geom)
WHEN 0 THEN '2D'
WHEN 1 THEN '带 M 值'
WHEN 2 THEN '带 Z 值'
WHEN 3 THEN '带 ZM 值'
END AS description,
COUNT(*) AS count
FROM spatial_data
GROUP BY ST_Zmflag(geom);
7.4 边界和范围函数
7.4.1 ST_Boundary
-- 获取几何的边界
-- 点的边界是空几何
SELECT ST_AsText(ST_Boundary(ST_MakePoint(0, 0))); -- GEOMETRYCOLLECTION EMPTY
-- 线的边界是端点
SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)')));
-- MULTIPOINT((0 0),(2 0))
-- 闭合线的边界是空
SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0, 0 0)')));
-- MULTIPOINT EMPTY
-- 多边形的边界是环
SELECT ST_AsText(ST_Boundary(ST_MakeEnvelope(0, 0, 1, 1)));
-- LINESTRING(0 0,0 1,1 1,1 0,0 0)
-- 带孔洞多边形的边界
SELECT ST_AsText(ST_Boundary(
ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))')
));
-- MULTILINESTRING((0 0,10 0,10 10,0 10,0 0),(2 2,8 2,8 8,2 8,2 2))
7.4.2 ST_Envelope
-- 获取边界框
SELECT ST_AsText(ST_Envelope(
ST_GeomFromText('POLYGON((0 0, 1 3, 3 1, 0 0))')
));
-- POLYGON((0 0,0 3,3 3,3 0,0 0))
-- 点的边界框是点本身
SELECT ST_AsText(ST_Envelope(ST_MakePoint(1, 1)));
-- POINT(1 1)
-- 水平或垂直线的边界框是线本身
SELECT ST_AsText(ST_Envelope(ST_GeomFromText('LINESTRING(0 0, 5 0)')));
-- LINESTRING(0 0,5 0)
-- 扩展边界框
SELECT ST_AsText(ST_Expand(ST_Envelope(geom), 0.1))
FROM poi
WHERE id = 1;
7.4.3 ST_BoundingDiagonal
-- 获取边界框的对角线
SELECT ST_AsText(ST_BoundingDiagonal(
ST_GeomFromText('POLYGON((0 0, 1 3, 3 1, 0 0))')
));
-- LINESTRING(0 0,3 3)
-- 获取对角线长度（边界框的对角线距离）
SELECT ST_Length(ST_BoundingDiagonal(geom)) AS diagonal_length
FROM districts;
-- 保留 Z 值
SELECT ST_AsText(ST_BoundingDiagonal(
ST_GeomFromText('LINESTRING Z(0 0 0, 1 1 10, 2 2 5)'),
true -- 保留高维度
));
-- LINESTRING Z(0 0 0,2 2 10)
7.5 组件访问函数
7.5.1 ST_NumGeometries 和 ST_GeometryN
-- 获取几何集合中的几何数量
SELECT ST_NumGeometries(ST_GeomFromText('MULTIPOINT((0 0), (1 1), (2 2))')); -- 3
SELECT ST_NumGeometries(ST_MakePoint(0, 0)); -- 1（单体几何也返回 1）
-- 获取第 N 个几何（索引从 1 开始）
SELECT ST_AsText(ST_GeometryN(
ST_GeomFromText('MULTIPOINT((0 0), (1 1), (2 2))'),
2
));
-- POINT(1 1)
-- 遍历所有子几何
WITH multi AS (
SELECT ST_GeomFromText('MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)), ((2 2, 3 2, 3 3, 2 3, 2 2)))') AS geom
)
SELECT
n AS geom_index,
ST_AsText(ST_GeometryN(geom, n)) AS sub_geom
FROM multi, generate_series(1, ST_NumGeometries(geom)) AS n;
-- 使用 ST_Dump 更方便
SELECT
(dump).path[1] AS index,
ST_AsText((dump).geom) AS geom
FROM (
SELECT ST_Dump(geom) AS dump
FROM multi_polygons
) t;
7.5.2 ST_NumPoints 和 ST_NPoints
-- ST_NumPoints：仅用于 LineString（OGC 标准）
SELECT ST_NumPoints(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)')); -- 3
-- ST_NPoints：用于任意几何类型（PostGIS 扩展）
SELECT ST_NPoints(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)')); -- 3
SELECT ST_NPoints(ST_MakeEnvelope(0, 0, 1, 1)); -- 5（包括闭合点）
SELECT ST_NPoints(ST_GeomFromText('MULTIPOINT((0 0), (1 1))')); -- 2
-- 统计几何的点数
SELECT id, name, ST_NPoints(geom) AS point_count
FROM polygons
ORDER BY ST_NPoints(geom) DESC
LIMIT 10;
7.5.3 ST_PointN 和 ST_StartPoint/ST_EndPoint
-- 获取线的第 N 个点
SELECT ST_AsText(ST_PointN(
ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2, 3 3)'),
2
));
-- POINT(1 1)
-- 负索引从末尾开始
SELECT ST_AsText(ST_PointN(
ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2, 3 3)'),
-1 -- 最后一个点
));
-- POINT(3 3)
-- 获取起点和终点
SELECT
ST_AsText(ST_StartPoint(geom)) AS start_point,
ST_AsText(ST_EndPoint(geom)) AS end_point
FROM roads;
-- 判断线的方向
SELECT
name,
CASE
WHEN ST_Y(ST_StartPoint(geom)) < ST_Y(ST_EndPoint(geom)) THEN '向北'
WHEN ST_Y(ST_StartPoint(geom)) > ST_Y(ST_EndPoint(geom)) THEN '向南'
ELSE '东西向'
END AS direction
FROM roads;
7.5.4 ST_DumpPoints
-- 拆解所有点
SELECT (ST_DumpPoints(geom)).*
FROM (SELECT ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') AS geom) t;
-- path | geom
-- {1,1} | POINT(0 0)
-- {1,2} | POINT(1 0)
-- {1,3} | POINT(1 1)
-- {1,4} | POINT(0 1)
-- {1,5} | POINT(0 0)
-- 拆解并获取坐标
SELECT
(dp).path AS point_path,
ST_X((dp).geom) AS x,
ST_Y((dp).geom) AS y
FROM (
SELECT ST_DumpPoints(geom) AS dp
FROM polygons
) t;
-- 计算多边形的顶点数（不包括重复的闭合点）
SELECT id, ST_NPoints(geom) - ST_NumInteriorRings(geom) - 1 AS vertex_count
FROM polygons;
7.5.5 ST_Dump 和 ST_DumpRings
-- ST_Dump：拆解几何集合
SELECT
(dump).path AS path,
ST_GeometryType((dump).geom) AS type,
ST_AsText((dump).geom) AS geom
FROM (
SELECT ST_Dump(ST_GeomFromText('MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)), ((2 2, 3 2, 3 3, 2 3, 2 2)))')) AS dump
) t;
-- ST_DumpRings：拆解多边形的环
SELECT
(ring).path[1] AS ring_index,
CASE WHEN (ring).path[1] = 0 THEN '外环' ELSE '内环 ' || (ring).path[1] END AS ring_type,
ST_AsText((ring).geom) AS ring
FROM (
SELECT ST_DumpRings(
ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))')
) AS ring
) t;
-- 递归拆解嵌套的几何集合
WITH RECURSIVE geom_tree AS (
-- 初始几何
SELECT
ARRAY[0]::int[] AS path,
geom,
0 AS depth
FROM complex_geometries
WHERE id = 1
UNION ALL
-- 递归拆解
SELECT
path || n,
ST_GeometryN(geom, n),
depth + 1
FROM geom_tree, generate_series(1, ST_NumGeometries(geom)) AS n
WHERE ST_NumGeometries(geom) > 1 AND depth < 10
)
SELECT path, ST_GeometryType(geom), ST_AsText(geom)
FROM geom_tree
WHERE ST_NumGeometries(geom) = 1;
7.6 线和多边形特定函数
7.6.1 线的访问函数
-- ST_NumPoints：获取线的点数
SELECT ST_NumPoints(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)')); -- 3
-- ST_PointOnSurface 对线也有效（返回线上的一点）
SELECT ST_AsText(ST_PointOnSurface(ST_GeomFromText('LINESTRING(0 0, 10 10)')));
-- ST_LineLocatePoint：定位点在线上的相对位置 (0-1)
SELECT ST_LineLocatePoint(
ST_GeomFromText('LINESTRING(0 0, 10 0)'),
ST_MakePoint(5, 0)
); -- 0.5
-- ST_LineInterpolatePoint：根据相对位置获取点
SELECT ST_AsText(ST_LineInterpolatePoint(
ST_GeomFromText('LINESTRING(0 0, 10 0)'),
0.5
)); -- POINT(5 0)
-- 获取线的中点
SELECT ST_AsText(ST_LineInterpolatePoint(geom, 0.5)) AS midpoint
FROM roads;
-- ST_LineSubstring：获取线的一段
SELECT ST_AsText(ST_LineSubstring(
ST_GeomFromText('LINESTRING(0 0, 10 10)'),
0.25, -- 起始位置
0.75 -- 结束位置
));
-- LINESTRING(2.5 2.5,7.5 7.5)
7.6.2 多边形的访问函数
-- ST_ExteriorRing：获取外环
SELECT ST_AsText(ST_ExteriorRing(ST_MakeEnvelope(0, 0, 1, 1)));
-- LINESTRING(0 0,0 1,1 1,1 0,0 0)
-- ST_NumInteriorRings：获取内环数量
SELECT ST_NumInteriorRings(
ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))')
); -- 1
-- ST_InteriorRingN：获取第 N 个内环
SELECT ST_AsText(ST_InteriorRingN(
ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))'),
1
));
-- LINESTRING(2 2,8 2,8 8,2 8,2 2)
-- 查找有孔洞的多边形
SELECT id, name, ST_NumInteriorRings(geom) AS hole_count
FROM polygons
WHERE ST_NumInteriorRings(geom) > 0;
-- ST_NRings：获取所有环的数量（外环 + 内环）
SELECT ST_NRings(
ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))')
); -- 2
7.6.3 特征点函数
-- ST_Centroid：获取质心（几何中心）
SELECT ST_AsText(ST_Centroid(ST_MakeEnvelope(0, 0, 2, 2)));
-- POINT(1 1)
-- 注意：质心可能在多边形外部
SELECT ST_AsText(ST_Centroid(
ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 2 2, 0 4, 0 0))')
));
-- ST_PointOnSurface：获取在几何内部的点
SELECT ST_AsText(ST_PointOnSurface(
ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 2 2, 0 4, 0 0))')
));
-- 比较 Centroid 和 PointOnSurface
SELECT
name,
ST_Contains(geom, ST_Centroid(geom)) AS centroid_inside,
ST_Contains(geom, ST_PointOnSurface(geom)) AS point_on_surface_inside
FROM districts;
-- ST_MinimumClearance：获取最小净距
SELECT ST_MinimumClearance(
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0.5 0.5, 0 1, 0 0))')
);
7.7 实用查询示例
7.7.1 几何信息汇总
-- 创建几何信息汇总视图
CREATE VIEW geometry_info AS
SELECT
id,
name,
GeometryType(geom) AS type,
ST_SRID(geom) AS srid,
ST_CoordDim(geom) AS coord_dim,
ST_NPoints(geom) AS point_count,
ST_IsValid(geom) AS is_valid,
ST_IsSimple(geom) AS is_simple,
ST_XMin(geom) AS x_min,
ST_YMin(geom) AS y_min,
ST_XMax(geom) AS x_max,
ST_YMax(geom) AS y_max,
CASE
WHEN GeometryType(geom) = 'POINT' THEN NULL
WHEN GeometryType(geom) LIKE '%POLYGON%' THEN ST_Area(geom::geography)
ELSE NULL
END AS area_m2,
CASE
WHEN GeometryType(geom) = 'POINT' THEN NULL
WHEN GeometryType(geom) LIKE '%LINE%' THEN ST_Length(geom::geography)
WHEN GeometryType(geom) LIKE '%POLYGON%' THEN ST_Perimeter(geom::geography)
ELSE NULL
END AS length_m
FROM spatial_data;
7.7.2 数据质量检查
-- 全面的数据质量检查
SELECT
'SRID 检查' AS check_type,
COUNT(*) FILTER (WHERE ST_SRID(geom) != 4326) AS issue_count,
'SRID 不是 4326' AS description
FROM spatial_data
UNION ALL
SELECT
'有效性检查',
COUNT(*) FILTER (WHERE NOT ST_IsValid(geom)),
'无效几何'
FROM spatial_data
UNION ALL
SELECT
'空几何检查',
COUNT(*) FILTER (WHERE ST_IsEmpty(geom)),
'空几何'
FROM spatial_data
UNION ALL
SELECT
'坐标范围检查',
COUNT(*) FILTER (WHERE ST_XMin(geom) < -180 OR ST_XMax(geom) > 180 OR ST_YMin(geom) < -90 OR ST_YMax(geom) > 90),
'坐标超出范围'
FROM spatial_data;
7.7.3 几何统计
-- 按类型统计几何
SELECT
GeometryType(geom) AS type,
COUNT(*) AS count,
AVG(ST_NPoints(geom))::INT AS avg_points,
MAX(ST_NPoints(geom)) AS max_points,
pg_size_pretty(SUM(pg_column_size(geom))) AS total_size
FROM spatial_data
GROUP BY GeometryType(geom)
ORDER BY count DESC;
7.8 本章小结
本章详细介绍了 PostGIS 的几何访问函数：
- 类型和属性：GeometryType、ST_Dimension、ST_IsValid 等
- 坐标访问：ST_X/Y/Z/M、ST_XMin/XMax 等
- 边界和范围：ST_Boundary、ST_Envelope 等
- 组件访问：ST_NumGeometries、ST_GeometryN、ST_Dump 等
- 线和多边形特定函数：ST_ExteriorRing、ST_InteriorRingN 等
- 特征点函数：ST_Centroid、ST_PointOnSurface 等
- 实用查询示例：数据质量检查和几何统计
7.9 下一步
在下一章中，我们将学习几何输出函数，包括：
- WKT/WKB 输出
- GeoJSON/GML/KML 输出
- SVG/编码折线输出
- 格式化输出
相关资源：
浙公网安备 33010602011771号