SQL Server如何统计指定半径范围内的事故数量并识别事故热点区域

基础实现方案（兼容所有支持空间类型的SQL Server版本） #

WITH AccidentGeopoints AS (
    SELECT 
        id,
        Latitude,
        Longitude,
        geography::Point(Latitude, Longitude, 4326) AS geopoint
    FROM accidents
    WHERE 
        -- 过滤无效坐标
        Latitude IS NOT NULL 
        AND Longitude IS NOT NULL
        -- 替换为你需要的选定日期范围
        AND accidentDate >= '2024-01-01' 
        AND accidentDate < '2024-02-01'
)
SELECT TOP 100
    -- 格式化输出要求的经纬度格式，可自行调整小数位数
    CONCAT(STR(a.Latitude, 6, 4), ' , ', STR(a.Longitude, 7, 5)) AS [geopoint (lat,lng)],
    COUNT(b.id) AS [事故数量]
FROM AccidentGeopoints a
INNER JOIN AccidentGeopoints b
    -- 匹配距离小于等于500米的所有事故点
    ON a.geopoint.STDistance(b.geopoint) <= 500
GROUP BY a.Latitude, a.Longitude
ORDER BY [事故数量] DESC, a.Latitude, a.Longitude

超大数据集优化说明 #

• 提前给表创建空间索引，查询效率可提升10~100倍，创建语句如下：

CREATE SPATIAL INDEX IX_Accidents_Geopoint 
ON accidents(geography::Point(Latitude, Longitude, 4326))
USING GEOGRAPHY_GRID
WITH (
    GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM),
    CELLS_PER_OBJECT = 16
);

• SQL Server 2017及以上版本可使用STCluster聚类函数直接聚合相近事故点，输出结果无冗余热点，代码如下：

WITH AccidentGeopoints AS (
    SELECT geography::Point(Latitude, Longitude, 4326) AS geopoint
    FROM accidents
    WHERE 
        Latitude IS NOT NULL 
        AND Longitude IS NOT NULL
        AND accidentDate >= '2024-01-01' 
        AND accidentDate < '2024-02-01'
)
SELECT
    CONCAT(STR(cluster.STCentroid().Lat, 6, 4), ' , ', STR(cluster.STCentroid().Long, 7, 5)) AS [geopoint (lat,lng)],
    cluster.STNumGeometries() AS [事故数量]
FROM (
    SELECT geopoint.STCluster(500) AS cluster
    FROM AccidentGeopoints
) t
ORDER BY [事故数量] DESC

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