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: 20010427004456.I27899@c0de.net (view raw or flat)
Thread:
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 */
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.

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

Responses

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-2014 The PostgreSQL Global Development Group