Great Circle Distance Function for Oracle
The Distance Learning folks wanted a provision to allow users to search for site locations based on the distance from a specified zip code. I did some research and found a few resources. Most of things I found use a single SQL statement, but that didn’t suit our needs so I made it into an Oracle function:
create or replace function gc_dist (lat1 IN number, lon1 IN number, lat2 IN number, lon2 IN number) RETURN number
is
A_lat number;
A_lon number;
B_lat number;
B_lon number;
delta_lat number;
delta_lon number;
pi number;
earth_radius number;
gcdist number;
distance number;
begin
pi := 3.14159265358979323;
earth_radius := 3963.189;
A_lat := (lat1 * pi) / 180;
A_lon := (lon1 * pi) / 180;
B_lat := (lat2 * pi) / 180;
B_lon := (lon2 * pi) / 180;
delta_lat := A_lat - B_lat;
delta_lon := A_lon - B_lon;
-- find great circle distance
gcdist := power(sin(delta_lat / 2), 2) + cos(A_lat) * cos(B_lat) * power(sin(delta_lon / 2), 2);
distance := ROUND(earth_radius * 2 * atan2(sqrt(gcdist),sqrt(1 - gcdist)), 1);
return distance;
end;I found the “exact” Earth radius on Wikipedia. phpZipLocator was used to get it working with the site. I had to modify the functions a little to suit the specific needs of the application.
Using something like this requires a database of zip codes along with the corresponding latitude and longitude for each. One can be found on the phpZipLocator site, but I don’t think I ended up using that one. I did a lot of research to find the best free one, but unfortunately I don’t remember which one was used (here’s another). As far as commercial solutions, I thought this one looks like one of the better ones. I’ll likely get back into it at some point and look this stuff up again.
Here’s an example usage of the function:
- Extract the latitude and longitude from the given zipcode:
SELECT lat, lon FROM zipcodes WHERE zip = :ZIP“:ZIP” is the specified zipcode and “zipcodes” is the name of the zipcode table. - Build the first part of the query sans the WHERE clause. In the WHERE clause we’ll specify the radius
SELECT gc_dist(zipcodes.lat, zipcodes.lon, :LAT, :LON) AS distance FROM zipcodes“:LAT” and “:LON” represent the returned values from the first query - Use phpZipLocator’s inradius function to build the WHERE clause that will restrict the results within the specified radius of the specified zipcode. Here is what’s inside the function (I don’t remember what I modified in the function so I’ll just paste the main part):
WHERE (POWER((69.1 * (lon - :LON) * COS(:LAT / 57.3)), 2) + POWER((69.1 * (lat - :LAT)), 2)) < (:RADIUS * :RADIUS);“:LON” and “:LAT” were found in the first query, and “:RADIUS” represents the specified radius in miles. - Put them together and run the query!