[postgis-users] within_distance function

[Paul Ramsey]

Perhaps doing the function as a SQL function rather than a PL/PgSQL 
function. Shot in the dark.

P

Obe, Regina wrote:
> 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.
> 
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 
> _______________________________________________ postgis-users mailing
> list postgis-users at postgis.refractions.net 
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   pramsey at refractions.net
   Phone: 250-383-3022
   Cell: 250-885-0632