乐于分享
好东西不私藏
夜雨聆风 > > 办公文件 > Postgis常用函数使用说明汇总

Postgis常用函数使用说明汇总

当前时间： 2026-08-16 05:11:31 分类：办公文件 评论(0)
Postgis常用函数使用说明汇总

几何构造与格式转换函数

1. ST_GeomFromText — WKT 转几何对象

功能说明：将 Well-Known Text（WKT）格式的文本转换为 PostGIS 几何对象（geometry）。

语法：

ST_GeomFromText(text WKT, integer srid);

参数：

  • WKT：几何的文本表示，如 POINT(116.4 39.9)
  • srid：空间参考系编号（可选，如 4326 代表 WGS84）

示例：

-- 创建一个点几何（北京坐标）
SELECT ST_GeomFromText('POINT(116.4074 39.9042)', 4326);

-- 创建一条线
SELECT ST_GeomFromText('LINESTRING(116.0 39.0, 117.0 40.0, 118.0 39.5)', 4326);

-- 创建一个多边形
SELECT ST_GeomFromText('POLYGON((116.0 39.0, 117.0 39.0, 117.0 40.0, 116.0 40.0, 116.0 39.0))', 4326);

2. ST_MakePoint — 快速构造点

功能说明：根据 X、Y 坐标直接创建点几何对象，适合批量生成点数据。

语法：

ST_MakePoint(float x, float y);
ST_MakePoint(float x, float y, float z);

示例：

-- 创建二维点（经度, 纬度）
SELECT ST_MakePoint(116.4074, 39.9042);

-- 创建带坐标系的点
SELECT ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326);

-- 创建三维点
SELECT ST_MakePoint(116.4074, 39.9042, 50);
“

ST_MakePoint 本身不设置 SRID，通常需要配合 ST_SetSRID 使用。

3. ST_AsText — 几何转 WKT 文本

功能说明：将几何对象转换为 Well-Known Text（WKT）格式的可读文本，用于调试和输出。

语法：

ST_AsText(geometry g1);

示例：

-- 查看点的文本表示
SELECT ST_AsText(ST_MakePoint(116.4074, 39.9042));
-- 输出：POINT(116.4074 39.9042)

-- 查看多边形
SELECT ST_AsText(ST_Buffer(ST_MakePoint(0, 0), 10));

4. ST_AsGeoJSON — 几何转 GeoJSON

功能说明：将几何对象转换为 GeoJSON 格式字符串。

语法：

ST_AsGeoJSON(geometry geom);
ST_AsGeoJSON(geometry geom, integer maxdecimaldigits);

示例：

-- 转换为 GeoJSON
SELECT ST_AsGeoJSON(ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326));
-- 输出：{"type":"Point","coordinates":[116.4074,39.9042]}

-- 保留 6 位小数
SELECT ST_AsGeoJSON(geom, 6) FROM cities WHEREname = '北京';

5. ST_GeomFromGeoJSON — GeoJSON 转几何

功能说明：将 GeoJSON 格式的字符串解析为几何对象。

语法：

ST_GeomFromGeoJSON(text geomjson);

示例：

-- 从 GeoJSON 创建点
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[116.4074,39.9042]}');

-- 带 SRID 的完整写法
SELECT ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[116.4,39.9]}'), 4326);

几何访问器函数

1. ST_X / ST_Y — 获取点的坐标值

功能说明：提取点几何的 X（经度）或 Y（纬度）坐标值，只能用于 Point 类型。

语法：

ST_X(geometry a_point);
ST_Y(geometry a_point);

示例：

-- 获取北京点的经纬度
SELECT
  ST_X(ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)) AS 经度,
  ST_Y(ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)) AS 纬度;

-- 从表中批量提取坐标
SELECTname, ST_X(geom) AS lng, ST_Y(geom) AS lat 
FROM poi_table;

2. GeometryType — 获取几何类型

功能说明：返回几何对象的类型名称，如 POINT、LINESTRING、POLYGON、MULTIPOLYGON 等。

语法：

GeometryType(geometry g1);

示例：

-- 判断几何类型
SELECT GeometryType(ST_GeomFromText('POINT(1 2)'));
-- 输出：ST_Point

SELECT GeometryType(ST_GeomFromText('LINESTRING(0 0, 1 1)'));
-- 输出：ST_LineString

-- 检查表中几何类型分布
SELECT GeometryType(geom), COUNT(*) 
FROM spatial_table 
GROUPBY GeometryType(geom);

3. ST_NumPoints — 获取顶点数量

功能说明：返回线串或环的顶点数量，常用于检查几何的复杂度。

语法：

ST_NumPoints(geometry g1);

示例：

-- 统计线的顶点数
SELECT ST_NumPoints(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2, 3 3)'));
-- 输出：4

-- 查找顶点数超过 1000 的复杂几何
SELECTid, ST_NumPoints(geom) 
FROM polygons 
WHERE ST_NumPoints(geom) > 1000;

空间关系判断函数

1. ST_Intersects — 判断是否相交

功能说明：判断两个几何对象是否存在空间交集（有任何公共点即返回 true）。

语法：

ST_Intersects(geometry g1, geometry g2);

示例：

-- 判断点是否在多边形内
SELECT ST_Intersects(
  ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326),
  ST_GeomFromText('POLYGON((116 39, 117 39, 117 40, 116 40, 116 39))', 4326)
);
-- 输出：true

-- 查询与北京市相交的所有区县
SELECTname
FROM districts 
WHERE ST_Intersects(geom, (SELECT geom FROM cities WHEREname = '北京市'));
“

ST_Intersects 会自动使用空间索引（GIST），是 WHERE 条件中的首选函数。

2. ST_Contains — 判断包含关系

功能说明：判断几何 A 是否完全包含几何 B（B 的所有点都在 A 内部，且边界不接触）。

语法：

ST_Contains(geometry geomA, geometry geomB);

示例：

-- 判断点是否完全在多边形内部
SELECT ST_Contains(
  ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
  ST_GeomFromText('POINT(5 5)')
);
-- 输出：true

-- 边界上的点不被包含
SELECT ST_Contains(
  ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
  ST_GeomFromText('POINT(0 5)')
);
-- 输出：false

-- 查询河北省内的所有城市
SELECT city_name 
FROM cities 
WHERE ST_Contains(
  (SELECT geom FROM provinces WHEREname = '河北省'),
  geom
);

3. ST_Within — 判断被包含

功能说明：ST_Within(A, B) 等价于 ST_Contains(B, A)，判断 A 是否完全在 B 内部。

语法：

ST_Within(geometry geomA, geometry geomB);

示例：

-- 查询所有位于北京市内的 POI
SELECT poi_name 
FROM pois 
WHERE ST_Within(geom, (SELECT geom FROM cities WHEREname = '北京市'));

-- 等价写法（使用 ST_Contains）
SELECT poi_name 
FROM pois 
WHERE ST_Contains((SELECT geom FROM cities WHEREname = '北京市'), geom);

4. ST_DWithin — 距离范围内查询 ⭐

功能说明：判断两个几何之间的距离是否在指定阈值内。这是最常用的邻近查询函数，支持空间索引加速。

语法：

ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);
ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid=true);

示例：

-- 平面坐标系：查询距离某点 1000 米范围内的所有 POI
SELECTname
FROM pois 
WHERE ST_DWithin(
  geom, 
  ST_Transform(ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326), 3857),
1000-- 单位与坐标系一致（米）
);

-- 地理坐标系（geography 类型）：直接用米作单位
SELECTname
FROM pois_geog 
WHERE ST_DWithin(
  geog,
  ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)::geography,
5000-- 5000 米
);

-- 查询距离地铁站 500 米内的小区
SELECT community_name 
FROM communities c
JOIN stations s ON ST_DWithin(c.geom, s.geom, 500)
WHERE s.name = '中关村站';
“

使用 geometry 类型时，距离单位与坐标系单位一致（经纬度坐标系下是度，不是米！）。需要米级精度使用 geography 类型或投影到米制坐标系（如 3857）。

5. ST_Touches — 判断是否相接

功能说明：判断两个几何是否仅在边界上接触，但内部不相交。

语法：

ST_Touches(geometry g1, geometry g2);

示例：

-- 两个相邻的多边形（共享边界）
SELECT ST_Touches(
  ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
  ST_GeomFromText('POLYGON((10 0, 20 0, 20 10, 10 10, 10 0))')
);
-- 输出：true

-- 查询与某区相邻的所有区县
SELECTname
FROM districts 
WHERE ST_Touches(geom, (SELECT geom FROM districts WHEREname = '海淀区'));

6. ST_Disjoint — 判断是否分离

功能说明：判断两个几何完全不相交（没有任何公共点），是 ST_Intersects 的取反。

语法：

ST_Disjoint(geometry g1, geometry g2);

示例：

SELECT ST_Disjoint(
  ST_GeomFromText('POINT(0 0)'),
  ST_GeomFromText('LINESTRING(2 0, 0 2)')
);
-- 输出：true

7. ST_Crosses — 判断是否交叉穿过

功能说明：判断两个几何是否交叉穿过（内部有交集但互不包含），常用于线与线、线与面的关系判断。

语法：

ST_Crosses(geometry g1, geometry g2);

示例：

-- 两条线交叉
SELECT ST_Crosses(
  ST_GeomFromText('LINESTRING(0 0, 10 10)'),
  ST_GeomFromText('LINESTRING(0 10, 10 0)')
);
-- 输出：true

-- 查询穿过某区域的道路
SELECT road_name 
FROM roads 
WHERE ST_Crosses(geom, (SELECT geom FROM zones WHEREname = '开发区'));

8. ST_Overlaps — 判断是否重叠

功能说明：判断两个同维度的几何是否部分重叠（有交集但互不包含）。

语法：

ST_Overlaps(geometry g1, geometry g2);

示例：

SELECT ST_Overlaps(
  ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
  ST_GeomFromText('POLYGON((5 5, 15 5, 15 15, 5 15, 5 5))')
);
-- 输出：true

几何处理与分析函数

1. ST_Buffer — 生成缓冲区

功能说明：在几何对象周围生成指定距离的缓冲区（多边形）。

语法：

ST_Buffer(geometry g1, float radius_of_buffer);
ST_Buffer(geometry g1, float radius, integer num_seg_quarter_circle);
ST_Buffer(geometry g1, float radius, text buffer_style_parameters);

参数说明：

  • radius：缓冲区半径，单位与坐标系一致
  • num_seg_quarter_circle：四分之一圆的线段数（默认 8），值越大越平滑
  • buffer_style_parameters：高级样式参数，如 'endcap=round join=round'

示例：

-- 点的缓冲区（生成圆形）
SELECT ST_Buffer(ST_MakePoint(0, 0), 10);

-- 线的缓冲区（生成带状面）
SELECT ST_Buffer(
  ST_GeomFromText('LINESTRING(0 0, 100 0)'),
10
);

-- 高精度缓冲区（四分之一圆 32 段）
SELECT ST_Buffer(ST_MakePoint(0, 0), 100, 32);

-- 方形端点的线缓冲区
SELECT ST_Buffer(
  ST_GeomFromText('LINESTRING(0 0, 100 0)'),
10,
'endcap=square join=mitre'
);

-- 实际应用：查询地铁站 500 米覆盖范围
SELECT ST_Buffer(geom, 500) AS coverage_area
FROM subway_stations
WHEREname = '国贸站';
“

样式参数可选值：

  • endcap：round（圆头）、flat（平头）、square（方头）
  • join：round（圆角）、mitre（尖角）、bevel（斜切）

2. ST_Intersection — 求交集

功能说明：计算两个几何对象的交集部分，返回它们共有的几何区域。

语法：

ST_Intersection(geometry geomA, geometry geomB);

示例：

-- 两个圆的交集
SELECT ST_AsText(ST_Intersection(
  ST_Buffer(ST_MakePoint(0, 0), 5),
  ST_Buffer(ST_MakePoint(3, 0), 5)
));

-- 计算两个行政区的重叠区域
SELECT ST_Intersection(a.geom, b.geom) AS overlap_area
FROM districts a, districts b
WHERE a.name = '海淀区'AND b.name = '朝阳区';

-- 裁剪：提取道路在某区域内的部分
SELECT ST_Intersection(r.geom, z.geom) AS road_segment
FROM roads r, zones z
WHERE z.name = '经济开发区'AND ST_Intersects(r.geom, z.geom);

3. ST_Union — 几何合并

功能说明：将多个几何对象合并为一个几何对象，消除重叠部分。支持两个几何合并或聚合合并。

语法：

ST_Union(geometry g1, geometry g2);
ST_Union(geometry[] g1_array);
-- 聚合函数形式
ST_Union(geometry geom);

示例：

-- 合并两个多边形
SELECT ST_Union(
  ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
  ST_GeomFromText('POLYGON((5 5, 15 5, 15 15, 5 15, 5 5))')
);

-- 聚合：合并某省所有地级市的边界
SELECT ST_Union(geom) AS province_geom
FROM cities
WHERE province = '广东省';

-- 聚合：合并缓冲区生成服务范围
SELECT ST_Union(ST_Buffer(geom, 1000)) AS service_area
FROM hospitals
WHERElevel = '三级甲等';

4. ST_Difference — 求差集

功能说明：从几何 A 中减去与几何 B 重叠的部分，返回剩余区域。

语法：

ST_Difference(geometry geomA, geometry geomB);

示例：

-- 从大圆中挖去小圆
SELECT ST_Difference(
  ST_Buffer(ST_MakePoint(0, 0), 10),
  ST_Buffer(ST_MakePoint(0, 0), 5)
);

-- 计算某区域内除水域外的陆地面积
SELECT ST_Difference(land_geom, water_geom) AS land_only
FROM regions WHEREname = '某区';

5. ST_SymDifference — 对称差

功能说明：返回两个几何中不重叠的部分（异或运算），即 A∪B - A∩B。

语法：

ST_SymDifference(geometry geomA, geometry geomB);

示例：

SELECT ST_SymDifference(
  ST_Buffer(ST_MakePoint(0, 0), 5),
  ST_Buffer(ST_MakePoint(3, 0), 5)
);

6. ST_Centroid — 计算质心

功能说明：计算几何对象的几何质心（重心），常用于将面转换为中心点。

语法：

ST_Centroid(geometry g1);

示例：

-- 计算多边形的中心点
SELECT ST_Centroid(
  ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')
);
-- 输出：POINT(5 5)

-- 获取每个行政区的中心点坐标
SELECT
name,
  ST_X(ST_Centroid(geom)) AS center_lng,
  ST_Y(ST_Centroid(geom)) AS center_lat
FROM districts;

-- 注意：质心不一定在几何内部（如U型多边形）
-- 若需要保证点在内部，使用 ST_PointOnSurface

7. ST_PointOnSurface — 表面内点

功能说明：返回保证位于几何表面（内部）的一个点，比质心更可靠（一定在多边形内）。

语法：

ST_PointOnSurface(geometry g1);

示例：

-- 生成多边形的标签点（保证在内部）
SELECTname, ST_PointOnSurface(geom) AS label_point
FROM admin_boundaries;

8. ST_Simplify — 几何简化

功能说明：使用 Douglas-Peucker 算法简化几何顶点，减少数据量，提升渲染和查询性能。

语法：

ST_Simplify(geometry geomA, float tolerance);
ST_Simplify(geometry geomA, float tolerance, boolean preserveCollapsed);

示例：

-- 简化多边形（容差 10 米）
SELECT ST_Simplify(geom, 10) AS simplified_geom
FROM complex_polygons;

-- 不同简化程度对比
SELECT
  ST_NumPoints(geom) AS original_points,
  ST_NumPoints(ST_Simplify(geom, 10)) AS simplified_10m,
  ST_NumPoints(ST_Simplify(geom, 50)) AS simplified_50m
FROM national_boundary;
“

ST_Simplify 可能导致拓扑错误（如多边形自交）。若需保留拓扑正确性，使用 ST_SimplifyPreserveTopology。

9. ST_ConvexHull — 凸包

功能说明：计算包含所有输入几何的最小凸多边形，常用于生成点集的外包范围。

语法：

ST_ConvexHull(geometry geomA);

示例：

-- 计算多点的凸包
SELECT ST_ConvexHull(
  ST_GeomFromText('MULTIPOINT(0 0, 10 0, 10 10, 0 10, 5 5)')
);

-- 计算一组 POI 的覆盖范围
SELECT ST_ConvexHull(ST_Collect(geom)) AS coverage_hull
FROM pois
WHEREcategory = '学校';

10. ST_Collect — 收集几何

功能说明：将多个几何收集为一个 Multi 几何或 GeometryCollection，不进行合并运算。

语法：

ST_Collect(geometry g1, geometry g2);
ST_Collect(geometry[] g1_array);
-- 聚合形式
ST_Collect(geometry geom);

示例：

-- 两个点合成多点
SELECT ST_Collect(ST_MakePoint(0, 0), ST_MakePoint(1, 1));

-- 聚合成多点几何
SELECT ST_Collect(geom) AS all_points
FROM pois WHEREtype = 'restaurant';

测量计算函数

1. ST_Distance — 计算距离

功能说明：计算两个几何对象之间的最短距离。geometry 类型返回坐标系单位，geography 类型返回米。

语法：

ST_Distance(geometry g1, geometry g2);
ST_Distance(geography gg1, geography gg2, boolean use_spheroid=true);

示例：

-- 平面距离（单位：度，不推荐）
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326),
  ST_SetSRID(ST_MakePoint(116.5, 39.9), 4326)
);

-- 地理距离（geography 类型，单位：米）
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)::geography,
  ST_SetSRID(ST_MakePoint(121.47, 31.23), 4326)::geography
) / 1000AS distance_km;
-- 北京到上海约 1068 公里

-- 查询距离某点最近的 5 个 POI
SELECTname, ST_Distance(geog, ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)::geography) AS dist
FROM pois
ORDERBY dist
LIMIT5;

2. ST_Area — 计算面积

功能说明：计算多边形的面积。geometry 类型返回坐标系单位的平方，geography 类型返回平方米。

语法：

ST_Area(geometry g1);
ST_Area(geography geog, boolean use_spheroid=true);

示例：

-- 计算简单矩形面积（平面坐标）
SELECT ST_Area(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));
-- 输出：100

-- 地理坐标计算真实面积（平方米）
SELECT ST_Area(
  ST_GeomFromText('POLYGON((116.0 39.0, 117.0 39.0, 117.0 40.0, 116.0 40.0, 116.0 39.0))', 4326)::geography
) AS area_sqm;

-- 转换为平方公里
SELECTname, ST_Area(geom::geography) / 1000000AS area_km2
FROM districts
ORDERBY area_km2 DESC;

3. ST_Length — 计算长度

功能说明：计算线串的长度。geometry 类型返回坐标系单位，geography 类型返回米。

语法：

ST_Length(geometry a_2dlinestring);
ST_Length(geography geog, boolean use_spheroid=true);

示例：

-- 计算线段长度
SELECT ST_Length(ST_GeomFromText('LINESTRING(0 0, 10 0, 10 10)'));
-- 输出：20

-- 计算道路的实际长度（公里）
SELECT
  road_name,
  ST_Length(geom::geography) / 1000AS length_km
FROM roads
WHERE road_name = '长安街';

-- 计算所有道路总长度
SELECTSUM(ST_Length(geom::geography)) / 1000AS total_km
FROM roads;

4. ST_Perimeter — 计算周长

功能说明：计算多边形的周长。

语法：

ST_Perimeter(geometry g1);
ST_Perimeter(geography geog, boolean use_spheroid=true);

示例：

-- 计算多边形周长
SELECT ST_Perimeter(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));
-- 输出：40

-- 计算行政区边界周长
SELECTname, ST_Perimeter(geom::geography) / 1000AS perimeter_km
FROM provinces;

5. ST_DistanceSphere — 球面距离

功能说明：基于球面模型计算两点间的大圆距离，速度快但精度略低。

语法：

ST_DistanceSphere(geometry geomlonlatA, geometry geomlonlatB);

示例：

-- 北京到上海的球面距离（米）
SELECT ST_DistanceSphere(
  ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326),
  ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)
);

空间参考系统函数

1. ST_Transform — 坐标转换

功能说明：将几何对象从一个坐标系转换到另一个坐标系，是多源数据融合的必备函数。

语法：

ST_Transform(geometry g1, integer srid);

常用 SRID：

  • 4326：WGS84 经纬度（GPS 原始坐标）
  • 3857：Web 墨卡托投影（Google Maps、高德、百度 Web 端）
  • 4490：CGCS2000 国家大地坐标系

示例：

-- WGS84 转 Web 墨卡托
SELECT ST_Transform(
  ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326),
3857
);

-- 批量转换表中数据的坐标系
UPDATE my_table 
SET geom_3857 = ST_Transform(geom_4326, 3857);

-- WGS84 转 CGCS2000（基本一致，仅基准面微调）
SELECT ST_Transform(geom, 4490) FROM spatial_data;

2. ST_SetSRID — 设置坐标系

功能说明：为几何对象设置 SRID（空间参考标识符），不会进行坐标转换，只是声明坐标系。

语法：

ST_SetSRID(geometry geom, integer srid);

示例：

-- 为原始坐标点设置 WGS84 坐标系
SELECT ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326);

-- 修复丢失 SRID 的数据
UPDATE broken_table SET geom = ST_SetSRID(geom, 4326) WHERE ST_SRID(geom) = 0;

3. ST_SRID — 获取坐标系

功能说明：返回几何对象的 SRID 编号。

语法：

ST_SRID(geometry g1);

示例：

-- 查看几何的坐标系
SELECT ST_SRID(geom) FROM my_table LIMIT1;

-- 检查 SRID 是否正确
SELECTCOUNT(*) FROM spatial_data WHERE ST_SRID(geom) != 4326;

4. UpdateGeometrySRID — 更新整列 SRID

功能说明：批量更新表中几何列的 SRID，并更新系统目录中的元数据。

语法：

UpdateGeometrySRID(varchar table_name, varchar column_name, integer srid);
UpdateGeometrySRID(varchar schema_name, varchar table_name, varchar column_name, integer srid);

示例：

-- 更新表的几何列 SRID（仅修改元数据，不转换坐标值）
SELECT UpdateGeometrySRID('public', 'my_table', 'geom', 4326);

高级空间分析函数

1. ST_ClusterDBSCAN — 密度聚类

功能说明：基于 DBSCAN 算法对空间数据进行密度聚类，常用于热点分析、POI 聚类。

语法：

ST_ClusterDBSCAN(geometry geom, float8 eps, integer minpoints);

示例：

-- 对 POI 进行 DBSCAN 聚类（距离阈值 500 米，最少 5 个点成簇）
SELECT
name,
  ST_ClusterDBSCAN(geom, eps := 500, minpoints := 5) OVER () AScluster_id
FROM pois;

-- 统计每个聚类的 POI 数量
WITH clustered AS (
SELECT
    ST_ClusterDBSCAN(geom, eps := 1000, minpoints := 3) OVER () AS cid,
    geom
FROM restaurants
)
SELECT cid, COUNT(*) AS poi_count, ST_Collect(geom) AS cluster_geom
FROM clustered
WHERE cid ISNOTNULL
GROUPBY cid
ORDERBY poi_count DESC;

2. ST_ClusterKMeans — K-Means 聚类

功能说明：基于 K-Means 算法将几何分为指定数量的簇。

语法：

ST_ClusterKMeans(geometry geom, integer number_of_clusters);

示例：

-- 将所有学校分为 5 个聚类
SELECT
name,
  ST_ClusterKMeans(geom, 5) OVER () AScluster_id
FROM schools;

3. ST_SnapToGrid — 吸附到网格

功能说明：将几何顶点吸附到指定大小的网格上，用于数据精度归一化或去重。

语法：

ST_SnapToGrid(geometry geomA, float size);

示例：

-- 坐标精度保留到 0.001 度
SELECT ST_SnapToGrid(geom, 0.001) FROM points;

-- 去重：吸附到网格后分组
SELECT ST_SnapToGrid(geom, 0.0001) AS geom, COUNT(*)
FROM pois
GROUPBY ST_SnapToGrid(geom, 0.0001)
HAVINGCOUNT(*) > 1;

4. ST_MakeValid — 修复无效几何

功能说明：修复无效的几何对象（如自交多边形、开口环等），数据清洗必备。

语法：

ST_MakeValid(geometry input);

示例：

-- 修复无效几何
UPDATE messy_data SET geom = ST_MakeValid(geom) WHERENOT ST_IsValid(geom);

-- 检查并修复
SELECT
COUNT(*) AS total,
SUM(CASEWHEN ST_IsValid(geom) THEN1ELSE0END) AS valid_count,
SUM(CASEWHENNOT ST_IsValid(geom) THEN1ELSE0END) AS invalid_count
FROM polygons;

5. ST_IsValid — 验证几何有效性

功能说明：判断几何是否符合 OGC 规范（拓扑有效）。

语法：

ST_IsValid(geometry g1);

示例：

-- 查找所有无效几何
SELECTid, ST_IsValidReason(geom) AS invalid_reason
FROM polygons
WHERENOT ST_IsValid(geom);

6. ST_Extent — 计算边界框

功能说明：计算一组几何的外包矩形（边界框），返回 box2d 类型。

语法：

ST_Extent(geometry geomfield);

示例：

-- 计算数据集的整体范围
SELECT ST_Extent(geom) AS total_bbox FROM cities;

-- 获取某类 POI 的边界范围
SELECT ST_Extent(geom) FROM pois WHEREcategory = 'hospital';

应用案例

案例 1：周边查询——查找 3 公里内的餐厅

-- 假设用户位置为 (116.4, 39.9)
SELECT
name,
  address,
ROUND(ST_Distance(
    geog,
    ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)::geography
  )) AS distance_m
FROM restaurants
WHERE ST_DWithin(
  geog,
  ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)::geography,
3000-- 3 公里
)
ORDERBY distance_m
LIMIT20;

案例 2：服务区分析——医院 1 公里覆盖人口

SELECT
  h.name AS hospital_name,
SUM(p.population) AS covered_population
FROM hospitals h
JOIN communities p 
ON ST_Intersects(p.geom, ST_Buffer(h.geom, 1000))
WHERE h.level = '三级甲等'
GROUPBY h.name
ORDERBY covered_population DESC;

案例 3：数据裁剪——提取行政区内的道路网

-- 按行政区裁剪道路数据
SELECT
  r.road_id,
  r.name,
  ST_Intersection(r.geom, d.geom) AS clipped_geom,
  ST_Length(ST_Intersection(r.geom, d.geom)::geography) AS length_m
FROM roads r
JOIN districts d ON ST_Intersects(r.geom, d.geom)
WHERE d.name = '海淀区';

案例 4：空间聚合——统计每个区县的 POI 数量

SELECT
  d.name AS district_name,
COUNT(p.id) AS poi_count
FROM districts d
LEFTJOIN pois p ON ST_Contains(d.geom, p.geom)
GROUPBY d.name
ORDERBY poi_count DESC;

案例 5：最近邻查询——找最近的地铁站

-- 为每个小区找最近的地铁站
SELECTDISTINCTON (c.id)
  c.name AS community,
  s.name AS nearest_station,
ROUND(ST_Distance(c.geog, s.geog)) AS distance_m
FROM communities c
CROSSJOIN subway_stations s
ORDERBY c.id, ST_Distance(c.geog, s.geog);