optimizing pl/pgsql function

From: "Uqtous" <Ubqtous(at)hotmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: optimizing pl/pgsql function
Date: 2002-06-25 18:53:40
Message-ID: OE50JGUkBSZ5E8k6kD30001fd97@hotmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have a function that determines the distance between zip codes using
latitude and longitude values. This function is called by a query that
searches for members with x miles of the requesting member. The code works,
but is very slow! Any optimization tips would be appreciated!

I'm running PG 7.1.3...

-- Function: zipdist(int4, int4)
CREATE FUNCTION "zipdist"("int4", "int4") RETURNS "numeric" AS 'DECLARE

from_lat NUMERIC;
from_long NUMERIC;
to_lat NUMERIC;
to_long NUMERIC;
xcoord NUMERIC;
ycoord NUMERIC;

BEGIN

SELECT INTO from_lat tlkpZip.zipLatitude::numeric FROM tlkpZip WHERE
tlkpZip.zipCode=$1;
SELECT INTO from_long tlkpZip.zipLongitude::numeric FROM tlkpZip WHERE
tlkpZip.zipCode=$1;
SELECT INTO to_lat tlkpZip.zipLatitude::numeric FROM tlkpZip WHERE
tlkpZip.zipCode=$2;
SELECT INTO to_long tlkpZip.zipLongitude::numeric FROM tlkpZip WHERE
tlkpZip.zipCode=$2;

xcoord:=(69.1::numeric*(to_lat::numeric-from_lat::numeric)::numeric);
ycoord:=(69.1::numeric*((to_long::numeric-from_long::numeric)::numeric*cos(f
rom_lat/57.3::numeric)::numeric));

RETURN
sqrt((xcoord::numeric*xcoord::numeric)::numeric+(ycoord::numeric*ycoord::num
eric)::numeric)::numeric;

END;' LANGUAGE 'plpgsql';

And the query that calls the function; "12345" is the requesting member's
zip, "5" is the preferred distance, and "1" is the requesting member's ID:

SELECT tblmbr.*
FROM tblmbr
WHERE zipdist(12345,tblmbr.mbrzipcode)<=5
AND tblmbr.mbrid <> 1
ORDER BY tblmbr.mbrusername;

Browse pgsql-novice by date

  From Date Subject
Next Message Uqtous 2002-06-25 21:46:51 optimizing pl/pgsql function
Previous Message Juliet May 2002-06-25 15:49:49 cygwin