SQL如何按地理区域分组统计_关联经纬度范围与维度表-SQL-PHP中文网

需用空间函数（如st_within）将经纬度转点后匹配多边形行政区，而非简单四舍五入；须确保坐标系一致、维度表含空间索引及合适颗粒度，否则会导致漏匹配或性能差。

sql如何按地理区域分组统计_关联经纬度范围与维度表

核心是把原始数据里的 lat 和 lng 落到某个省/市/区的多边形范围内，不是简单四舍五入或取整。直接用 BETWEEN 做矩形框粗筛可以，但会漏掉边界弯曲区域（比如长三角某镇实际属江苏，却落在上海矩形框外）。

实操建议：

先确保维度表（如 dim_region）有 geom 字段，类型为 POLYGON 或 GEOMETRY，且已建立空间索引（PostGIS 用 CREATE INDEX ON dim_region USING GIST(geom)）
原始表需转成点：PostGIS 中用 ST_Point(lng, lat)（注意顺序是经度在前），再用 ST_Within(point, region.geom) 判断是否在内
MySQL 8.0+ 可用 ST_Contains(region.geom, ST_Point(lng, lat))，但性能弱于 PostGIS，且必须确保 geom 是 SRID 4326
如果没空间扩展，退而求其次：用最小外接矩形（MBR）预过滤——ST_Contains(ST_Envelope(region.geom), ST_Point(lng, lat))，再加业务规则兜底（如按城市中心点距离 fallback）

常见现象是：原始记录 10 万条，分组后 sum(count) 只有 9.2 万。根本原因不是 SQL 写错，而是坐标落在所有已知行政区划范围之外（海洋、边境无人区、坐标系不一致、或维度表本身缺失某些飞地）。

排查步骤：

先查未匹配上的点：SELECT lat, lng FROM raw_data d LEFT JOIN dim_region r ON ST_Within(ST_Point(d.lng, d.lat), r.geom) WHERE r.id IS NULL LIMIT 100
检查坐标系：原始数据若来自高德/百度，是 GCJ-02 或 BD-09，必须先纠偏转 WGS84（用对应 SDK 或开源库如 coordtransform），否则 ST_Point 构造的点位置偏差可达 500 米
维度表若只含地级市，但你要统计到区县，就必然有“市辖区”未覆盖的街道数据——得确认维度表颗粒度是否匹配业务需求

空间 JOIN 天然比普通 JOIN 慢，尤其当原始表千万级、区域表上千个面时，没优化很容易 10 分钟不出结果。

关键优化点：

强制走空间索引：在 PostGIS 中，确保 ST_Within 的第二个参数是索引字段（即 r.geom），不要写成 ST_Within(r.geom, ST_Point(...))，否则索引失效
加地理围栏预过滤：先用 ST_DWithin(ST_Transform(ST_Point(lng, lat), 4326), ST_Transform(r.center_point, 4326), 100000) 快速筛出 100km 内的候选区域，再精确判断
避免 SELECT *：只 SELECT 需要的区域字段（如 r.province_name, r.city_code），别把整个 geom 拉出来
小表驱动大表：把 dim_region 当驱动表（FROM dim_region r），用 LATERAL 关联原始数据，配合 ST_Intersects + 索引，有时比大表驱动快一个数量级

只能放弃精确多边形匹配，改用「行政中心点 + 距离阈值」近似法，适合对精度要求不高的运营报表。

操作方式：

维度表增加 center_lat、center_lng、radius_km 三列（半径可按地级市平均面积估算）
用 Haversine 公式算球面距离：SELECT *, 6371 * ACOS(LEAST(1.0, COS(RADIANS(lat)) * COS(RADIANS(center_lat)) * COS(RADIANS(lng - center_lng)) + SIN(RADIANS(lat)) * SIN(RADIANS(center_lat)))) AS distance_km FROM raw_data d JOIN dim_region r ON distance_km
缺点明显：圆形覆盖无法处理狭长区域（如甘肃走廊），且无索引，大数据量需加 lat BETWEEN ... AND ... 和 lng BETWEEN ... AND ... 先粗筛

真正难的不是写那几行 SQL，而是确认每一条坐标属于哪个“真实世界里的行政主体”——这背后涉及坐标系、行政区划变更、飞地归属、甚至国界争议。上线前务必抽样人工核验 50 条边界案例。