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/

Gyba

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.

pythagorean-theoremBdHxo


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

2 thoughts on “PL-SQL – Calculating distance between two geographical points (coordinates based)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s