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!