Created
March 10, 2015 03:22
-
-
Save matthallamew/60d5cc9cb2c7997b1a76 to your computer and use it in GitHub Desktop.
IBM Informix stored procedure to find the distance in miles from a user supplied zipcode and number of miles.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
procedure distancesearch | |
privilege owner | |
description "Returns cities,states,zips, and distances from the given zip and number of miles" | |
inputs inZip char(10) "inZip" | |
inMiles integer "inMiles" | |
returns char(30) "retCity" | |
char(2) "retSt" | |
char(10) "retZip" | |
float "retDist" | |
notes "" | |
begin procedure | |
DEFINE mylat float; | |
DEFINE mylon float; | |
DEFINE pii float; | |
DEFINE earthRadius integer; | |
DEFINE retCity char(30); | |
DEFINE retSt char(30); | |
DEFINE retZip char(10); | |
DEFINE retDist float; | |
LET pii = 3.14159265358979; | |
LET earthRadius = 3959; | |
if inZip > 0 then | |
select unique lat,lon into mylat,mylon | |
from zipcodetable | |
where zip = inZip | |
; | |
foreach | |
select t.city,t.state,t.zip,t.distance | |
into retCity,retSt,retZip,retDist | |
from ( | |
select unique a.city,a.state,a.zip, (earthRadius * 2 * ASIN(SQRT(POW(SIN((mylat - a.lat) * pii / 180 / 2),2) + | |
COS(mylat * pii / 180) * COS(a.lat * pii / 180) * | |
POW(SIN((mylon - a.lon) * pii / 180 / 2),2) )) ) as distance | |
from zipcodetable a | |
) as t | |
where t.distance <= inMiles | |
return retCity,retSt,retZip,retDist with resume; | |
end foreach; | |
else | |
return "","","",0 ; | |
end if; | |
end procedure | |
grant | |
execute to (public) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment