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

[Gregory Williams]

This is probably a question better asked on the dev@ mailing list, but for now I’ll answer here.

 

As David says you’ll need to ensure that you’ve actually imported the maxspeed tag. It isn’t imported by default. Edit the default.style file to include a line like this:

 

way        maxspeed           text        linear

 

Ignoring the projection issues which David mentions below, this query would do the job:

 

select

                maxspeed

                from planet_osm_line

                where highway is not null

                and ST_DWithin(ST_SetSRID(ST_Point(1.0753, 51.2817),4326), ST_Transform(way, 4326), 0.00013)

                order by

                                ST_Distance(ST_SetSRID(ST_Point(1.0753, 51.2817),4326), ST_Transform(way, 4326))

                limit 1

 

Replace the longitude and latitude values passed to the two ST_Point functions as appropriate.

 

The ST_Transform functions with arguments of 4326 convert it to degrees. By default osm2pgsql stores data in the Google Spherical Mercator projection (900913).

 

Gregory

 

PS There are 1.609 km per mile, not the 1.62 that David mentions.

 

From: David ``Smith'' [mailto:vidthekid at gmail.com] 
Sent: 23 August 2012 23:14
To: newbies at openstreetmap.org
Subject: Re: [OSM-newbies] Howto: Query to get max speed limit of nearby road/highway

 

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/20120824/3b213049/attachment-0001.html>