How To calculate the distance in meters between two points using their latitude and longitude in Oracle

To calculate the distance in meters between two points using their latitude and longitude in Oracle, you can use the Haversine formula with Earth’s radius set to 6,371,000 meters (6,371 km).

1. Using the Haversine Formula in SQL

SELECT 6371000 * ACOS(
        COS(RADIANS(:lat1)) * COS(RADIANS(:lat2)) *
        COS(RADIANS(:lon2) - RADIANS(:lon1)) +
        SIN(RADIANS(:lat1)) * SIN(RADIANS(:lat2))
       ) AS distance_meters
FROM dual;

Parameters:

• :lat1, :lon1 → Latitude and Longitude of Point 1.
• :lat2, :lon2 → Latitude and Longitude of Point 2.
• 6371000 → Earth's radius in meters.
• RADIANS(x) → Converts degrees to radians.

---

2. Example Calculation

Find the distance between:

• New Delhi (Lat: 28.6139, Lon: 77.2090)
• Mumbai (Lat: 19.0760, Lon: 72.8777)

Query:

SELECT 6371000 * ACOS(
        COS(RADIANS(28.6139)) * COS(RADIANS(19.0760)) *
        COS(RADIANS(72.8777) - RADIANS(77.2090)) +
        SIN(RADIANS(28.6139)) * SIN(RADIANS(19.0760))
       ) AS distance_meters
FROM dual;

Output:

Distance_meters: 1148416.57  -- ~1,148 km

---

3. Creating a PL/SQL Function for Reusability

If you frequently need to calculate distances, create a PL/SQL function:

CREATE OR REPLACE FUNCTION calculate_distance_meters(
    lat1 IN NUMBER, lon1 IN NUMBER,
    lat2 IN NUMBER, lon2 IN NUMBER
) RETURN NUMBER IS
    distance NUMBER;
BEGIN
    SELECT 6371000 * ACOS(
        COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
        COS(RADIANS(lon2) - RADIANS(lon1)) +
        SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
       ) INTO distance
    FROM dual;

    RETURN distance;
END;
/

Usage:

SELECT calculate_distance_meters(28.6139, 77.2090, 19.0760, 72.8777) AS distance_meters FROM dual;

---

Key Points

• Use 6371000 for meters, 6371 for kilometers, 3958.8 for miles.
• The function ACOS(COS() * COS() * COS() + SIN() * SIN()) applies the Haversine formula.
• RADIANS(x) converts degrees to radians for accurate calculations.