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:[email protected]]
Sent: 23 August 2012 23:14
To: [email protected]
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" <[email protected]> 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
[email protected]
http://lists.openstreetmap.org/listinfo/newbies
_______________________________________________
newbies mailing list
[email protected]
http://lists.openstreetmap.org/listinfo/newbies
