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;
