如何在SQL中实现按照地理位置半径进行分组聚合_利用空间数据库函数ST_Distance

在地理信息相关的业务开发中，经常需要统计某个中心点不同半径范围内的数据聚合结果，比如统计某门店1公里、3公里、5公里范围内的订单数量，或者统计不同半径圈内的用户分布。这种需求无法用普通SQL的标量计算实现，需要借助空间数据库提供的空间函数完成。

前置准备

首先需要确保使用的数据库支持空间扩展，常见的支持空间函数的数据库包括PostgreSQL（配合PostGIS扩展）、MySQL（5.7及以上版本的空间功能）、SQL Server等。本文以PostgreSQL+PostGIS为例进行说明，其他数据库的函数名称可能略有差异，但核心逻辑一致。

假设我们有一张用户位置表user_location，表结构如下：

CREATE TABLE user_location (
    id INT PRIMARY KEY,
    user_name VARCHAR(50),
    -- 存储用户位置的几何字段，类型为POINT，坐标系使用WGS84（SRID=4326）
    location GEOMETRY(Point, 4326),
    -- 记录用户位置的经度和纬度，方便后续单独使用
    lng NUMERIC(10, 6),
    lat NUMERIC(10, 6)
);

ST_Distance函数基础用法

ST_Distance是空间数据库中用于计算两个几何对象之间距离的函数，对于地理坐标系（如WGS84）的点位，计算的是球面距离，单位为米。基本语法如下：

-- 计算两个POINT点位之间的距离，返回单位为米
ST_Distance(geom1, geom2)

如果要指定中心点和半径进行过滤，可以结合ST_DWithin函数（用于判断两个几何对象之间的距离是否在指定范围内），不过本文重点是分组聚合，所以主要使用ST_Distance计算距离后按范围分组。

按固定半径分组聚合实现

假设我们需要统计以坐标(116.397128, 39.916527)为中心点，1公里、3公里、5公里半径范围内的用户数量，步骤如下：

第一步：计算每个用户到中心点的距离

首先需要将中心点转换为和表中location字段一致的空间类型，然后计算每个用户位置到中心点的距离：

-- 将中心点坐标转换为POINT类型，SRID和表中字段保持一致
SELECT 
    id,
    user_name,
    -- 计算用户位置到中心点的距离，单位为米
    ST_Distance(
        location,
        ST_SetSRID(ST_MakePoint(116.397128, 39.916527), 4326)
    ) AS distance_m
FROM user_location;

第二步：按半径范围分组统计

使用CASE WHEN语句将距离划分到不同的半径区间，再进行分组聚合：

SELECT 
    -- 定义半径分组标签
    CASE 
        WHEN distance_m <= 1000 THEN '1公里内'
        WHEN distance_m <= 3000 THEN '1-3公里'
        WHEN distance_m <= 5000 THEN '3-5公里'
        ELSE '5公里外'
    END AS radius_range,
    COUNT(*) AS user_count,
    -- 可选：计算该分组内的平均距离
    AVG(distance_m) AS avg_distance
FROM (
    -- 子查询计算每个用户到中心点的距离
    SELECT 
        id,
        ST_Distance(
            location,
            ST_SetSRID(ST_MakePoint(116.397128, 39.916527), 4326)
        ) AS distance_m
    FROM user_location
) AS user_distance
GROUP BY radius_range
ORDER BY 
    -- 按半径从小到大排序
    CASE radius_range
        WHEN '1公里内' THEN 1
        WHEN '1-3公里' THEN 2
        WHEN '3-5公里' THEN 3
        ELSE 4
    END;

动态半径分组实现

如果半径分组不是固定的，而是需要动态传入，比如业务需要统计1km、2km、5km、10km多个自定义半径的结果，可以将半径配置放到临时表或者数组中，再进行关联计算：

-- 定义动态半径数组，按从小到大排序
WITH radius_config AS (
    SELECT unnest(ARRAY[1000, 2000, 5000, 10000]) AS radius
)
SELECT 
    rc.radius AS radius_meter,
    COUNT(ul.id) AS user_count
FROM radius_config rc
-- 左连接用户表，匹配距离小于等于当前半径的用户
LEFT JOIN user_location ul 
    ON ST_Distance(
        ul.location,
        ST_SetSRID(ST_MakePoint(116.397128, 39.916527), 4326)
    ) <= rc.radius
GROUP BY rc.radius
ORDER BY rc.radius;

注意事项

• 坐标系选择：如果使用地理坐标系（SRID=4326），ST_Distance返回的是球面距离，精度更高；如果使用投影坐标系（如Web Mercator，SRID=3857），计算的是平面距离，会存在一定误差，建议优先使用地理坐标系。
• 性能优化：如果表数据量较大，建议在location字段上创建空间索引，可以大幅提升距离计算的查询效率：

  CREATE INDEX idx_user_location_geom ON user_location USING GIST (location);
  

• 函数差异：不同数据库的ST_Distance函数用法略有不同，比如MySQL中需要先将坐标转换为几何对象，且距离计算默认是平面距离，若要计算球面距离需要使用ST_Distance_Sphere函数。

其他数据库适配示例

MySQL实现示例

MySQL 8.0及以上版本的空间函数用法如下：

-- MySQL中计算球面距离使用ST_Distance_Sphere，默认返回单位为米
SELECT 
    CASE 
        WHEN distance <= 1000 THEN '1公里内'
        WHEN distance <= 3000 THEN '1-3公里'
        WHEN distance <= 5000 THEN '3-5公里'
        ELSE '5公里外'
    END AS radius_range,
    COUNT(*) AS user_count
FROM (
    SELECT 
        id,
        -- ST_Distance_Sphere计算两个球面点位的距离
        ST_Distance_Sphere(
            location,
            ST_GeomFromText('POINT(116.397128 39.916527)', 4326)
        ) AS distance
    FROM user_location
) AS t
GROUP BY radius_range;

SQL Server实现示例

SELECT 
    CASE 
        WHEN distance <= 1000 THEN '1公里内'
        WHEN distance <= 3000 THEN '1-3公里'
        WHEN distance <= 5000 THEN '3-5公里'
        ELSE '5公里外'
    END AS radius_range,
    COUNT(*) AS user_count
FROM (
    SELECT 
        id,
        -- SQL Server中计算地理距离使用STDistance方法
        location.STDistance(geography::Point(39.916527, 116.397128, 4326)) AS distance
    FROM user_location
) AS t
GROUP BY radius_range;

SQLST_Distance空间数据库地理位置分组半径聚合修改时间：2026-06-15 03:24:16