计算两个重复案例之间的地理距离

Question

我正在尝试计算数据库中两个重复案例之间的距离我正在研究SQL Oracle

例如：

ID ID_Household  long   lat
1  1             3.2    22.2
1  2             2.3    21.2
2  3             22.2   45.4
2  4             12.8   15.9
3  3             11.2   13.2
3  4             11.2   13.2

我想要的输出

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

我试着读了很多文章和问题，但无法构建逻辑如何在SQL Server中计算多个点之间的距离？

Answer 1

您可以创建函数：

CREATE FUNCTION haversine_distance(
lat1  IN NUMBER,
long1 IN NUMBER,
lat2  IN NUMBER,
long2 IN NUMBER
) RETURN NUMBER DETERMINISTIC
IS
PI           CONSTANT NUMBER := ASIN(1) * 2;
R            CONSTANT NUMBER := 6371000; -- Approx. radius of the earth in m
PHI1         CONSTANT NUMBER := lat1 * PI / 180;
PHI2         CONSTANT NUMBER := lat2 * PI / 180;
DELTA_PHI    CONSTANT NUMBER := (lat2 - lat1) * PI / 180;
DELTA_LAMBDA CONSTANT NUMBER := (long2 - long1) * PI / 180;
a NUMBER;
c NUMBER;
BEGIN
a := SIN(delta_phi/2) * SIN(delta_phi/2) + COS(phi1) * COS(phi2) *
SIN(delta_lambda/2) * SIN(delta_lambda/2);
c := 2 * ATAN2(SQRT(a), SQRT(1-a));
RETURN R * c; -- in metres
END;
/

然后使用查询：

SELECT id,
haversine_distance(lat1, long1, lat2, long2) AS distance_metres,
CASE
WHEN haversine_distance(lat1, long1, lat2, long2) > 100
THEN 'more than 100 meter'
ELSE 'less than 100 meter'
END AS distance
FROM   table_name
MATCH_RECOGNIZE(
PARTITION BY id
ORDER     BY id_household
MEASURES
FIRST(longitude) AS long1,
FIRST(latitude)  AS lat1,
LAST(longitude)  AS long2,
LAST(latitude)   AS lat2
PATTERN ( house{2} )
DEFINE
house AS 1 = 1
);

或任何其他将行分组成对并进行数据透视然后调用函数的方法

对于样本数据：

CREATE TABLE table_name (ID, ID_Household, longitude, latitude) AS
SELECT 1, 1,  3.2, 22.2 FROM DUAL UNION ALL
SELECT 1, 2,  2.3, 21.2 FROM DUAL UNION ALL
SELECT 2, 3, 22.2, 45.4 FROM DUAL UNION ALL
SELECT 2, 4, 12.8, 15.9 FROM DUAL UNION ALL
SELECT 3, 3, 11.2, 13.2 FROM DUAL UNION ALL
SELECT 3, 4, 11.2, 13.2 FROM DUAL;

输出：

超过100米超过100米

ID	DISTANCE_METRES	DISTANCE
1	144947.804966182829942744055657720422603
2	3395725.11733156831056822390960787854383
3	0	小于100米