[OSM-newbies] Howto: Query to get max speed limit of nearby road/highway

[Phil! Gold]

* René Fournier <m5 at renefournier.com> [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.