[postgis-users] within_distance function

[Paul Ramsey]

Interesting. I guess the optimizer has an easier time bringing the  
SQL function into the plan.

Now, to be "standards compliant" you should rename your function  
"DWithin()". Should we include such a thing in the main distro? Seems  
a pretty common construction in spatial SQL.

I have often wondered about the wisdom of wrapping the spatial  
booleans in appropriate SQL to auto-generate the necessary index  
calls. On the one hand it makes life easier and more automagic for  
new users.  On the other hand, explicitness of intent is lost for  
others.

P


On 12-Feb-07, at 5:54 AM, Obe, Regina wrote:

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