Here's the plpgsql function we use. It takes two zipcodes and calculates the distance between them. You can find the formulas on the internet.
create function zip_distance (char,char)
returns float8 as '
declare
p_zip1 alias for $1;
p_zip2 alias for $2;
v_lat float8;
v_lon float8;
v_lat_ref float8;
v_lon_ref float8;
v_distance float8;
lat_rad float8;
lon_rad float8;
lat_ref_rad float8;
lon_ref_rad float8;
dlon float8;
dlat float8;
a float8;
c float8;
d float8;
begin
select latitude::float8, longitude::float8
into v_lat, v_lon from us_zipcodes where zipcode = p_zip1;
select latitude::float8, longitude::float8
into v_lat_ref, v_lon_ref from us_zipcodes where zipcode = p_zip2;
lat_rad := v_lat * 0.017453293;
lon_rad := v_lon * 0.017453293;
lat_ref_rad := v_lat_ref * 0.017453293;
lon_ref_rad := v_lon_ref * 0.017453293;
dlon := lon_rad - lon_ref_rad;
dlat := lat_rad - lat_ref_rad;
a := pow(sin(dlat/2),2) + (cos(lat_rad)*cos(lat_ref_rad)*(pow(sin(dlon/2),2)));
c := 2*atan2(sqrt(a),sqrt(1-a));
d := 3963 * c;
return trunc(d);
end;' language 'plpgsql';
In your query, do something like this:
...
where zip_distance(:ref_zipcode,zipcode) <= $search_distance
...
Gilbert
