[postgis-users] Issue with BOX3D and distance

[Mulone]

Mark Cave-Ayland-3 wrote:
> 
> Mulone wrote:
> 
>> Hi guys,
>> I've managed to make this proximity query work:
>> 
>> SELECT * FROM planet_osm_polygon
>> WHERE 
>> way && setsrid(box3d('BOX3D(-6.216  53.300,-6.220  53.304)'), 4326)
>> AND 
>> distance( transform( GeomFromEWKT('SRID=4326;POINT( -6.218  53.302 0)'),
>> 32661 ),
>> transform( way, 32661 ) ) < 100
>> 
>> where 100 is a distance in meters and the bounding box is expressed in
>> degrees (it's always slightly bigger than what we need to be on the safe
>> side).
>> The point is that despite the use of the index, the query is still too
>> slow
>> for what we need (about 60ms on a very small table).
>> I wonder what the problem is... Maybe we can convert meters to degress in
>> srid 4326 with a reasonable precision and get rid of the transform(),
>> which
>> are probably slowing down everything. Is there a function to do such a
>> conversion directly in the postgis SQL?
>> 
>> Any hints?
> 
> Hi Mulone,
> 
> I think you need to explain more about what you're trying to do here. 
> You mention that 60ms is too long, but with no mention of what 
> performance you are aiming for? Databases are complicated beasts, and 
> the optimiser/planner will do different things depending upon the amount 
> of data in your tables. So the only way to get a feel for real 
> performance is to load in a reasonably large representative dataset and 
> then start digging into performance issues...
> 

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


-- 
View this message in context: http://www.nabble.com/Issue-with-BOX3D-and-distance-tp24699677p24717865.html
Sent from the PostGIS - User mailing list archive at Nabble.com.