Re: plpgsql locate provider by zip code

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