PL-SQL – Calculating distance between two geographical points (coordinates based)

Hi dear readers,

This is the first contribution of this blog to PL-SQL community, actually this is a tribute to my great friend Itagyba Abondanza Kuhlman, author of this source code, please visit his blog at http://blog.iak.com.br/

There are a lot of curiosities about the Oracle Database World, his specialty. He is one of those people who came to this planet to the mission of helping humanity to evolve, developing critical thinking, friendship, thinking of things never before thought, leaving wherever he goes a trail of good feelings. Thank You Gyba.

The purpose of this code is to calculate the distance (in Kilometers) between two geographical points using mathematics, specifically trigonometry.

create or replace PACKAGE GEOGRAPHICAL_PACKAGE IS

--This Package is designed to calculate the linear distance between two geographical points
--based on the Latitute and Longitude

HALF_CIRCLE CONSTANT NUMBER := 180;
MINUTES_PER_DEGREES CONSTANT NUMBER := 60;
MILES_PER_MINUTES CONSTANT NUMBER := 1.1515;
KILOMETERS_PER_MILES CONSTANT NUMBER := 1.609344;

FUNCTION PI RETURN NUMBER DETERMINISTIC;

FUNCTION DEGR2RAD(DEGREES NUMBER) RETURN NUMBER DETERMINISTIC;

FUNCTION RAD2DEGR(RADIANS NUMBER) RETURN NUMBER DETERMINISTIC;

FUNCTION DISTANCE(LAT1 NUMBER
,LONG1 NUMBER
,LAT2 NUMBER
,LONG2 NUMBER) RETURN NUMBER DETERMINISTIC;
--
END GEOGRAPHICAL_PACKAGE;

create or replace PACKAGE BODY GEOGRAPHICAL_PACKAGE IS
--
FUNCTION PI RETURN NUMBER DETERMINISTIC IS

BEGIN
RETURN ACOS(-1);
END PI;

FUNCTION DEGR2RAD(DEGREES NUMBER) RETURN NUMBER DETERMINISTIC IS

BEGIN
RETURN DEGREES * PI / HALF_CIRCLE;
END DEGR2RAD;

FUNCTION RAD2DEGR(RADIANS NUMBER) RETURN NUMBER DETERMINISTIC IS

BEGIN
RETURN RADIANS * HALF_CIRCLE / PI;
END RAD2DEGR;

FUNCTION DISTANCE(LAT1 NUMBER
,LONG1 NUMBER
,LAT2 NUMBER
,LONG2 NUMBER) RETURN NUMBER DETERMINISTIC IS
--
THETA NUMBER := LONG1 - LONG2;
DIST NUMBER;
LAT1_RAD NUMBER;
LAT2_RAD NUMBER;
--
BEGIN
--
LAT1_RAD := DEGR2RAD(LAT1);
LAT2_RAD := DEGR2RAD(LAT2);
DIST := SIN(LAT1_RAD) * SIN(LAT2_RAD) +
COS(LAT1_RAD) * COS(LAT2_RAD) * COS(DEGR2RAD(THETA));

-- This analysis was performed due to a situation where the Operating above
-- between points this returning a value that would be improper for the function
-- ACOS where your range should be among the possible values from -1 up to 1
DIST := ACOS(GREATEST(LEAST(DIST,1),-1));
DIST := RAD2DEGR(DIST);
DIST := DIST * MINUTES_PER_DEGREES * MILES_PER_MINUTES * KILOMETERS_PER_MILES;
--
RETURN DIST;
--
END DISTANCE;
--
END GEOGRAPHICAL_PACKAGE;