IBM Informix stored procedure to find the distance in miles from a user supplied zipcode and number of miles.

distancesearch.txt

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)