Skip site navigation (1) Skip section navigation (2)

Re: plpgsql locate provider by zip code

From: Joel Burton <jburton(at)scw(dot)org>
To: Phill Kenoyer <phill(at)kenoyer(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: plpgsql locate provider by zip code
Date: 2001-04-27 15:00:45
Message-ID: Pine.LNX.4.21.0104271057510.17913-100000@olympus.scw.org (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington


In response to

pgsql-novice by date

Next:From: D. DucciniDate: 2001-04-27 15:06:26
Subject: Re: How to delete a table in Postgres ???
Previous:From: Joel BurtonDate: 2001-04-27 14:52:50
Subject: Re: How to delete a table in Postgres ???

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group