Výpočet zeměpisné vzdálenosti mezi 2 duplicitní případy

Můžete vytvořit funkce:

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

Pak použijte dotaz:

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

Nebo jakýkoli jiný způsob seskupení řádků do dvojic a otočná a pak volání funkce.

Což, pro ukázková data:

CREATE TABLE IF NOT EXISTS 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;

Výstupy:

ID	DISTANCE_METRES	VZDÁLENOST
1	144947.804966182829942744055657720422603	více než 100 metrů
2	3395725.11733156831056822390960787854383	více než 100 metrů
3	0	méně než 100 metrů

Nebo, pokud chcete použít SDO Geometrie funkce:

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
)

Které výstupy:

ID	DISTANCE_METRES	VZDÁLENOST
1	149223.001672844	více než 100 metrů
2	3293714.72371264	více než 100 metrů
3	0	méně než 100 metrů

db<>housle zde