[postgis-users] within_distance function

[Obe, Regina]

On further inspection, I realized my within_distance is not using the gist index and that the gain over the just distance check was because of the efficiency of extent comparison instead of distance comparison.
 
I suppose there is no way to trick the optimizer into using an index with the function call.  Something like a macro of some sort so it macro replaces my shorter statement with the more efficient longer statement?

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Obe, Regina
Sent: Fri 2/9/2007 3:32 PM
To: PostGIS Users Discussion
Subject: [postgis-users] within_distance function


I was hoping to simplify some of my distance queries by creating a within_distance function which looks like
 
CREATE OR REPLACE FUNCTION within_distance(geom1 geometry, geom2 geometry, dist double precision)
  RETURNS boolean AS
$BODY$
BEGIN
 return  (expand(geom1, dist) && geom2 AND distance(geom1, geom2) <= dist) ;
END;$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;
 
Upon testing this I noticed the following observations
 
SELECT l.pid, l.the_geom
FROM landparcels l , landparcels l2
WHERE l2.pid = '1803570000' and within_distance(l.the_geom, l2.the_geom, 1000) ;
 
runs in 1610ms and returns 679 rows
---------------------------------------------------------------------------
SELECT l.pid, l.the_geom
FROM landparcels l, landparcels l2
WHERE l2.pid = '1803570000' and (expand(l.the_geom, 1000) && l2.the_geom AND distance(l.the_geom, l2.the_geom) <= 1000)
 
runs in 609 ms and returns 679 rows
 
--------------------------------------------------------------------------
SELECT l.pid, l.the_geom
FROM landparcels l, landparcels l2
WHERE l2.pid = '1803570000' and (distance(l.the_geom, l2.the_geom) <= 1000)
 
runs in 5437 ms and returns 679 rows
 
-----
I ran the test a couple of times with similar results.  So apparently as far as I can tell, my function is probably using my gist indexes, but why is it 1/2 the speed of the regular expand call?
 
 

________________________________



The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended solely for the
addressee. If you received this in error, please contact the sender
and delete the material from any computer.


-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 6261 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070209/17d640f6/attachment.bin>