SQL如何实现基于地理位置的分组统计_空间数据库聚合函数应用-SQL-PHP中文网

PostGIS空间聚合需注意：ST_DWithin分组须显式指定锚点且SRID一致；行政区聚合需验证面有效性并处理NULL；ST_ClusterDBSCAN聚类需转geography调参；GeoJSON导出前须统一转WGS84。

sql如何实现基于地理位置的分组统计_空间数据库聚合函数应用

PostGIS中ST_DWithin做地理围栏分组统计

直接用 ST_DWithin 配合 GROUP BY 是最常用也最容易出错的方式。它不是“先算距离再分组”，而是以某个参考点（比如门店坐标）为圆心，把落在指定距离内的记录归为一组——但注意：这个“组”本身没有预定义的几何实体，必须显式提供锚点。

常见错误是写成 GROUP BY ST_DWithin(geom, ref_point, 1000)，这会报错，因为布尔值不能当分组键；正确做法是用子查询或 LATERAL 把每个目标点关联到最近/符合条件的锚点上。

必须确保两个参与比较的几何列 SRID 一致，否则 ST_DWithin 返回空结果（不报错！）
距离单位取决于 SRID：4326 下是度，不是米；要用 geography 类型才能直接输米（如 ST_DWithin(geom::geography, ref::geography, 5000)）
性能敏感时，务必在 geom 列上建 GIST 索引，否则全表扫描

按行政区域（省/市/区）做空间包含聚合

用 ST_Contains 或 ST_Intersects 关联点数据和面数据（如行政区划表），再统计每块面内有多少点、平均值等。关键在于：面表必须有有效拓扑且无自相交，否则 ST_Contains 可能漏判。

典型写法是 LEFT JOIN areas ON ST_Contains(areas.geom, points.geom)，但要注意 NULL 处理——落在区域外的点会被过滤掉，若需保留应改用 RIGHT JOIN 或补 UNION ALL 外部计数。

行政区面数据常来自公开 Shapefile，导入后建议运行 ST_IsValid(geom) 检查，无效面可用 ST_MakeValid 修复
ST_Contains 要求面完全包裹点，边界重合不算；需要含边界的用 ST_Intersects
大范围聚合（如全国 POI 按地级市统计）时，先用 ST_Envelope 做粗筛可提速 3–5 倍

使用ST_ClusterDBSCAN动态聚类热点区域

当没有预设区域划分，只想找出密度高的自然聚集区（比如外卖订单热力图），ST_ClusterDBSCAN 是唯一推荐的空间聚类函数。它按距离和最小点数自动分簇，返回每个点所属的簇 ID，之后就能按该 ID 分组统计。

它不依赖任何外部面表，纯靠点自身分布生成簇，适合探索性分析。但参数调优很关键：eps 过小导致碎片化，过大则全连成一片；minpoints 太低会把噪声当簇，太高则漏掉真实小集群。

输入必须是 geometry 类型，WGS84（SRID=4326）下 eps 单位是“度”，建议先转 geography 再用米制（如 ST_ClusterDBSCAN(geom::geography, eps := 500, minpoints := 10)）
结果中簇 ID 为 NULL 表示该点是噪声，不要忽略这个标记
该函数不可下推，大数据量时建议先用空间索引限制范围（如加 WHERE geom && ST_MakeEnvelope(...)）

聚合结果导出为GeoJSON时的坐标系陷阱

用 ST_AsGeoJSON 输出聚合后的面（如 ST_Union 合并的商圈）或点簇中心（ST_Centroid），默认输出 WGS84 经纬度。但如果原始数据是投影坐标系（如 Web Mercator EPSG:3857），直接转 GeoJSON 会导致坐标值巨大且无法在 Leaflet 等库中正常渲染。