[postgis-users] within_distance function

[Obe, Regina]

Great suggestion Paul!  My SQL function is running just as fast as the
expand combination in my code. 

My sql function looks like this
CREATE OR REPLACE FUNCTION within_distancesql(geom1 geometry, geom2
geometry, dist double precision)
  RETURNS boolean AS
$BODY$
	SELECT (expand($1, $3) && $2 AND distance($1, $2) <= $3) ;
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

---
First I realized that I had my queries backward and I wasn't taking full
advantage of my indexes because when I switched the order of my query
from

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)

to

SELECT l.pid, l.the_geom
FROM landparcels l, landparcels l2
WHERE l2.pid = '1803570000' and (expand(l2.the_geom, 1000) && l.the_geom
AND distance(l.the_geom, l2.the_geom) <= 1000)

My time went from 609ms to 78ms.  The original with_distance didn't
matter the order of the arguments since presumably it wasn't taking
advantage of the gist index.

When I changed my query to 
SELECT l.pid, l.the_geom
FROM landparcels l , landparcels l2
WHERE l2.pid = '1803570000' and within_distancesql(l2.the_geom,
l.the_geom, 1000) 

My time when to 78ms.   

For the less optimized
SELECT l.pid, l.the_geom
FROM landparcels l , landparcels l2
WHERE l2.pid = '1803570000' and within_distancesql(l.the_geom,
l2.the_geom, 1000) 

time was 609ms

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: Friday, February 09, 2007 6:49 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] within_distance function

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