* René Fournier <[email protected]> [2012-08-27 14:44 -0600]:
> On 2012-08-27, at 1:55 PM, René Fournier wrote:
> > SELECT highway, maxspeed , ST_AsText(way) as polyline
> > FROM planet_osm_line
> > WHERE ST_DWithin(ST_SetSRID(ST_Point(-79.587976, 43.579156),4326),
> > ST_Transform(way, 4326), 0.00013)
> > AND highway is not null
> > ORDER BY ST_Distance(ST_SetSRID(ST_Point(-79.587976,
> > 43.579156),4326), ST_Transform(way, 4326)) LIMIT 5
>
> Removing ST_Transform makes the query 100 times faster (about 5 ms). But...
> should I be concerned about the projection issues at ~10 meters? What would
> you suggest for making the query fast AND accurate?
Try using ST_Transform on your point rather than on the way field. That
should let PostgreSQL use the index on the field. If it's still not using
the index, add a bbox predicate:
SELECT highway, maxspeed , ST_AsText(way) as polyline
FROM planet_osm_line
WHERE ST_DWithin(ST_Transform(ST_SetSRID(ST_Point(-79.587976, 43.579156),
4326), 900913), way, 10)
AND ST_Buffer(ST_Transform(ST_SetSRID(ST_Point(-79.587976, 43.579156),
4326), 900913), 10) && way
AND highway is not null
ORDER BY ST_Distance(ST_SetSRID(ST_Transform(ST_Point(-79.587976,
43.579156), 4326), 900913), way) LIMIT 5
I think that using ST_DWithin should implicitly add that second predicate,
though.
_______________________________________________
newbies mailing list
[email protected]
http://lists.openstreetmap.org/listinfo/newbies
