[postgis-users] Issue with BOX3D and distance

[Mark Cave-Ayland]

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