如何在SQL中对经纬度地理栅格数据进行聚合统计-SQL-PHP中文网

直接GROUP BY经纬度字段失效，因高精度浮点值几乎无重复，无法实现栅格聚合；需先将连续空间离散为固定大小网格（如0.01°或1km），再按网格ID分组统计。

如何在sql中对经纬度地理栅格数据进行聚合统计

为什么直接 GROUP BY 经纬度字段会失效

因为原始 latitude 和 longitude 通常是高精度浮点数（如 39.9042、116.4074），直接 GROUP BY 几乎不会产生重复值，统计结果每行一条，完全达不到“栅格聚合”目的。真正需要的是把连续空间离散成固定大小的网格单元（比如 0.01° × 0.01° 或 1km × 1km），再按单元计数。

用 ROUND() 实现等距经纬度栅格化（最轻量方案）

适用于中小数据量、对地理精度要求不苛刻（如城市级热力图初筛）、数据库无空间扩展的场景。核心是把经纬度分别四舍五入到指定小数位，生成可分组的栅格 ID。

ROUND(latitude, 2) 对应约 ±0.55km 纬向误差（赤道附近），ROUND(longitude, 2) 经向误差随纬度升高而缩小
组合成复合键更稳妥：CONCAT(ROUND(latitude, 2), '_', ROUND(longitude, 2)) AS grid_id

SQL 示例：

SELECT 
  CONCAT(ROUND(lat, 2), '_', ROUND(lng, 2)) AS grid_id,
  COUNT(*) AS cnt
FROM locations 
GROUP BY ROUND(lat, 2), ROUND(lng, 2)
ORDER BY cnt DESC
LIMIT 10;

注意：ROUND 在不同数据库行为一致，但 PostgreSQL 需显式转换类型，如 ROUND(lat::numeric, 2)

用 ST_SnapToGrid() 或自定义函数做投影后栅格（高精度必需）

当需要严格等距（如 1km 正方形）、跨纬度带保持面积一致，或后续要叠加 GIS 图层时，必须先将 WGS84（EPSG:4326）坐标转为平面投影（如 Web Mercator EPSG:3857），再栅格化。PostgreSQL/PostGIS 支持 ST_SnapToGrid()，MySQL 8.0+ 可用 ST_PointFromText() + 自定义计算。

PostGIS 示例（转 Web Mercator 后按 1000 米栅格）：

SELECT 
  ST_SnapToGrid(
    ST_Transform(ST_SetSRID(ST_MakePoint(lng, lat), 4326), 3857), 
    1000
  ) AS grid_geom,
  COUNT(*) AS cnt
FROM locations 
GROUP BY grid_geom;

该方式生成的是几何对象，不是字符串 ID，可直接用于空间连接或导出为 GeoJSON
性能开销明显高于 ROUND：每次需坐标转换 + 几何运算，百万级数据建议提前建 GENERATED COLUMN 缓存栅格几何
MySQL 不支持 ST_SnapToGrid()，需手动算米制坐标：FLOOR((lng * cos(radians(lat)) * 111320) / 1000) 等，易出错且不推荐

聚合结果导出为 GeoJSON 栅格面时的关键陷阱

很多用户想把聚合结果画成热力图或 choropleth，直接用点聚合结果去渲染，导致边缘模糊或漏格。真正可用的栅格面必须由每个 grid_id 扩展出实际多边形边界。

用 ROUND 方案时，必须反推边界：每个 (lat_rnd, lng_rnd) 对应的矩形是 [lat_rnd - 0.005, lat_rnd + 0.005) × [lng_rnd - 0.005, lng_rnd + 0.005)
PostGIS 中可用 ST_MakeEnvelope(lng_min, lat_min, lng_max, lat_max, 4326) 构造面，再用 ST_AsGeoJSON() 输出
忽略 WGS84 坐标系下“经度间隔随纬度压缩”的事实，会导致高纬度地区栅格被拉长——若要求视觉均匀，必须用等面积投影（如 EPSG:3035）再栅格化
最终输出前务必检查 ST_IsValid()，尤其当栅格跨越国际日期变更线或极点时，ST_MakeEnvelope 可能生成无效几何