0

I'm trying to Calculate the distance between 2 duplicate Cases in the database I'm working on SQL Oracle

as example:

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

I want the output as

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

I tried to read a lot of articles and questions but couldn't build the logic How to calculate distance between multiple points in SQL Server?

Nora S
  • 61
  • 5
  • **Hint** : An aggregated query containing a HAVING clause along with `sdo_geom.sdo_distance`, `sdo_geometry` and `sdo_point_type` functions in the WHERE clause. – Barbaros Özhan Nov 20 '21 at 13:49
  • can you please type an example? – Nora S Nov 20 '21 at 13:53
  • You can [check out](https://stackoverflow.com/search?tab=votes&q=sdo_geom.sdo_distance) – Barbaros Özhan Nov 20 '21 at 14:01
  • Either use `sdo_geom.sdo_distance` (which is an optional Oracle feature you have to pay for) or have a look at https://stackoverflow.com/a/59171192/3027266 – Wernfried Domscheit Nov 20 '21 at 15:00
  • If you are satisfied with reduced accuracy, you can use even simple Pythagoras: `111km ⋅ sqrt((Δlat² + cos(lat) ⋅ Δlon²))`. Are these real values? 0.1° is around 11km (at the equator), so any raw difference > 0.01 is certainly more than 100 meters. – Wernfried Domscheit Nov 20 '21 at 15:12
  • You can use the "Great Circle" distance https://en.wikipedia.org/wiki/Great-circle_distance#Computational_formulas or the simplified "Haversine" distance https://en.wikipedia.org/wiki/Great-circle_distance#Computational_formulas – The Impaler Nov 20 '21 at 15:31
  • What is a "case"? What are "duplicated" cases? If I were to guess, a "case" is information for a specific ID, and the issue is that ID is not unique - so you are talking about "duplicated ID's". But **I shouldn't have to guess** - your question should be clear enough so no guessing is needed. Then: if my guess is correct, why doesn't ID = 2 appear in your desired output? Also, what if an ID appears **more than twice** in the input table? What is the desired output then? –  Nov 20 '21 at 17:57
  • @WernfriedDomscheit The spatial option is now free for all database editions so it should be safe to use the SDO functions anywhere. – Jon Heller Nov 20 '21 at 19:29

1 Answers1

0

You can create the function:

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;
/

Then use the query:

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
);

Or any other method of grouping the rows into pairs and pivoting and then calling the function.

Which, for the sample data:

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;

Outputs:

ID DISTANCE_METRES DISTANCE
1 144947.804966182829942744055657720422603 more than 100 meter
2 3395725.11733156831056822390960787854383 more than 100 meter
3 0 less than 100 meter

Or, if you want to use SDO Geometry functions:

SELECT id,
       sdo_geom.sdo_distance(
         sdo_geometry(
           2001, -- 2D co-ordinate containing a single point 
           4326, -- Spatial reference system id (SRID) for WGS84 coordinates
           sdo_point_type(lat1,long1,null), 
           null, 
           null
         ),
         sdo_geometry(
           2001, -- 2D co-ordinate containing a single point 
           4326, -- Spatial reference system id (SRID) for WGS84 coordinates
           sdo_point_type(lat2,long2,null), 
           null, 
           null
         ),
         0.005,
         'unit=m'
       ) AS distance_metres,
       CASE
       WHEN sdo_geom.sdo_distance(
              sdo_geometry(
                2001, -- 2D co-ordinate containing a single point 
                4326, -- Spatial reference system id (SRID) for WGS84 coordinates
                sdo_point_type(lat1,long1,null), 
                null, 
                null
              ),
              sdo_geometry(
                2001, -- 2D co-ordinate containing a single point 
                4326, -- Spatial reference system id (SRID) for WGS84 coordinates
                sdo_point_type(lat2,long2,null), 
                null, 
                null
              ),
              0.005,
              'unit=m'
            ) > 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
)

Which outputs:

ID DISTANCE_METRES DISTANCE
1 149223.001672844 more than 100 meter
2 3293714.72371264 more than 100 meter
3 0 less than 100 meter

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117