如何在Oracle SQL中计算数据库重复病例之间的地理距离

实现逻辑 #

• 先筛选出恰好存在2条关联记录的ID（对应输出的duplication_status为2）
• 对同ID的两条记录做自连接，获取两条记录各自的经纬度坐标
• 基于经纬度计算两点球面距离，换算为米单位后判断区间输出对应描述

方案选择说明 #

Oracle支持两种距离计算方案，可根据数据库权限选择：

1. 内置空间函数方案：需要数据库开启Oracle Spatial组件权限，计算精度更高
2. Haversine公式手写方案：无额外权限要求，通用性更强

方案1：使用Oracle Spatial内置函数实现 #

WITH id_with_dup AS (
    -- 筛选出刚好有2条重复记录的ID
    SELECT ID, COUNT(*) AS duplication_status
    FROM case_location
    GROUP BY ID
    HAVING COUNT(*) = 2
),
coord_pairs AS (
    -- 自连接获取同ID的两个坐标点，用ID_Household比较避免重复配对
    SELECT 
        a.ID,
        a.long AS long1, a.lat AS lat1,
        b.long AS long2, b.lat AS lat2,
        c.duplication_status
    FROM case_location a
    JOIN case_location b 
        ON a.ID = b.ID 
        AND a.ID_Household < b.ID_Household
    JOIN id_with_dup c ON a.ID = c.ID
)
SELECT 
    ID,
    duplication_status,
    CASE 
        WHEN SDO_GEOM.SDO_DISTANCE(
            SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(long1, lat1, NULL), NULL, NULL),
            SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(long2, lat2, NULL), NULL, NULL),
            0.005, 'unit=m'
        ) > 100 THEN 'more than 100 meter'
        ELSE 'less than 100 meter'
    END AS distance
FROM coord_pairs;

说明：参数4326是WGS84经纬度坐标系的SRID，若你的数据使用其他坐标系需替换为对应SRID值。

方案2：使用Haversine公式手动计算（无额外权限要求） #

WITH id_with_dup AS (
    SELECT ID, COUNT(*) AS duplication_status
    FROM case_location
    GROUP BY ID
    HAVING COUNT(*) = 2
),
coord_pairs AS (
    SELECT 
        a.ID,
        a.long AS long1, a.lat AS lat1,
        b.long AS long2, b.lat AS lat2,
        c.duplication_status
    FROM case_location a
    JOIN case_location b 
        ON a.ID = b.ID 
        AND a.ID_Household < b.ID_Household
    JOIN id_with_dup c ON a.ID = c.ID
)
SELECT 
    ID,
    duplication_status,
    CASE 
        -- 6371000为地球平均半径，单位：米
        WHEN 6371000 * 2 * ASIN(SQRT(
            POWER(SIN((RADIANS(lat2) - RADIANS(lat1))/2), 2)
            + COS(RADIANS(lat1)) * COS(RADIANS(lat2))
            * POWER(SIN((RADIANS(long2) - RADIANS(long1))/2), 2)
        )) > 100 THEN 'more than 100 meter'
        ELSE 'less than 100 meter'
    END AS distance
FROM coord_pairs;

说明：该方案计算结果误差在1%以内，可满足常规距离判断需求。

输出结果 #

两种方案执行后都会输出你要求的格式：

ID  duplication_status    distance
1   2                     more than 100 meter
3   2                     less than 100 meter

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