Submitted on: 05-06-2001
Description:
A way to locate nearest ZIP codes from a source ZIP code. [Phill Kenoyer]
Code:
Author: Phill Kenoyer
Date: Fri, 27 Apr 2001
License Type: Public Domain, Freeware
Requirment: To create a function in Postgres that will return nearest ZIP from a source ZIP.
ZIP code table:
CREATE TABLE "zips" (
"zip" varchar(10) NOT NULL,
"state" char(2) DEFAULT 'NA' NOT NULL,
"city" varchar(50) DEFAULT 'NA' NOT NULL,
"location" point DEFAULT '(0,0)' NOT NULL -- (longitude,latitude)
);
/* ZIP code data provided from: http://ftp.census.gov/geo/www/gazetteer/places.html */
Install the Earthdistance from the contrib section of Postgresql.
create function locate_providers (char(10),int2) returns text as '
declare
source_zip alias for $1;
offset alias for $2;
source_point point;
result record;
begin
-- Get the source point by zip
select into source_point location from zips where zip = source_zip;
if not found
then return ''Error: ZIP not found'';
end if;
-- Get the providers one at a time by offset
select into result
trim(p.name)::text as name, trim(p.address1)::text as address,
p.phone1::text as phone, trim(p.city)::text as city,
round(source_point <@> location)::text as distance
from zips z, providers p
where source_point <@> location < 50 and z.zip = p.zip
order by distance
limit 1, offset;
-- Return
if found
then return result.name || '', '' || result.address || '', '' ||
result.city || '', '' || result.phone || '', '' ||
result.distance || '' Miles'';
end if;
-- if nothing found
return ''Error: Provider Not Found'';
end;'
language 'plpgsql';
Now this works great for me and my project. My programs will have a loop to get the last 10 zips ie:
for ($x=0; $x<10; $x++)
{
$sql = "select locate_provider('95661',$x)";
$db->query($sql);
while ($db->fetch())
{
...
}
}
I hope this stuff helps!
|