<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;}
/* List Definitions */
@list l0
{mso-list-id:1741830715;
mso-list-template-ids:1130763894;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></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 lang=EN-US style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>Stéphane,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>Yes, you’re right in your analysis of how the co-ordinates are stored in the slim mode tables. This’ll help confirm it for you:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>Executing this query against your Postgres database:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>select<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>lon,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>lat,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>ST_X(ST_Transform(ST_SetSRID(ST_Point(lon / 100, lat / 100),900913),4326)),<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>ST_Y(ST_Transform(ST_SetSRID(ST_Point(lon / 100, lat / 100),900913),4326))<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>from planet_osm_nodes where id = 19;<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>Yields:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>Lat;lon;st_x;st_y<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>679034492;-2304090;-0.206971841461138;51.9458701799331<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>Request the same node from the API:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'><a href="http://api.openstreetmap.org/api/0.6/node/19"><span style='color:windowtext'>http://api.openstreetmap.org/api/0.6/node/19</span></a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>Yields:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>…<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'><node id="19" … lat="51.9458753" lon="-0.20698" …><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>…<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif"'>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><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"'> Stéphane Henriod [mailto:s@henriod.info] <br><b>Sent:</b> 06 September 2012 06:56<br><b>To:</b> Ramas<br><b>Cc:</b> dev@openstreetmap.org<br><b>Subject:</b> Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-bottom:12.0pt'>Hi again everybody!<br><br>I am trying to implement the post-processing solution: removing all records in <i>planet_osm_ways</i> that are completely outside my bounding box. For this I first need to find out all the nodes outside the bounding box...<br><br>The problem is that the x and y coordinates of <i>planet_osm_nodes</i> seem to be plenty wrong. My first record, for instance, has<br>lat = 450768421<br>lon = 796483777<br><br>I assume that these are in srid 900913 but, if they are, they are way outside the limits of the 900913 srs. Actually, I just realised that they are systematically wrong by a factor 100. The correct coordinates should be something like:<br>lat = 4507684<br>lon = 7964837<br><br>Have I done something wrong? or is there a known (or unknown?) bug with osm2pgsql? The geometries of the "useful" tables (line, point, polygon, roads) seem correct, but the x/y columns of the temporary tableshave this problem...<br><br>Cheers<br><br>Stéphane<br clear=all>--<br>"Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux" -- Albert Einstein<br><br>"A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you." -- Nicolas Bouvier<br><br>Photos de voyages, photos de montagne: <a href="http://www.henriod.info/" target="_blank">http://www.henriod.info</a> <br><br><br><o:p></o:p></p><div><p class=MsoNormal>On Wed, Sep 5, 2012 at 5:25 PM, Ramas <<a href="mailto:ieskok@ramuno.lt" target="_blank">ieskok@ramuno.lt</a>> wrote:<o:p></o:p></p><p class=MsoNormal>You can check tool osmupdate which has option to clip data outside boundaries.<o:p></o:p></p><div><p class=MsoNormal style='margin-bottom:12.0pt'><a href="http://wiki.openstreetmap.org/wiki/Osmupdate#Applying_Geographical_Borders" target="_blank">http://wiki.openstreetmap.org/wiki/Osmupdate#Applying_Geographical_Borders</a><o:p></o:p></p><div><p class=MsoNormal>On 5 September 2012 16:06, Stéphane Henriod <<a href="mailto:s@henriod.info" target="_blank">s@henriod.info</a>> wrote:<o:p></o:p></p><div><p class=MsoNormal>thanks for your anwers!<br><br>But it looks like I have a problem now...<o:p></o:p></p><ol start=1 type=1><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1'>I need to keep the tables ways, rels and nodes because of the diff updates<o:p></o:p></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1'>Those 3 tables will continue growing up with each new diff, until I reach the storage capacity of the server<o:p></o:p></li></ol><p>The only solution I see is a post-processing that will erase from PostGIS all the nodes lying outside my bounding box + all the ways using one of thses nodes + all the relations using one of thses nodes or one of these ways.<o:p></o:p></p><p>Except if anyone else has a magical solution...<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p></div></div></body></html>