Thread: plpgsql locate provider by zip code

I spent the last 15 hours writing this function.  It took me all day to
figure it out.  This is my first postgresql function.

I have a zip code database (the free one).  I wanted to locate providers
by zip code, and also have the distance to each provider.  I had a PHP
script that did this well, but I needed to move it into the database to
make it easy for the IVR system.  This is what I did:

/* My database of zips */
CREATE TABLE "zips" (
   "zip" int8 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)
);

/* Earthdistance from Postgresql contrib */
CREATE FUNCTION geo_distance (point, point) RETURNS float8
  AS '/usr/local/pgsql/share/contrib/earthdistance.so' LANGUAGE 'c';

CREATE OPERATOR <@> (
  leftarg = point,
  rightarg = point,
  procedure = geo_distance,
  commutator = <@>
);


I figured out this query to select my providers:

/* get my location by zip */
select location from zips where zip=95661;

/* using my location get 10 providers */
select p.name, p.city, p.phone1, '(121.234,38.7346)'::point <@> location as distance
from zips z, providers p
where '(121.234,38.7346)'::point <@> location < 50
and z.zip = p.zip
order by distance
limit 10;

This worked well, now time to move it into the database:

create function locate_providers (int8,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 I could issue:

    select locate_providers(95661,0);
    select locate_providers(95661,1);
    select locate_providers(95661,2);
    ...
    select locate_providers(95661,10);

and get back each provider by distance.

It works well for me, probably a few things wrong with it, but I'm a
novice! 8-)  I hope this helps some of the people out there trying to
figure out this stuff.

--
 _   |       _
(_()(|('.|)('||.|()|`|(

On Fri, 27 Apr 2001, Phill Kenoyer wrote:

> I spent the last 15 hours writing this function.  It took me all day to
> figure it out.  This is my first postgresql function.
>
> I have a zip code database (the free one).  I wanted to locate providers
> by zip code, and also have the distance to each provider.  I had a PHP
> script that did this well, but I needed to move it into the database to
> make it easy for the IVR system.  This is what I did:
>
> /* My database of zips */
> CREATE TABLE "zips" (
>    "zip" int8 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)
> );

Phill --

This looks interesting. I haven't tried to the geographic functions yet,
but this is inspiring me to take a look.

Two thoughts:

1) please submit this (& other plpgsql functions you write!) to the
   cookbook at techdocs.postgresql.org.

2) you might want to consider using a varchar() for zip, rather than
   int. ZIP codes in the northeast US frequently start w/ '0' or '00',
   which would be dropped off the front when you show them. People in
   Mass. frequently find it annoying to get mail to zip code '315', rather
   then 00315. Using VARCHAR() also gives you some breathing room
   if you add non-US postal codes, since practically every country
   uses alphanumeric systems.

Thanks for posting this,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

On Fri, 27 Apr 2001, Phill Kenoyer wrote:

> >    "zip" int8 NOT NULL,
>
>    then 00315. Using VARCHAR() also gives you some breathing room
>    if you add non-US postal codes, since practically every country
>    uses alphanumeric systems.

    I couldn't agree more.  It is very annoying not to be able to input
Brazilian zip codes in US systems.  Even if they accept letters, our system
is in the form 99.999-999 -- that's 10 characters, or 9 if you omit the dot,
but omiting the hyphen is so unusual it goes against the grain.  Even if we
use just the numbers it's still 8 numbers, and most US systems do not accept
more than six, which force us to use, not the first six numbers (that would
be a mistake and then the letter might take weeks more to arrive than usual)
but only the first five numbers - while it isn't a gross error it is still a
serious omission and will delay the correspondence for some days.




--
  _
 / \       Leandro Guimarães Faria Corcete Dutra        +55 (11) 3040 8913
 \ /       Amdocs Brasil Ltda at Tele Danmark                +45 3387 5214
  X      http://geocities.com./lgdutra/       mailto:leandrod@amdocs.com
 / \     Campanha fita ASCII                        mailto:moreno@tdk.dk