[postgis-users] Issue with BOX3D and distance

[nicklas.aven at jordogskog.no]

I think you should look at st_dwithin instead of distance. st_dwithin will will first compare boundingboxes wich st_distance or distance have no use of.st_dwithin uses the boundingboxes for each geometriy. Then the performance is depending on how many vertexes there is in your geometries. Where the boundingboxes is not used, postgis will have to calculate and compare the distances between every vertex and your point. But st_dwithin should help a lot. then you won't need your box-calculation. /Nicklas  2009-07-29 Mark Cave-Ayland wrote:

Mulone wrote:
>
>> I'm trying to get all the geometries within a certain radius from a point.
>> Given that distance doesn't use the index, I added the && BBox operator to
>> narrow down the problem to a smaller dataset, but the problem is that the
>> radius is expressed in meters and the BBOX in degrees, but I'd like to
>> define them in a consistent way. Besides, working on a very small dataset
>> (about 2000 geometries), I was shocked to see that it takes up to 500ms to
>> perform the query... That's why I started messing with the index.
>> 
>> Mulone
>
>Okay, this is a fairly standard scenario. What normally happens in a 
>query similar to yours is that the && operator pulls matches directly 
>from the index, the results of which are then run through the distance() 
>function. (Incidentally, I notice you are still using the non ST_ prefix 
>functions in your queries - you probably want to change this given that 
>these are deprecated)
>
>So I suspect a large amount of it comes down to how do you calculate 
>your BOX3D here:
>
>way && setsrid(box3d('BOX3D(-6.216 53.300,-6.220 53.304)'), 4326)
>
>If you can reduce the size of the BOX3D so that it exactly matches your 
>area of interest, then that should give you the best overall performance.
>
>
>HTH,
>
>Mark.
>
>-- 
>Mark Cave-Ayland - Senior Technical Architect
>PostgreSQL - PostGIS
>Sirius Corporation plc - control through freedom
>http://www.siriusit.co.uk
>t: +44 870 608 0063
>_______________________________________________
>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/20090729/27a29ed7/attachment.html>