<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-GB link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>This is probably a question better asked on the dev@ mailing list, but for now I’ll answer here.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>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:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>way maxspeed text linear<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Ignoring the projection issues which David mentions below, this query would do the job:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>select<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> maxspeed<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> from planet_osm_line<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> where highway is not null<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> and ST_DWithin(ST_SetSRID(ST_Point(1.0753, 51.2817),4326), ST_Transform(way, 4326), 0.00013)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> order by<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> ST_Distance(ST_SetSRID(ST_Point(1.0753, 51.2817),4326), ST_Transform(way, 4326))<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> limit 1<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Replace the longitude and latitude values passed to the two ST_Point functions as appropriate.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>The ST_Transform functions with arguments of 4326 convert it to degrees. By default osm2pgsql stores data in the Google Spherical Mercator projection (900913).<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Gregory<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>PS There are 1.609 km per mile, not the 1.62 that David mentions.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> David ``Smith'' [mailto:vidthekid@gmail.com] <br><b>Sent:</b> 23 August 2012 23:14<br><b>To:</b> newbies@openstreetmap.org<br><b>Subject:</b> Re: [OSM-newbies] Howto: Query to get max speed limit of nearby road/highway<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p>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.<o:p></o:p></p><p>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.<o:p></o:p></p><p>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. <o:p></o:p></p><div><p class=MsoNormal>On Aug 23, 2012 5:34 PM, "René Fournier" <<a href="mailto:m5@renefournier.com">m5@renefournier.com</a>> wrote:<o:p></o:p></p><p class=MsoNormal>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!<br><br>...Rene<br>_______________________________________________<br>newbies mailing list<br><a href="mailto:newbies@openstreetmap.org">newbies@openstreetmap.org</a><br><a href="http://lists.openstreetmap.org/listinfo/newbies" target="_blank">http://lists.openstreetmap.org/listinfo/newbies</a><o:p></o:p></p></div></div></div></body></html>