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

[David ``Smith'']

On Aug 24, 2012 6:19 AM, "Gregory Williams" <gregory at gregorywilliams.me.uk>
wrote:
> The ST_Transform functions with arguments of 4326 convert it to degrees.
By default osm2pgsql stores data in the Google Spherical Mercator
projection (900913).

I didn't realize that.  In that case, does it even make sense to specify
distance in degrees?  Also, thanks for correcting my faulty recollections
and speculations.

Another thing that occurred to me is that it might make sense to delete
rows with null highway or null maxspeed as an additional preparatory step
(probably just after ensuring all maxspeed values are in km/h).  This would
further shrink the database size, remove the need to filter those objects
out of query results, and I presume speed up the queries because fewer
objects need to be checked.

Warning: the rest of this message gets rambling.  Consider reading it
"extra credit".

On the other hand, what about cases where local streets have maxspeed set,
but not the adjacent main road?  In order to not return too-low speed limit
data for bits of the main road very near intersections with side streets, a
slightly more elaborate algoritm might be called for.  In db preparation,
keep objects with null maxspeed (but you can still drop those with null
highway).  Then when querying, sort descending by highway (motorway > trunk
> primary > secondary > tertiary > unclassified > residential > service =
track = path = living_street > [anything else] *) then descending by
maxspeed, limit 1.  This will mean more significant roads will take
precedence over less significant roads at intersections, even if the major
roads don't have speed limit data.

But what about cases where the major road has a lower maxspeed than the
local road, and both are present in the data?  We'd want the higher (local
road) maxspeed, right?  I think we'd have to compare the highway value of
the highest-maxspeed result to the highest highway value of results with
null maxspeed; if the latter is greater, return null, otherwise return
highest-maxspeed value.  I am not sure how to write that efficiently in SQL.

* The highway value ordering will have to be set up in the db prep stage
along with deleting unneeded objects and normalizing maxspeed values.  I
don't know for sure if there's an elegant way to do that in SQL; if not,
the highway word values would have to be replaced with numbers representing
their significance.  Also, it's possible other OSM contributors may
disagree with the order of precedence I've specified.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/newbies/attachments/20120824/0a2603fa/attachment.html>