Haversine PL/SQL

January 17, 2017

1 minute

I didn’t see a PL/SQL version on https://rosettacode.org for the Haversine formula so here’s a version for anyone that needs it.



SQL> create or replace
  2  function p2p_distance(
  3              p_latitude1 number,
  4              p_longitude1 number,
  5              p_latitude2 number,
  6              p_longitude2 number) return number deterministic is
  7    earth_radius  number := 6371;
  8    pi_approx     number := 3.1415927/180; 
  9    lat_delta     number := (p_latitude2-p_latitude1)*pi_approx;
 10    lon_delta     number := (p_longitude2-p_longitude1)*pi_approx;
 11    arc           number := sin(lat_delta/2) * sin(lat_delta/2) +
 12                                 sin(lon_delta/2) * sin(lon_delta/2) * cos(p_latitude1*pi_approx) * cos(p_latitude2*pi_approx);
 13  begin
 14    return earth_radius * 2 * atan2(sqrt(arc), sqrt(1-arc));
 15  end;
 16  /

Function created.

SQL>
SQL> select p2p_distance(36.12, -86.67, 33.94, -118.4) from dual;

P2P_DISTANCE(36.12,-86.67,33.94,-118.4)
---------------------------------------
                             2886.40705

10 responses to “Haversine PL/SQL”

Iurii

January 17, 2017 at 8:01 pm

Hello Connor,
I want to suggest a tiny correction for pi, it seems, has to be 3.141593, i.e. last number is 3 instead of 7.
🙂

Reply
connormcdonald

January 18, 2017 at 8:59 am

Ah sorry – typo. Thanks

Reply
John Cardenas

March 14, 2018 at 7:30 am

Hello,

Is the result expressed in KM or miles?

Thank you

Reply
1. genevievewarriner
  
  March 14, 2018 at 7:35 am
  
  KM
  
  Reply
David

October 4, 2018 at 6:26 am

Hey Connor, I think your π approximation is fine being 3.1415927, it’s closer than what lurii suggests at 3.141593. You’ve just used 7 places instead of 6. π = 3.141592653589 10 12 dec places.

Reply
Connor McDonald

October 4, 2018 at 7:50 am

Thanks for that

Reply
Spring has Sprung! – OBIEE News

April 24, 2019 at 5:04 pm

[…] Connor McDonald shared Haversine PL/SQL […]

Reply
Wernfried Domscheit

December 4, 2019 at 3:44 pm

Instead of ‘3.1415927’ better use simply ‘ACOS(-1)’

Reply
1. Connor McDonald
  
  December 4, 2019 at 3:45 pm
  
  +1
  
  Reply
Roelof van Suilichem

May 13, 2021 at 10:31 pm

Thanks for the code, Connor.
Possibly a textbook case for optimisation by using SIMPLE_DOUBLE rather than NUMBER throughout, and also to consider native compilation on top of that. I had a similar case a few years ago, and using SIMPLE types gave a HUGE improvement.

Reply