SQL实现半英里范围内站点合并后的唯一数量统计咨询

核心需求是对经纬度站点做地理空间聚类，把半英里（约804.67米）范围内的站点归为同一组，最终按组统计数量。以下是具体实现思路和示例：

1. 明确距离计算方式 #

因为是经纬度坐标，必须用球面距离公式（Haversine）或数据库自带的地理空间函数计算真实地表距离，不能直接用平面坐标的距离公式。

2. 实现聚类分组 #

方法一：递归CTE聚类（通用SQL方案，支持MySQL 8+、PostgreSQL、SQL Server） #

先基于你的原查询获取目标站点数据集，再通过递归把相邻半英里内的站点归为同一组：

-- 第一步：获取目标客户的有效站点数据集
WITH site_data AS (
    SELECT 
        s.id AS site_id,
        s.name,
        s.center_lat,
        s.center_lng,
        ROW_NUMBER() OVER () AS rn
    FROM images i
    INNER JOIN sites s ON s.id = i.site_id
    INNER JOIN customers c ON c.id = s.customer_id
    WHERE i.hidden = 'false' 
      AND i.copied_from_id IS NULL 
      AND i.status = 'complete' 
      AND c.id = '353'
    GROUP BY s.id, s.name, s.center_lat, s.center_lng
),
-- 递归CTE分组
clusters AS (
    -- 初始化：每个站点先作为单独组
    SELECT site_id, center_lat, center_lng, rn AS cluster_id
    FROM site_data
    WHERE rn = 1
    UNION ALL
    -- 递归：将距离现有组内站点半英里内的站点并入该组
    SELECT 
        sd.site_id,
        sd.center_lat,
        sd.center_lng,
        c.cluster_id
    FROM site_data sd
    JOIN clusters c 
        ON ST_Distance_Sphere(
            POINT(sd.center_lng, sd.center_lat),
            POINT(c.center_lng, c.center_lat)
        ) <= 804.67 -- 半英里≈804.67米
    WHERE sd.site_id NOT IN (SELECT site_id FROM clusters)
)
-- 最终统计：按聚类分组，取组内任意站点作为代表，统计组内站点数
SELECT 
    MIN(sd.name) AS group_name,
    MIN(sd.center_lat) AS group_lat,
    MIN(sd.center_lng) AS group_lng,
    COUNT(DISTINCT sd.site_id) AS site_count_in_group
FROM site_data sd
JOIN clusters c ON sd.site_id = c.site_id
GROUP BY c.cluster_id;

方法二：用数据库地理扩展（PostGIS为例，更高效） #

如果你的数据库支持PostGIS，可以直接用专门的聚类函数，代码更简洁：

WITH site_data AS (
    SELECT 
        s.id AS site_id,
        s.name,
        ST_SetSRID(ST_MakePoint(s.center_lng, s.center_lat), 4326) AS geom
    FROM images i
    INNER JOIN sites s ON s.id = i.site_id
    INNER JOIN customers c ON c.id = s.customer_id
    WHERE i.hidden = 'false' 
      AND i.copied_from_id IS NULL 
      AND i.status = 'complete' 
      AND c.id = '353'
    GROUP BY s.id, s.name, s.center_lat, s.center_lng
)
SELECT 
    ST_X(ST_Centroid(ST_Collect(geom))) AS group_lng,
    ST_Y(ST_Centroid(ST_Collect(geom))) AS group_lat,
    COUNT(site_id) AS site_count_in_group
FROM site_data
GROUP BY ST_ClusterDBSCAN(geom, 0, 804.67); -- 0代表每个组至少1个点，804.67是半英里的米数

3. 关键说明 #

• 不同数据库的地理函数语法有差异：比如MySQL用ST_Distance_Sphere，PostgreSQL（PostGIS）用ST_Distance，SQL Server用STDistance，需要根据你的数据库调整。
• 半英里转米的数值是近似值，可根据需求精确调整。
• 如果站点数量较多，递归CTE的性能可能不如专门的地理聚类函数，优先用数据库自带的空间扩展功能。

内容的提问来源于stack exchange，提问作者Patrick