[postgis-users] Issue with BOX3D and distance

[Mark Cave-Ayland]

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...


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