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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
--
_ | _
(_()(|('.|)('||.|()|`|(
From | Date | Subject | |
---|---|---|---|
Next Message | suhail sarwar | 2001-04-27 11:57:50 | How to delete a table in Postgres ??? |
Previous Message | Shay Moreno | 2001-04-27 07:11:16 | Re: Locking a Table |