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

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

BEGIN
RETURN RADIANS * HALF_CIRCLE / PI;

FUNCTION DISTANCE(LAT1  NUMBER
,LONG1 NUMBER
,LAT2  NUMBER
,LONG2 NUMBER) RETURN NUMBER DETERMINISTIC IS
--
THETA NUMBER := LONG1 - LONG2;
DIST  NUMBER;
--
BEGIN
--

-- 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 := DIST * MINUTES_PER_DEGREES * MILES_PER_MINUTES * KILOMETERS_PER_MILES;
--
RETURN DIST;
--
END DISTANCE;
--
END GEOGRAPHICAL_PACKAGE;

__ATA.cmd.push(function() {
__ATA.initVideoSlot('atatags-370373-5d14777809b4f', {
sectionId: '370373',
});
});

__ATA.cmd.push(function() {
__ATA.initSlot('atatags-26942-5d14777809b83',  {
collapseEmpty: 'before',
sectionId: '26942',
location: 120,
width: 300,
height: 250
});
});

1. Itagyba Abondanza Kuhlmann says:
1. victorjabur says: