如何基于近似经纬度关联度假村信息表与雪情信息表？

原始数据表 #

度假村信息表（Resorts）

雪情信息表（SnowData）

核心思路 #

通过计算地理坐标间的球面距离（避免平面距离的误差），为每个度假村筛选出距离最近的雪情记录。以下是几种常用实现方式：

1. 通用SQL实现（无需空间扩展）

使用Haversine公式计算两点间的球面距离（单位：公里），再筛选每个度假村的最小距离记录：

SELECT 
    r.Resorts,
    r.Latitude AS Resort_Lat,
    r.Longitude AS Resort_Lon,
    s.Month,
    s.Latitude AS Snow_Lat,
    s.Longitude AS Snow_Lon,
    -- Haversine距离计算公式
    6371 * 2 * ASIN(
        SQRT(
            POWER(SIN((r.Latitude - s.Latitude) * PI()/180 / 2), 2) +
            COS(r.Latitude * PI()/180) * COS(s.Latitude * PI()/180) *
            POWER(SIN((r.Longitude - s.Longitude) * PI()/180 / 2), 2)
        )
    ) AS Distance_KM
FROM Resorts r
CROSS JOIN SnowData s
WHERE (r.Resorts, Distance_KM) IN (
    SELECT 
        r_inner.Resorts,
        MIN(
            6371 * 2 * ASIN(
                SQRT(
                    POWER(SIN((r_inner.Latitude - s_inner.Latitude) * PI()/180 / 2), 2) +
                    COS(r_inner.Latitude * PI()/180) * COS(s_inner.Latitude * PI()/180) *
                    POWER(SIN((r_inner.Longitude - s_inner.Longitude) * PI()/180 / 2), 2)
                )
            )
        ) AS Min_Distance
    FROM Resorts r_inner
    CROSS JOIN SnowData s_inner
    GROUP BY r_inner.Resorts
)
ORDER BY r.Resorts, Distance_KM;

执行后会返回Hemsedal度假村对应的最近雪情记录（即第二行雪情数据，距离约30公里）。

2. 空间数据库优化实现（以PostgreSQL+PostGIS为例）

如果使用支持空间数据的数据库，可借助内置空间函数简化计算：

SELECT 
    r.Resorts,
    s.Month,
    -- 计算距离并转为公里
    ST_Distance(
        ST_SetSRID(ST_MakePoint(r.Longitude, r.Latitude), 4326),
        ST_SetSRID(ST_MakePoint(s.Longitude, s.Latitude), 4326)
    ) / 1000 AS Distance_KM
FROM Resorts r
-- 为每个度假村关联最近的1条雪情记录
JOIN LATERAL (
    SELECT *
    FROM SnowData s
    ORDER BY ST_Distance(
        ST_SetSRID(ST_MakePoint(r.Longitude, r.Latitude), 4326),
        ST_SetSRID(ST_MakePoint(s.Longitude, s.Latitude), 4326)
    )
    LIMIT 1
) s ON TRUE;

ST_SetSRID(4326)指定使用WGS84全球坐标系，LATERAL JOIN配合LIMIT 1直接获取最近记录，效率更高。

3. Excel手动处理方案

如果用Excel分析，可通过公式实现：

1. 在雪情表新增Distance列，输入公式计算与度假村的距离：

   =6371*2*ASIN(SQRT(SIN((B2-$B$2)*PI()/180/2)^2+COS(B2*PI()/180)*COS($B$2*PI()/180)*SIN((C2-$C$2)*PI()/180/2)^2))
   

   （假设度假村经纬度在B2、C2，雪情表经纬度在B列、C列）
2. 计算最小距离：=MIN(D:D)（D列为新增的Distance列）
3. 匹配对应雪情记录：=INDEX(A:A,MATCH(MIN(D:D),D:D,0))（A列为Month列）

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