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

[David ``Smith'']

The first question is, does your postGIS database contain the speed limit
information?  I'm assuming you used a utility called osm2pgsql to import
the data. That program uses a plaintext file called something like
style.txt which tells it which OSM tags are important for the database; you
want to make sure "maxspeed" is in there, which it probably isn't by
default; the only other tag you really need for this purpose is "highway",
and the rest can probably go, to keep your database smaller.  I hope you
already figured this out.

Now your database should have a table (among others) called something like
lines, whose columns should include geometry, highway, and maxspeed.  The
values in the highway and maxspeed columns are strings, though the maxspeed
values should "look like" numbers.  It would be prudent to find maxspeed
values of the form "## mph", extract the numerical part, and multiply by
1.62, then replace the original string value with that result.  Probably
not necessary, but possibly still prudent, discard any other maxspeed
values that contain nonnumeric characters.  Now all maxspeed values are
strings that look like numbers, and are expressed in km/h.

I imagine the postgresql functions include a way to query which linear
features come within a certain distance of a given point, but I don't know
the name or syntax or even that it exists with certainty.  Unless you want
to deal with reprojection at multiple places in the project, the distance
will have to be specified in degrees (probably about 0.00013 degrees for
your application), and your queries will likely be more sensitive to
east-west distance than north-south distance (by a factor of about 1.4 for
populated areas of Canada, tolerable in my opinion).  You'll also want to
filter out results whose maxspeed or highway columns are null, and then
return the highest maxspeed value remaining in the results.
On Aug 23, 2012 5:34 PM, "René Fournier" <m5 at renefournier.com> wrote:

> So... I've imported 27 GB of canada.osm into PostGIS 1.5.4. Now I am
> trying to construct a query that, given a point (latitude, longitude),
> finds the max speed limit of the road it is on (or within 10 meters of —
> otherwise, return nothing). Can anyone help? I'm a total OSM noob, and have
> no idea how the data is structured or how best to extract this one piece of
> information. Thanks!
>
> ...Rene
> _______________________________________________
> newbies mailing list
> newbies at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/newbies
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/newbies/attachments/20120823/9004baca/attachment.html>