CODEWITHSUNDEEP
plsqllatitude-longitude
Gavin
Gavin

Reputation: 167

PLSQL distance between two points

Would appreciate any pointers, I need to find the distance between to sets of Longitude and Latitude points in PLSQL. Is there a formula that I can plug in variables for Long and Lat ? Thanks Gav

Upvotes: 2

Views: 1534

Answers (1)

Geysa O. Marinho
Geysa O. Marinho

Reputation: 79

A long time ago I was looking for ways to calcute distances with coordinates, and it helped a lot: Oracle spatial search within distance

My Test Query:

SELECT  a.LATITUDE, a.LONGITUDE, a.distance FROM
      (
        SELECT i.LATITUDE, i.LONGITUDE, calc_distance(i.LATITUDE, i.LONGITUDE, b.mylat, b.mylng) AS distance
        --https://www.google.com/maps/place/Parque+Nacional+da+Chapada+Diamantina/@-12.9838169,-42.6544937,8.13z/data=!4m5!3m4!1s0x74221bc4b6315bf:0x5b6f59531dd4b9bd!8m2!3d-12.5800309!4d-41.701355
        FROM (SELECT -12.9838169 LATITUDE, 
               -42.6544937 LONGITUDE

              FROM dual
             ) i
          JOIN (
            --https://www.google.com/maps/@-25.0814262,-48.7200207,10z
            SELECT -25.0814262 AS mylat,
                   -48.7200207 AS mylng,
                    3.1415926 AS pi, 
                    6371.4 earthradius
              FROM DUAL
          )b ON (1 = 1)

      )a

I don't know if it is right for any coordinates, but the distance was precise in my tests. To validate: https://www.sunearthtools.com/pt/tools/distance.php

The function from the link:

CREATE OR REPLACE FUNCTION calc_distance(
 pLat1 NUMBER,
 pLon1 NUMBER,
 pLat2 NUMBER,
 pLon2 NUMBER)
 RETURN NUMBER
IS

-- r is the spherical radius of earth in Kilometers
cSpherRad CONSTANT NUMBER := 6367;
                                                                        -- The spherical radius of earth in miles is 3956
a        NUMBER;
vLat     NUMBER;
vLat1Rad NUMBER;
vLat2Rad NUMBER;
vLon     NUMBER;
vLon1Rad NUMBER;
vLon2Rad NUMBER;

BEGIN
  /*
  Most computers require the arguments of trigonometric functions to be
  expressed in radians. To convert lon1, lat1 and lon2,lat2 from
  degrees,minutes, seconds to radians, first convert them to decimal
  degrees. To convert decimal degrees to radians, multiply the number
  of degrees by pi/180 = 0.017453293 radians/degrees.
  */

  vLat1Rad := pLat1 * 0.017453293;
  vLat2Rad := pLat2 * 0.017453293;
  vLon1Rad := pLon1 * 0.017453293;
  vLon2Rad := pLon2 * 0.017453293;

  vLon := vLon2Rad - vLon1Rad;
  vLat := vLat2Rad - vLat1Rad;

  a := POWER(SIN(vLat/2),2) + COS(vLat1Rad) * COS(vLat2Rad) *
  POWER(SIN(vLon/2),2);

  /*
  The intermediate result c is the great circle distance in radians.
  Inverse trigonometric functions return results expressed in radians.
  To express c in decimal degrees, multiply the number of radians by
   180/pi = 57.295780 degrees/radian.
  The great circle distance d will be in the same units as r.
  */

  RETURN ROUND(cSpherRad * 2 * ATAN2(SQRT(a), SQRT(1-a)),1);
EXCEPTION
  WHEN OTHERS THEN
    RETURN 999;
END calc_distance;

Upvotes: 2

Related Questions