Posted by 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
Learning is not a spectator sport 
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.
🙂
Ah sorry – typo. Thanks
Hello,
Is the result expressed in KM or miles?
Thank you
KM
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.
Thanks for that
Instead of ‘3.1415927’ better use simply ‘ACOS(-1)’
+1
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.