Common goal . Find all zip codes within 30 miles of the given zip code.
Data source . A list of zip codes with latitude and longitude like "x" and "y".
Example:
create or replace view zips as
select 37171 zip, 36.362704 y, -87.30434 x,'Southside' City, 'TN' State from dual
union
select 37212, 36.133012, -86.802764, 'Nashville', 'TN' from dual
union
select 37027, 36.00245, -86.791159, 'Brentwood', 'TN' from dual
union
select 37191, 36.501196, -87.539057, 'Woodlawn', 'TN' from dual
union
select 37067, 35.928406, -86.805538, 'Franklin', 'TN' from dual ;
What I tried : I found something similar to solve all my problems
CREATE OR REPLACE FUNCTION distance (Lat1 IN NUMBER,
Lon1 IN NUMBER,
Lat2 IN NUMBER,
Lon2 IN NUMBER,
Radius IN NUMBER DEFAULT 3963) RETURN NUMBER IS
DegToRad NUMBER := 57.29577951;
BEGIN
RETURN(NVL(Radius,0) * ACOS((sin(NVL(Lat1,0) / DegToRad) * SIN(NVL(Lat2,0) / DegToRad)) +
(COS(NVL(Lat1,0) / DegToRad) * COS(NVL(Lat2,0) / DegToRad) *
COS(NVL(Lon2,0) / DegToRad - NVL(Lon1,0)/ DegToRad))));
END;
However, this gave me funky numbers - consistently too small (I know it is not 1 mile from Franklin to Nashville), but not a constant factor that I could determine. (The code I used for testing is below - I just compared the distances on Google Maps with these distances)
select b.zip
,b.city
,b.state
,distance(a.x,a.y,b.x,b.y) distance
from zips a, zips b
where a.zip=37067
order by distance;
, , , , lat long, Wikipedia Haversine
CREATE OR REPLACE FUNCTION distance (Lat1_d IN NUMBER,
Lon1_d IN NUMBER,
Lat2_d IN NUMBER,
Lon2_d IN NUMBER,
Radius IN NUMBER DEFAULT 3959) RETURN NUMBER IS
-- Convert degrees to radians
DegToRad NUMBER := .0174532925;
Lat1 NUMBER := Lat1_d * DegToRad;
Lon1 NUMBER := Lon1_d * DegToRad;
Lat2 NUMBER := Lat2_d * DegToRad;
Lon2 NUMBER := Lon2_d * DegToRad;
BEGIN
RETURN 2*Radius *
asin(
sqrt(
power(sin((Lat2-Lat1)/2),2) + cos(Lat1) * cos(Lat2) * power(sin((Lon2-Lon1)/2),2)
)
)
;
END;
- , , . ? ?
, , 30-50 , , , / .