[postgis-users] Postgres/Postgis query very slow

[Nicklas Avén]

Hallo

Here ST_DFulltwithin will give wrong result. It is easily done to think wrong about this function. It will cause a measuring from the point to the furthest part of the line instead of the closest. In this case it will cause a measure like the first illustration in the documentation of ST_Longestline.
http://postgis.org/documentation/manual-1.5/ST_LongestLine.html

Since one of the geometries is a point it is possible to use ST_DWithin instead. If the point is within, it is also fullywithin.

So, just use ST_DWithin indead of ST_DFullywithin in strk's suggestion. It should be much faster than the buffer solution.


/Nicklas

2010-04-01 strk  wrote:

On Thu, Apr 01, 2010 at 02:35:09AM -0700, iKey wrote:
>> 
>> Hey,
>> I've set up a Postgres database with Postgis support.
>> Now I try to excecute the following query:
>> 
>> SELECT DISTINCT p.*
>> FROM punkte p, lines l
>> WHERE l.tags LIKE '%"highway"="footway"%'
>>     AND CONTAINS(BUFFER(l.the_geom, 500), p.the_geom)
>
>Blind guess:
>
>- Use ST_DFullyWithin instead of buffer (major improvement expected)
>- Use equality operator and an OR for tags rather than like (minor)
>- Avoid the DISTINCT if not needed (minor)
>
>--strk;
>
>  ()   Free GIS & Flash consultant/developer
>  /\   http://strk.keybit.net/services.html
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100401/d4792ce2/attachment.html>