Finally I was able to import itay.osm with osm2po and to get right results from routing query.<div><br></div><div>Something was wrong in the pgrouting installation.</div><div><br></div><div>Thanks to all anyway.</div><div>
<br></div><div>Just some little adjustment to the column names for using osm importad data (by osm2po) with pgRouting directly</div><div><br></div><div><font class="Apple-style-span" face="'courier new', monospace">id -> gid</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace">ways_geom -> the_geom</font></div><div><br></div><div>and (obviously) use the cost column as <font class="Apple-style-span" face="'courier new', monospace">cost</font> in the wrapper functions.</div>
<div><br></div><div><br></div><div><br><div class="gmail_quote">2011/1/20 lorenzo amato <span dir="ltr"><<a href="mailto:lorenzo.amato@geosdi.org">lorenzo.amato@geosdi.org</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Hi Daniel, hi list<div><br></div><div>I noticed another strange behaviour:</div><div><br></div><div>On the same Postgres/postGIS + pgRouting I have 2 database:</div><div><ol><li>prov_roma_osm (created using osm2pgrouting)</li>
<li>osm2po (created using osm2po)</li></ol><div>Working on the first db the routing query go right (also with the wrapper function) presenting the consecutive rows (not ordered by id).</div><div>Working with the second one the routing quey give the strange result (ordering the rows by id) I mensioned in the previous emails.</div>
<div><br></div><div>Maybe something depends on the data?</div><div><br></div><div>I'm going to do some other test, but if you have any suggestion I'll appreciate it :)</div><div><br></div><font color="#888888"><div>
Lorenzo</div></font><div><div></div><div class="h5"><br><div class="gmail_quote">
2011/1/20 lorenzo amato <span dir="ltr"><<a href="mailto:lorenzo.amato@geosdi.org" target="_blank">lorenzo.amato@geosdi.org</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi Daniel,<div>thanks for the quick replay..</div><div><br></div><div>.. but the output is the same.<br><br></div><div><div>osm2po=# SELECT id, gid, AsText(the_geom) from astar_sp_delta('osm_topo',723,6610,0.1);</div>
<div>LOG: statement: SELECT id, gid, AsText(the_geom) from astar_sp_delta('osm_topo',723,6610,0.1);</div><div> id | gid | astext </div>
<div>----+------+-------------------------------------------------------------------------------------------------------------------------------</div><div> 1 | 682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))</div>
<div> 2 | 683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))</div><div> 3 | 685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))</div><div>
4 | 686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))</div>
<div> 5 | 693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))</div><div> 6 | 696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))</div><div> 7 | 697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))</div>
<div> 8 | 7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))</div><div> 9 | 7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))</div><div> 10 | 7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))</div>
<div>(10 rows)</div><div><br></div><div>osm2po=# SELECT id, gid, AsText(the_geom) from astar_sp_delta('osm_topo',723,6610,0.1) ORDER BY id;</div><div>LOG: statement: SELECT id, gid, AsText(the_geom) from astar_sp_delta('osm_topo',723,6610,0.1) ORDER BY id;</div>
<div> id | gid | astext </div><div>----+------+-------------------------------------------------------------------------------------------------------------------------------</div>
<div> 1 | 682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))</div><div> 2 | 683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))</div><div> 3 | 685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))</div>
<div> 4 | 686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))</div><div> 5 | 693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))</div>
<div> 6 | 696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))</div><div> 7 | 697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))</div><div> 8 | 7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))</div>
<div> 9 | 7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))</div><div> 10 | 7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))</div><div>(10 rows)</div></div><div>
<br></div><div><br><div class="gmail_quote">2011/1/19 Daniel Kastl <span dir="ltr"><<a href="mailto:daniel@georepublic.de" target="_blank">daniel@georepublic.de</a>></span><div><div></div><div><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi Lorenzo,<div><br></div><div>Can you try to also output "id" with "ORDER BY id"</div><div><span style="font-family:'courier new', monospace">SELECT id, gid, AsText(the_geom) from astar_sp_delta('osm_topo',723,6610,0.1) ORDER BY id;</span></div>
<div><br>This "id" should be in the right order ... but I'm not 100% sure this was added to all wrapper functions. Can you try once?</div><div><br></div><div>Daniel</div><div><br></div><div><br></div><div><br>
<div class="gmail_quote">2011/1/20 lorenzo amato <span dir="ltr"><<a href="mailto:lorenzo.amato@geosdi.org" target="_blank">lorenzo.amato@geosdi.org</a>></span><div><div></div><div><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi List<div><br></div><div>before import the whole italy.osm with osm2po I'm doing some test with a smaller portion of data.</div><div><br></div><div>I notice a strange behaviour:</div><div><br></div><div>If I use the core A* function I get a correct resultset (i.e. multininestrings are consecutive)</div>
<div><br></div><div>If I use the wrapper function astar_sp_delta I get a wrong resultset (i.e. multilinestring ordered by gid and not consecutive)</div><div><br></div><div>This not affects the rendering of the whole route, but makes impossible other operations like the driving direction calculation, tha needs to compare two consecutive rows of the result at time.</div>
<div><br></div><div><b>Any idea on why this appen?</b></div><div><br></div><div>This is an example to understand the different results:</div><div><font face="'courier new', monospace"><br>
</font></div><div><font face="'courier new', monospace"><br></font></div><div><font face="'courier new', monospace">osm2po=# SELECT * from shortest_path_astar('select gid as id, source, target, length as cost, x1,y1,x2,y2 from osm_topo',723,6610,false,false);</font></div>
<div><font face="'courier new', monospace">LOG: statement: SELECT * from shortest_path_astar('select gid as id, source, target, length as cost, x1,y1,x2,y2 from osm_topo',723,6610,false,false);</font></div>
<div><font face="'courier new', monospace"> vertex_id | edge_id | cost </font></div><div><font face="'courier new', monospace">-----------+---------+----------</font></div>
<div><font face="'courier new', monospace"> 723 | 683 | 0.006361</font></div><div><font face="'courier new', monospace"> 722 | 682 | 0.000656</font></div>
<div><font face="'courier new', monospace"> 721 | 696 | 9.3e-05</font></div><div><font face="'courier new', monospace"> 732 | 697 | 0.000377</font></div>
<div><font face="'courier new', monospace"> 731 | 693 | 0.000575</font></div><div><font face="'courier new', monospace"> 726 | 685 | 0.00025</font></div>
<div><font face="'courier new', monospace"> 727 | 686 | 0.000927</font></div><div><font face="'courier new', monospace"> 724 | 7820 | 0.000661</font></div>
<div><font face="'courier new', monospace"> 2333 | 7819 | 0.003565</font></div><div><font face="'courier new', monospace"> 976 | 7818 | 0.000274</font></div>
<div><font face="'courier new', monospace"> 6610 | -1 | 0</font></div><div><font face="'courier new', monospace">(11 rows)</font></div><div>
<font face="'courier new', monospace"><br></font></div><div><font face="'courier new', monospace">osm2po=# SELECT gid, AsText(the_geom) from astar_sp_delta('osm_topo',723,6610,0.1);</font></div>
<div><font face="'courier new', monospace">LOG: statement: SELECT gid, AsText(the_geom) from astar_sp_delta('osm_topo',723,6610,0.1);</font></div><div><font face="'courier new', monospace"> gid | astext </font></div>
<div><font face="'courier new', monospace">------+-------------------------------------------------------------------------------------------------------------------------------</font></div>
<div><font face="'courier new', monospace"> 682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))</font></div><div><font face="'courier new', monospace"> 683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))</font></div>
<div><font face="'courier new', monospace"> 685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))</font></div><div><font face="'courier new', monospace"> 686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))</font></div>
<div><font face="'courier new', monospace"> 693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))</font></div><div><font face="'courier new', monospace"> 696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))</font></div>
<div><font face="'courier new', monospace"> 697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))</font></div><div><font face="'courier new', monospace"> 7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))</font></div>
<div><font face="'courier new', monospace"> 7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))</font></div><div><font face="'courier new', monospace"> 7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))</font></div>
<div><font face="'courier new', monospace">(10 rows)</font></div><div><font face="'courier new', monospace"> </font><br><br><div class="gmail_quote">2011/1/19 lorenzo amato <span dir="ltr"><<a href="mailto:lorenzo.amato@geosdi.org" target="_blank">lorenzo.amato@geosdi.org</a>></span><div>
<div></div><div><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Richard<div>Thanks very much for your suggestion.<div><br></div><div>I'mk going to test osm2po for creating pgRouting compatible tables and it seems great.</div>
<div><br></div><div>I will post to the list some more considerations at the end of my tests.</div>
<div><br></div><div>Lorenzo</div><div><br><div class="gmail_quote">2011/1/18 Richard Marsden <span dir="ltr"><<a href="mailto:winwaed@gmail.com" target="_blank">winwaed@gmail.com</a>></span><div><div></div><div>
<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF"><div>Try the osm2po program instead. This can handle large files, and recently I was able to use it to import planet.osm. Osm2po create an SQL script which you then execute. Planet.osm took a few days to import - Italy should be a lot quicker!</div>
<div><br></div><div>Search the archives of this list server - I had a couple of queries which were quickly answered. They may help you, especially if you are new to PostGis and pgRouting (as I am).</div><div><br></div><div>
Richard.</div><div><div></div><div><div><br></div><div><br></div><div><br>On Jan 18, 2011, at 10:42 AM, lorenzo amato <<a href="mailto:lorenzo.amato@geosdi.org" target="_blank">lorenzo.amato@geosdi.org</a>> wrote:<br>
<br></div><div></div><blockquote type="cite"><div><span style="font-family:arial, sans-serif;font-size:13px;border-collapse:collapse">Hi list<div>I'm trying to import a large osm file (italy.osm from cloudmade) into postgis using the osm2pgrouting tool without success.</div>
<div>I read somewhere in the list (or in the wikies) that osm2pgrouting uses a large amount of memory. </div><div><br></div><div>So i tried to lounch osm2pgrouting on a workstation with 32G RAM ... without success.</div>
<div>
<br></div><div>I also found this thread:</div><div><br></div><div><a href="http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html" style="color:rgb(64, 100, 128)" target="_blank"></a><a href="http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html" target="_blank">http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html</a></div>
<div><br></div><div><br></div><div>and this wiki page</div><div><br></div><div><a href="http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html" style="color:rgb(64, 100, 128)" target="_blank"></a><a href="http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html" target="_blank">http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html</a></div>
<div><br></div><div>but some errors occurred whan I tryed to apply the patch.</div><div><br></div><div>Any help?</div></span><div><br></div>Or any other way to import large OSM data for routing?<br clear="all"><br>-- <br>
Lorenzo Amato<br><a href="mailto:lorenzo.amato@geosdi.org" target="_blank"></a><a href="mailto:lorenzo.amato@geosdi.org" target="_blank">lorenzo.amato@geosdi.org</a><br><a href="mailto:lorenzotlc@gmail.com" target="_blank"></a><a href="mailto:lorenzotlc@gmail.com" target="_blank">lorenzotlc@gmail.com</a><br>
<a href="mailto:lorenzo.amato@nsdi.it" target="_blank"></a><a href="mailto:lorenzo.amato@nsdi.it" target="_blank">lorenzo.amato@nsdi.it</a><br>
<br>Consiglio Nazionale delle Ricerche<br>Istituto di Metodologie per l'Analisi Ambientale - geoSDI<br>
</div></blockquote></div></div><blockquote type="cite"><div><span>_______________________________________________</span><br><span>Pgrouting-users mailing list</span><br><span><a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a></span><br>
<span><a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a></span><br></div></blockquote></div><br>_______________________________________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
<br></blockquote></div></div></div><div><div></div><div><br><br clear="all"><br>-- <br>Lorenzo Amato<br><a href="mailto:lorenzo.amato@geosdi.org" target="_blank">lorenzo.amato@geosdi.org</a><br><a href="mailto:lorenzotlc@gmail.com" target="_blank">lorenzotlc@gmail.com</a><br>
<a href="mailto:lorenzo.amato@nsdi.it" target="_blank">lorenzo.amato@nsdi.it</a><br>
<br>Consiglio Nazionale delle Ricerche<br>Istituto di Metodologie per l'Analisi Ambientale - geoSDI<br>
</div></div></div></div>
</blockquote></div></div></div><div><div></div><div><br><br clear="all"><br>-- <br>Lorenzo Amato<br><a href="mailto:lorenzo.amato@geosdi.org" target="_blank">lorenzo.amato@geosdi.org</a><br><a href="mailto:lorenzotlc@gmail.com" target="_blank">lorenzotlc@gmail.com</a><br>
<a href="mailto:lorenzo.amato@nsdi.it" target="_blank">lorenzo.amato@nsdi.it</a><br>
<br>Consiglio Nazionale delle Ricerche<br>Istituto di Metodologie per l'Analisi Ambientale - geoSDI<br>
</div></div></div>
<br>_______________________________________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
<br></blockquote></div></div></div><br><br clear="all"><br>-- <br><span style="font-family:arial, sans-serif;font-size:13px;border-collapse:collapse">Georepublic UG & Georepublic Japan<br>eMail: <a href="mailto:daniel.kastl@georepublic.de" style="color:rgb(66, 99, 171)" target="_blank">daniel.kastl@georepublic.de</a><br>
Web: <a href="http://georepublic.de/" style="color:rgb(66, 99, 171)" target="_blank">http://georepublic.de</a></span><br>
</div>
<br>_______________________________________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
<br></blockquote></div></div></div><div><div></div><div><br><br clear="all"><br>-- <br>Lorenzo Amato<br><a href="mailto:lorenzo.amato@geosdi.org" target="_blank">lorenzo.amato@geosdi.org</a><br><a href="mailto:lorenzotlc@gmail.com" target="_blank">lorenzotlc@gmail.com</a><br>
<a href="mailto:lorenzo.amato@nsdi.it" target="_blank">lorenzo.amato@nsdi.it</a><br>
<br>Consiglio Nazionale delle Ricerche<br>Istituto di Metodologie per l'Analisi Ambientale - geoSDI<br>
</div></div></div>
</blockquote></div><br><br clear="all"><br>-- <br>Lorenzo Amato<br><a href="mailto:lorenzo.amato@geosdi.org" target="_blank">lorenzo.amato@geosdi.org</a><br><a href="mailto:lorenzotlc@gmail.com" target="_blank">lorenzotlc@gmail.com</a><br>
<a href="mailto:lorenzo.amato@nsdi.it" target="_blank">lorenzo.amato@nsdi.it</a><br>
<br>Consiglio Nazionale delle Ricerche<br>Istituto di Metodologie per l'Analisi Ambientale - geoSDI<br>
</div></div></div>
</blockquote></div><br><br clear="all"><br>-- <br>Lorenzo Amato<br><a href="mailto:lorenzo.amato@geosdi.org">lorenzo.amato@geosdi.org</a><br><a href="mailto:lorenzotlc@gmail.com">lorenzotlc@gmail.com</a><br><a href="mailto:lorenzo.amato@nsdi.it">lorenzo.amato@nsdi.it</a><br>
<br>Consiglio Nazionale delle Ricerche<br>Istituto di Metodologie per l'Analisi Ambientale - geoSDI<br>
</div>