If you are not using SDO_GEOM then you can create the haversine function to calculate the distance between two latitude/longditude co-ordinates:
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, I am assuming that you have your data in third normal form:
CREATE TABLE towers (
tower_id PRIMARY KEY,
t_lat,
t_long
) AS
SELECT 'CM0321', 6.890487, 79.869199 FROM DUAL UNION ALL
SELECT 'BD0010', 6.947618, 81.160246 FROM DUAL UNION ALL
SELECT 'CM0037', 6.896111, 79.868611 FROM DUAL UNION ALL
SELECT 'GM0121', 7.121666, 80.028888 FROM DUAL;
CREATE TABLE customers (
customer_number PRIMARY KEY,
cx_lat,
cx_long,
latched_tower_id
) AS
SELECT 34532, 6.897257333, 79.86474533, 'CM0321' FROM DUAL UNION ALL
SELECT 43445, 6.935598403, 81.14939421, 'BD0010' FROM DUAL UNION ALL
SELECT 54365, 6.866224000, 79.88215000, 'CM0037' FROM DUAL UNION ALL
SELECT 52568, 7.113198000, 80.03724700, 'GM0121' FROM DUAL;
ALTER TABLE customers ADD CONSTRAINT customers__lti__fk
FOREIGN KEY (latched_tower_id) REFERENCES towers (tower_id);
Then, from Oracle 12, you can calculate the closer towers using:
SELECT c.*,
TO_CHAR(
HAVERSINE_DISTANCE(c.cx_lat, c.cx_long, t.t_lat, t.t_long)/1000,
'FM999990.000'
) AS distance,
ct.tower_id AS closer_tower_id,
TO_CHAR(ct.distance, 'FM999990.000') AS closer_distance
FROM customers c
INNER JOIN towers t
ON (t.tower_id = c.latched_tower_id)
LEFT OUTER JOIN LATERAL(
SELECT ct.*,
HAVERSINE_DISTANCE(
c.cx_lat,
c.cx_long,
ct.t_lat,
ct.t_long
)/1000 AS distance
FROM towers ct
ORDER BY distance ASC
FETCH FIRST ROW ONLY
) ct
ON (ct.tower_id != c.latched_tower_id);
Which outputs:
| CUSTOMER_NUMBER |
CX_LAT |
CX_LONG |
LATCHED_TOWER_ID |
DISTANCE |
CLOSER_TOWER_ID |
CLOSER_DISTANCE |
| 43445 |
6.935598403 |
81.14939421 |
BD0010 |
1.795 |
|
|
| 54365 |
6.866224 |
79.88215 |
CM0037 |
3.644 |
CM0321 |
3.053 |
| 34532 |
6.897257333 |
79.86474533 |
CM0321 |
0.899 |
CM0037 |
0.445 |
| 52568 |
7.113198 |
80.037247 |
GM0121 |
1.318 |
|
|
Before Oracle 12, you can use:
SELECT customer_number,
cx_lat,
cx_long,
latched_tower_id,
distance,
CASE
WHEN latched_tower_id != closer_tower_id
THEN closer_tower_id
END AS closer_tower_id,
CASE
WHEN latched_tower_id != closer_tower_id
THEN closer_distance
END AS closer_distance
FROM (
SELECT c.*,
TO_CHAR(
HAVERSINE_DISTANCE(c.cx_lat, c.cx_long, t.t_lat, t.t_long)/1000,
'FM999990.000'
) AS distance,
ct.tower_id AS closer_tower_id,
TO_CHAR(
HAVERSINE_DISTANCE(c.cx_lat, c.cx_long, ct.t_lat, ct.t_long)/1000,
'FM999990.000'
) AS closer_distance,
ROW_NUMBER() OVER (
PARTITION BY c.customer_number
ORDER BY HAVERSINE_DISTANCE(c.cx_lat, c.cx_long, ct.t_lat, ct.t_long)
) AS rn
FROM customers c
INNER JOIN towers t
ON (t.tower_id = c.latched_tower_id)
CROSS JOIN towers ct
ORDER BY
customer_number,
DISTANCE ASC
)
WHERE rn = 1;
db<>fiddle here
