SQL如何实现按经纬度坐标进行聚类统计_利用ROUND函数网格化-SQL-PHP中文网

ROUND经纬度网格化是轻量可控方案，但因WGS84球面非线性变形（纬度1°≈111km恒定，经度1°从赤道111km缩至60°N约55km），直接ROUND(经, N)+ROUND(纬, N)导致高纬格网东西向压缩、边界错分；需统一转DECIMAL(9,6)、加偏移防负数截断、字符串拼接组合键，并用HAVING过滤孤立点；PostgreSQL推荐ST_SnapToGrid（支持geography转米级精度）以保障空间语义一致性。

sql如何实现按经纬度坐标进行聚类统计_利用round函数网格化

直接用 ROUND 对经纬度做网格化聚类，是轻量、可控、兼容性最强的方案，但容易因精度丢失或坐标偏移导致边界点错分——关键不在函数本身，而在如何设格网步长和处理 WGS84 坐标系下的非线性变形。

为什么 ROUND(经度, N) + ROUND(纬度, N) 不等于真实地理聚类

地球表面是球面，WGS84 下 1° 纬度 ≈ 111km，但 1° 经度在赤道约 111km，在北纬 60° 仅约 55km。直接对原始度数四舍五入，等效于在“经纬度平面”上画正方形格网，实际覆盖地表是不规则梯形带，越往高纬，东西向压缩越严重。

实操建议：

若业务容忍误差（如城市级热力图、POI 粗略密度展示），可用 ROUND(longitude, 3) + ROUND(latitude, 3)，对应约 ±500m 网格（赤道附近）
避免用 ROUND(longitude, 2) —— 步长 0.01° 在北京≈1.1km，在哈尔滨≈0.7km，同一格子南北跨度差异大
必须统一先转为 DECIMAL(9,6) 类型再 ROUND，防止浮点误差（如 116.30000000000001 被误判为不同格子）
不要依赖 GROUP BY ROUND(lat,3), ROUND(lng,3) 直接出结果，需补 HAVING COUNT(*) > 1 过滤掉孤立点

MySQL 中实现稳定网格聚合的写法

常见错误是直接 SELECT ROUND(lat,3), ROUND(lng,3), COUNT(*) FROM points GROUP BY ROUND(lat,3), ROUND(lng,3)，这在有重复坐标的脏数据下会漏计；更糟的是，MySQL 8.0+ 对 ROUND 的隐式类型转换可能让 DECIMAL 和 FLOAT 混合时产生不可控截断。

正确做法：

显式 CAST：用 ROUND(CAST(latitude AS DECIMAL(9,6)), 3) 避免隐式转换
加偏移防负数截断：ROUND((latitude + 90) * 1000) / 1000 - 90 可规避 -0.0005 四舍五入成 0.000 的边界问题
组合键用字符串拼接更安全：CONCAT(ROUND(lat,3), ',', ROUND(lng,3))，避免浮点比较歧义

示例（统计每 0.001° 网格内 POI 数）：

SELECT
  CONCAT(ROUND(CAST(latitude AS DECIMAL(9,6)), 3), ',', ROUND(CAST(longitude AS DECIMAL(9,6)), 3)) AS grid_key,
  COUNT(*) AS poi_count
FROM pois
WHERE latitude IS NOT NULL AND longitude IS NOT NULL
GROUP BY grid_key
HAVING poi_count >= 2
ORDER BY poi_count DESC
LIMIT 100;

PostgreSQL 里用 ST_SnapToGrid 更靠谱

PostGIS 提供 ST_SnapToGrid，它基于几何对象做空间对齐，比纯数值 ROUND 更符合地理语义——它能保证同一格网内的所有点被 snap 到同一个 POINT，后续可直接用 ST_Union 合并几何或关联行政区划。

注意点：

ST_SnapToGrid(geom, 0.001) 中的 0.001 单位仍是度，不是米；要转米级格网，得先转 geography：ST_SnapToGrid(geom::geography, 100)::geometry
必须确保输入 geom SRID=4326，否则 ST_SnapToGrid 行为未定义
性能上，ST_SnapToGrid 比纯数值计算慢 2–3 倍，但结果可参与空间索引和 ST_Contains 关联，扩展性强

示例（生成 500 米格网 ID 并统计）：

SELECT
  ST_SnapToGrid(geom::geography, 500)::geometry AS grid_geom,
  COUNT(*) AS cnt
FROM locations
WHERE geom IS NOT NULL
GROUP BY grid_geom
ORDER BY cnt DESC
LIMIT 50;

ROUND 网格化本质是降维取近似，它快、简单、到处都能跑，但你得清楚自己丢掉了多少地理保真度——尤其当数据跨纬度带（如从广州到哈尔滨）或需要对接 GIS 工具时，那个看似整齐的 ROUND(lat,3) 字符串，可能在地图渲染里裂开成两块不连续的区域。