关于MS Server中基于地理位置的表3数据插入脚本的技术问询

嘿，我来帮你完善这个脚本，同时给你推荐一个更高效的替代方案——毕竟游标在处理大量邮编数据时性能会比较拉胯，咱们先从你已经写了开头的游标版本入手，再聊聊更优的集合式写法。

一、先完善你的游标脚本 #

首先，咱们需要补充计算距离的逻辑，还要处理每个邮编对应的最近门店筛选。假设你的表结构大概是这样：

• dimZip：ZipCode（邮编）、Latitude（纬度）、Longitude（经度）
• dimStore：StoreId（门店ID）、Latitude（纬度）、Longitude（经度）
• 目标表ZipStoreDistance：ZipCode、StoreId、DistanceInMiles（英里距离）

第一步：创建计算英里距离的自定义函数 #

咱们用Haversine公式来计算两点间的英里距离，先创建这个函数：

CREATE FUNCTION dbo.CalculateMilesBetweenPoints(
    @lat1 FLOAT, @lon1 FLOAT,
    @lat2 FLOAT, @lon2 FLOAT
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @distance FLOAT;
    DECLARE @earthRadius FLOAT = 3958.8; -- 地球平均半径（英里）

    -- 将角度转换为弧度（SQL三角函数默认用弧度计算）
    SET @lat1 = RADIANS(@lat1);
    SET @lon1 = RADIANS(@lon1);
    SET @lat2 = RADIANS(@lat2);
    SET @lon2 = RADIANS(@lon2);

    -- 应用Haversine公式计算距离
    SET @distance = @earthRadius * ACOS(
        COS(@lat1) * COS(@lat2) * COS(@lon2 - @lon1) +
        SIN(@lat1) * SIN(@lat2)
    );

    RETURN @distance;
END;

第二步：补全游标插入逻辑 #

把你开头的脚本完整补全：

DECLARE @zip VARCHAR(10);
DECLARE @RangeInMiles INT = 50;
-- 新增变量存储邮编经纬度、最近门店ID和距离
DECLARE @zipLat FLOAT, @zipLon FLOAT;
DECLARE @closestStoreId INT;
DECLARE @minDistance FLOAT;

DECLARE zip_cursor CURSOR FOR 
    SELECT ZipCode FROM dimZip;

OPEN zip_cursor;
FETCH NEXT FROM zip_cursor INTO @zip;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 获取当前邮编的经纬度
    SELECT @zipLat = Latitude, @zipLon = Longitude
    FROM dimZip
    WHERE ZipCode = @zip;

    -- 筛选50英里内的门店，并找出最近的那一个
    SELECT TOP 1
        @closestStoreId = s.StoreId,
        @minDistance = dbo.CalculateMilesBetweenPoints(@zipLat, @zipLon, s.Latitude, s.Longitude)
    FROM dimStore s
    WHERE dbo.CalculateMilesBetweenPoints(@zipLat, @zipLon, s.Latitude, s.Longitude) <= @RangeInMiles
    ORDER BY @minDistance ASC;

    -- 如果找到符合条件的门店，插入目标表
    IF @closestStoreId IS NOT NULL
    BEGIN
        INSERT INTO ZipStoreDistance (ZipCode, StoreId, DistanceInMiles)
        VALUES (@zip, @closestStoreId, @minDistance);
    END

    -- 取下一个邮编
    FETCH NEXT FROM zip_cursor INTO @zip;
END

-- 关闭并释放游标
CLOSE zip_cursor;
DEALLOCATE zip_cursor;

二、更高效的集合式写法（强烈推荐） #

游标是逐行处理数据，当你的dimZip有几万甚至几十万条邮编时，性能会非常差。SQL Server是基于集合的数据库，咱们用窗口函数来实现相同需求，速度会快很多：

-- 可选：清空目标表（根据业务场景决定是否需要）
-- DELETE FROM ZipStoreDistance;

INSERT INTO ZipStoreDistance (ZipCode, StoreId, DistanceInMiles)
SELECT ZipCode, StoreId, DistanceInMiles
FROM (
    SELECT
        z.ZipCode,
        s.StoreId,
        dbo.CalculateMilesBetweenPoints(z.Latitude, z.Longitude, s.Latitude, s.Longitude) AS DistanceInMiles,
        -- 按邮编分组，给每个门店的距离排序，最近的排第1
        ROW_NUMBER() OVER (PARTITION BY z.ZipCode ORDER BY dbo.CalculateMilesBetweenPoints(z.Latitude, z.Longitude, s.Latitude, s.Longitude) ASC) AS rn
    FROM dimZip z
    CROSS JOIN dimStore s
    -- 只保留50英里内的记录
    WHERE dbo.CalculateMilesBetweenPoints(z.Latitude, z.Longitude, s.Latitude, s.Longitude) <= 50
) ranked
-- 只取每个邮编对应的最近门店
WHERE rn = 1;

额外说明 #

• 如果存在多个门店和邮编的距离完全相同且都是最近的，上面的ROW_NUMBER()只会保留其中一个。如果需要保留所有这类门店，可以把ROW_NUMBER()换成RANK()。
• 如果你的表已经有GEOGRAPHY类型的空间字段，也可以用STDistance()方法计算距离（需转换为英里）：

  -- 假设dimZip有GeoPoint字段，dimStore有StoreGeoPoint字段
  z.GeoPoint.STDistance(s.StoreGeoPoint) / 1609.34 AS DistanceInMiles -- 米转英里
  

• 为了进一步优化性能，可以给dimZip和dimStore的经纬度字段创建索引，或者给空间字段创建空间索引。

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