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

plpgsql locate provider by zip code

From: Phill Kenoyer <phill(at)kenoyer(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: plpgsql locate provider by zip code
Date: 2001-04-27 07:44:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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 */
   "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/' LANGUAGE 'c';

  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.phone1, '(121.234,38.7346)'::point <@> location as distance 
from zips z, providers p 
where '(121.234,38.7346)'::point <@> location < 50
and =
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 '
 source_zip alias for $1;
 offset alias for $2;
 source_point point;
 result record;
  -- 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( as name, trim(p.address1)::text as address,
    p.phone1::text as phone, trim( as city,
    round(source_point <@> location)::text as distance
  from zips z, providers p 
  where source_point <@> location < 50 and =
  order by distance
  limit 1, offset;

  -- Return
  if found
    then return || '', '' || result.address || '', '' ||
       || '', '' || || '', '' || 
                result.distance || '' Miles'';
  end if;
  -- if nothing found
  return ''Error: Provider Not Found'';
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.

 _   |       _           


pgsql-novice by date

Next:From: suhail sarwarDate: 2001-04-27 11:57:50
Subject: How to delete a table in Postgres ???
Previous:From: Shay MorenoDate: 2001-04-27 07:11:16
Subject: Re: Locking a Table

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