[pgrouting-users] Import large OSM data for routing

lorenzo_amato · January 18, 2011, 4:42pm

Hi list
I’m trying to import a large osm file (italy.osm from cloudmade) into postgis using the osm2pgrouting tool without success.
I read somewhere in the list (or in the wikies) that osm2pgrouting uses a large amount of memory.

So i tried to lounch osm2pgrouting on a workstation with 32G RAM … without success.

I also found this thread:

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html

and this wiki page

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html

but some errors occurred whan I tryed to apply the patch.

Any help?

Or any other way to import large OSM data for routing?

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Richard_Marsden · January 18, 2011, 7:01pm

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!

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).

Richard.

On Jan 18, 2011, at 10:42 AM, lorenzo amato <lorenzo.amato@geosdi.org> wrote:

Hi list
I’m trying to import a large osm file (italy.osm from cloudmade) into postgis using the osm2pgrouting tool without success.
I read somewhere in the list (or in the wikies) that osm2pgrouting uses a large amount of memory.

So i tried to lounch osm2pgrouting on a workstation with 32G RAM … without success.

I also found this thread:

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html

and this wiki page

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html

but some errors occurred whan I tryed to apply the patch.

Any help?

Or any other way to import large OSM data for routing?

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

lorenzo_amato · January 19, 2011, 11:05am

Richard
Thanks very much for your suggestion.

I’mk going to test osm2po for creating pgRouting compatible tables and it seems great.

I will post to the list some more considerations at the end of my tests.

Lorenzo

2011/1/18 Richard Marsden <winwaed@gmail.com>

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!

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).

Richard.

On Jan 18, 2011, at 10:42 AM, lorenzo amato <lorenzo.amato@geosdi.org> wrote:

Hi list
I’m trying to import a large osm file (italy.osm from cloudmade) into postgis using the osm2pgrouting tool without success.
I read somewhere in the list (or in the wikies) that osm2pgrouting uses a large amount of memory.

So i tried to lounch osm2pgrouting on a workstation with 32G RAM … without success.

I also found this thread:

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html

and this wiki page

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html

but some errors occurred whan I tryed to apply the patch.

Any help?

Or any other way to import large OSM data for routing?

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

lorenzo_amato · January 19, 2011, 5:31pm

Hi List

before import the whole italy.osm with osm2po I’m doing some test with a smaller portion of data.

I notice a strange behaviour:

If I use the core A* function I get a correct resultset (i.e. multininestrings are consecutive)

If I use the wrapper function astar_sp_delta I get a wrong resultset (i.e. multilinestring ordered by gid and not consecutive)

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.

Any idea on why this appen?

This is an example to understand the different results:

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);
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);
vertex_id | edge_id | cost
-----------±--------±---------
723 | 683 | 0.006361
722 | 682 | 0.000656
721 | 696 | 9.3e-05
732 | 697 | 0.000377
731 | 693 | 0.000575
726 | 685 | 0.00025
727 | 686 | 0.000927
724 | 7820 | 0.000661
2333 | 7819 | 0.003565
976 | 7818 | 0.000274
6610 | -1 | 0
(11 rows)

osm2po=# SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
LOG: statement: SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
gid | astext
------±------------------------------------------------------------------------------------------------------------------------------
682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

2011/1/19 lorenzo amato <lorenzo.amato@geosdi.org>

Richard
Thanks very much for your suggestion.

I’mk going to test osm2po for creating pgRouting compatible tables and it seems great.

I will post to the list some more considerations at the end of my tests.

Lorenzo

2011/1/18 Richard Marsden <winwaed@gmail.com>

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!

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).

Richard.

On Jan 18, 2011, at 10:42 AM, lorenzo amato <lorenzo.amato@geosdi.org> wrote:

Hi list
I’m trying to import a large osm file (italy.osm from cloudmade) into postgis using the osm2pgrouting tool without success.
I read somewhere in the list (or in the wikies) that osm2pgrouting uses a large amount of memory.

So i tried to lounch osm2pgrouting on a workstation with 32G RAM … without success.

I also found this thread:

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html

and this wiki page

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html

but some errors occurred whan I tryed to apply the patch.

Any help?

Or any other way to import large OSM data for routing?

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

dkastl · January 19, 2011, 5:53pm

Hi Lorenzo,

Can you try to also output “id” with “ORDER BY id”
SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;

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?

Daniel

2011/1/20 lorenzo amato <lorenzo.amato@geosdi.org>

Hi List

before import the whole italy.osm with osm2po I’m doing some test with a smaller portion of data.

I notice a strange behaviour:

If I use the core A* function I get a correct resultset (i.e. multininestrings are consecutive)

If I use the wrapper function astar_sp_delta I get a wrong resultset (i.e. multilinestring ordered by gid and not consecutive)

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.

Any idea on why this appen?

This is an example to understand the different results:

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);
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);
vertex_id | edge_id | cost
-----------±--------±---------
723 | 683 | 0.006361
722 | 682 | 0.000656
721 | 696 | 9.3e-05
732 | 697 | 0.000377
731 | 693 | 0.000575
726 | 685 | 0.00025
727 | 686 | 0.000927
724 | 7820 | 0.000661
2333 | 7819 | 0.003565
976 | 7818 | 0.000274
6610 | -1 | 0
(11 rows)

osm2po=# SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
LOG: statement: SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
gid | astext
------±------------------------------------------------------------------------------------------------------------------------------
682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

2011/1/19 lorenzo amato <lorenzo.amato@geosdi.org>

Richard
Thanks very much for your suggestion.

I’mk going to test osm2po for creating pgRouting compatible tables and it seems great.

I will post to the list some more considerations at the end of my tests.

Lorenzo

2011/1/18 Richard Marsden <winwaed@gmail.com>

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!

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).

Richard.

On Jan 18, 2011, at 10:42 AM, lorenzo amato <lorenzo.amato@geosdi.org> wrote:

Hi list
I’m trying to import a large osm file (italy.osm from cloudmade) into postgis using the osm2pgrouting tool without success.
I read somewhere in the list (or in the wikies) that osm2pgrouting uses a large amount of memory.

So i tried to lounch osm2pgrouting on a workstation with 32G RAM … without success.

I also found this thread:

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html

and this wiki page

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html

but some errors occurred whan I tryed to apply the patch.

Any help?

Or any other way to import large OSM data for routing?

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

lorenzo_amato · January 20, 2011, 7:55am

Hi Daniel,
thanks for the quick replay…

… but the output is the same.

osm2po=# SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
LOG: statement: SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
id | gid | astext
----±-----±------------------------------------------------------------------------------------------------------------------------------
1 | 682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
2 | 683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
3 | 685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
4 | 686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
5 | 693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
6 | 696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
7 | 697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
8 | 7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
9 | 7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
10 | 7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

osm2po=# SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;
LOG: statement: SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;
id | gid | astext
----±-----±------------------------------------------------------------------------------------------------------------------------------
1 | 682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
2 | 683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
3 | 685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
4 | 686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
5 | 693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
6 | 696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
7 | 697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
8 | 7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
9 | 7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
10 | 7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

2011/1/19 Daniel Kastl <daniel@georepublic.de>

Hi Lorenzo,

Can you try to also output “id” with “ORDER BY id”
SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;

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?

Daniel

2011/1/20 lorenzo amato <lorenzo.amato@geosdi.org>

Hi List

before import the whole italy.osm with osm2po I’m doing some test with a smaller portion of data.

I notice a strange behaviour:

If I use the core A* function I get a correct resultset (i.e. multininestrings are consecutive)

If I use the wrapper function astar_sp_delta I get a wrong resultset (i.e. multilinestring ordered by gid and not consecutive)

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.

Any idea on why this appen?

This is an example to understand the different results:

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);
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);
vertex_id | edge_id | cost
-----------±--------±---------
723 | 683 | 0.006361
722 | 682 | 0.000656
721 | 696 | 9.3e-05
732 | 697 | 0.000377
731 | 693 | 0.000575
726 | 685 | 0.00025
727 | 686 | 0.000927
724 | 7820 | 0.000661
2333 | 7819 | 0.003565
976 | 7818 | 0.000274
6610 | -1 | 0
(11 rows)

osm2po=# SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
LOG: statement: SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
gid | astext
------±------------------------------------------------------------------------------------------------------------------------------
682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

2011/1/19 lorenzo amato <lorenzo.amato@geosdi.org>

Richard
Thanks very much for your suggestion.

I’mk going to test osm2po for creating pgRouting compatible tables and it seems great.

I will post to the list some more considerations at the end of my tests.

Lorenzo

2011/1/18 Richard Marsden <winwaed@gmail.com>

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!

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).

Richard.

On Jan 18, 2011, at 10:42 AM, lorenzo amato <lorenzo.amato@geosdi.org> wrote:

Hi list
I’m trying to import a large osm file (italy.osm from cloudmade) into postgis using the osm2pgrouting tool without success.
I read somewhere in the list (or in the wikies) that osm2pgrouting uses a large amount of memory.

So i tried to lounch osm2pgrouting on a workstation with 32G RAM … without success.

I also found this thread:

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html

and this wiki page

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html

but some errors occurred whan I tryed to apply the patch.

Any help?

Or any other way to import large OSM data for routing?

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

lorenzo_amato · January 20, 2011, 8:06am

Hi Daniel, hi list

I noticed another strange behaviour:

On the same Postgres/postGIS + pgRouting I have 2 database:

prov_roma_osm (created using osm2pgrouting)
osm2po (created using osm2po)
Working on the first db the routing query go right (also with the wrapper function) presenting the consecutive rows (not ordered by id).
Working with the second one the routing quey give the strange result (ordering the rows by id) I mensioned in the previous emails.

Maybe something depends on the data?

I’m going to do some other test, but if you have any suggestion I’ll appreciate it

Lorenzo

2011/1/20 lorenzo amato <lorenzo.amato@geosdi.org>

Hi Daniel,
thanks for the quick replay…

… but the output is the same.

osm2po=# SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
LOG: statement: SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
id | gid | astext
----±-----±------------------------------------------------------------------------------------------------------------------------------
1 | 682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
2 | 683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
3 | 685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
4 | 686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
5 | 693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
6 | 696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
7 | 697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
8 | 7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
9 | 7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
10 | 7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

osm2po=# SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;
LOG: statement: SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;
id | gid | astext
----±-----±------------------------------------------------------------------------------------------------------------------------------
1 | 682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
2 | 683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
3 | 685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
4 | 686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
5 | 693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
6 | 696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
7 | 697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
8 | 7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
9 | 7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
10 | 7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

2011/1/19 Daniel Kastl <daniel@georepublic.de>

Hi Lorenzo,

Can you try to also output “id” with “ORDER BY id”
SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;

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?

Daniel

2011/1/20 lorenzo amato <lorenzo.amato@geosdi.org>

Hi List

before import the whole italy.osm with osm2po I’m doing some test with a smaller portion of data.

I notice a strange behaviour:

If I use the core A* function I get a correct resultset (i.e. multininestrings are consecutive)

If I use the wrapper function astar_sp_delta I get a wrong resultset (i.e. multilinestring ordered by gid and not consecutive)

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.

Any idea on why this appen?

This is an example to understand the different results:

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);
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);
vertex_id | edge_id | cost
-----------±--------±---------
723 | 683 | 0.006361
722 | 682 | 0.000656
721 | 696 | 9.3e-05
732 | 697 | 0.000377
731 | 693 | 0.000575
726 | 685 | 0.00025
727 | 686 | 0.000927
724 | 7820 | 0.000661
2333 | 7819 | 0.003565
976 | 7818 | 0.000274
6610 | -1 | 0
(11 rows)

osm2po=# SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
LOG: statement: SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
gid | astext
------±------------------------------------------------------------------------------------------------------------------------------
682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

2011/1/19 lorenzo amato <lorenzo.amato@geosdi.org>

Richard
Thanks very much for your suggestion.

I’mk going to test osm2po for creating pgRouting compatible tables and it seems great.

I will post to the list some more considerations at the end of my tests.

Lorenzo

2011/1/18 Richard Marsden <winwaed@gmail.com>

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!

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).

Richard.

On Jan 18, 2011, at 10:42 AM, lorenzo amato <lorenzo.amato@geosdi.org> wrote:

Hi list
I’m trying to import a large osm file (italy.osm from cloudmade) into postgis using the osm2pgrouting tool without success.
I read somewhere in the list (or in the wikies) that osm2pgrouting uses a large amount of memory.

So i tried to lounch osm2pgrouting on a workstation with 32G RAM … without success.

I also found this thread:

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html

and this wiki page

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html

but some errors occurred whan I tryed to apply the patch.

Any help?

Or any other way to import large OSM data for routing?

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

lorenzo_amato · January 21, 2011, 3:33pm

Finally I was able to import itay.osm with osm2po and to get right results from routing query.

Something was wrong in the pgrouting installation.

Thanks to all anyway.

Just some little adjustment to the column names for using osm importad data (by osm2po) with pgRouting directly

id → gid
ways_geom → the_geom

and (obviously) use the cost column as cost in the wrapper functions.

2011/1/20 lorenzo amato <lorenzo.amato@geosdi.org>

Hi Daniel, hi list

I noticed another strange behaviour:

On the same Postgres/postGIS + pgRouting I have 2 database:

prov_roma_osm (created using osm2pgrouting)

osm2po (created using osm2po)
Working on the first db the routing query go right (also with the wrapper function) presenting the consecutive rows (not ordered by id).
Working with the second one the routing quey give the strange result (ordering the rows by id) I mensioned in the previous emails.

Maybe something depends on the data?

I’m going to do some other test, but if you have any suggestion I’ll appreciate it

Lorenzo

2011/1/20 lorenzo amato <lorenzo.amato@geosdi.org>

Hi Daniel,
thanks for the quick replay…

… but the output is the same.

osm2po=# SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
LOG: statement: SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
id | gid | astext
----±-----±------------------------------------------------------------------------------------------------------------------------------
1 | 682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
2 | 683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
3 | 685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
4 | 686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
5 | 693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
6 | 696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
7 | 697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
8 | 7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
9 | 7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
10 | 7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

osm2po=# SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;
LOG: statement: SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;
id | gid | astext
----±-----±------------------------------------------------------------------------------------------------------------------------------
1 | 682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
2 | 683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
3 | 685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
4 | 686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
5 | 693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
6 | 696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
7 | 697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
8 | 7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
9 | 7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
10 | 7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

2011/1/19 Daniel Kastl <daniel@georepublic.de>

Hi Lorenzo,

Can you try to also output “id” with “ORDER BY id”
SELECT id, gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1) ORDER BY id;

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?

Daniel

2011/1/20 lorenzo amato <lorenzo.amato@geosdi.org>

Hi List

before import the whole italy.osm with osm2po I’m doing some test with a smaller portion of data.

I notice a strange behaviour:

If I use the core A* function I get a correct resultset (i.e. multininestrings are consecutive)

If I use the wrapper function astar_sp_delta I get a wrong resultset (i.e. multilinestring ordered by gid and not consecutive)

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.

Any idea on why this appen?

This is an example to understand the different results:

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);
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);
vertex_id | edge_id | cost
-----------±--------±---------
723 | 683 | 0.006361
722 | 682 | 0.000656
721 | 696 | 9.3e-05
732 | 697 | 0.000377
731 | 693 | 0.000575
726 | 685 | 0.00025
727 | 686 | 0.000927
724 | 7820 | 0.000661
2333 | 7819 | 0.003565
976 | 7818 | 0.000274
6610 | -1 | 0
(11 rows)

osm2po=# SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
LOG: statement: SELECT gid, AsText(the_geom) from astar_sp_delta(‘osm_topo’,723,6610,0.1);
gid | astext
------±------------------------------------------------------------------------------------------------------------------------------
682 | MULTILINESTRING((14.9979217 41.9939472,14.997744 41.9942112))
683 | MULTILINESTRING((14.997744 41.9942112,14.9974089 41.9947092,14.9962375 41.9963223,14.9958494 41.9966941))
685 | MULTILINESTRING((14.9982187 41.9934783,14.9982846 41.9933772))
686 | MULTILINESTRING((14.9982846 41.9933772,14.9983547 41.9932913,14.9983794 41.9932546,14.9984662 41.9931742,14.99858 41.993024))
693 | MULTILINESTRING((14.9980506 41.9937596,14.9981486 41.9935518,14.9982187 41.9934783))
696 | MULTILINESTRING((14.9979217 41.9939472,14.9979489 41.9939108))
697 | MULTILINESTRING((14.9979489 41.9939108,14.9980265 41.9938069,14.9980506 41.9937596))
7818 | MULTILINESTRING((14.9998359 41.9912293,14.9997606 41.991339))
7819 | MULTILINESTRING((14.9997606 41.991339,14.9991878 41.9921736,14.9987675 41.9927617))
7820 | MULTILINESTRING((14.9987675 41.9927617,14.99858 41.993024))
(10 rows)

2011/1/19 lorenzo amato <lorenzo.amato@geosdi.org>

Richard
Thanks very much for your suggestion.

I’mk going to test osm2po for creating pgRouting compatible tables and it seems great.

I will post to the list some more considerations at the end of my tests.

Lorenzo

2011/1/18 Richard Marsden <winwaed@gmail.com>

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!

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).

Richard.

On Jan 18, 2011, at 10:42 AM, lorenzo amato <lorenzo.amato@geosdi.org> wrote:

Hi list
I’m trying to import a large osm file (italy.osm from cloudmade) into postgis using the osm2pgrouting tool without success.
I read somewhere in the list (or in the wikies) that osm2pgrouting uses a large amount of memory.

So i tried to lounch osm2pgrouting on a workstation with 32G RAM … without success.

I also found this thread:

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/297.html

and this wiki page

http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/osm2pgroutingPatchToAppendFile.html

but some errors occurred whan I tryed to apply the patch.

Any help?

Or any other way to import large OSM data for routing?

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI

–
Lorenzo Amato
lorenzo.amato@geosdi.org
lorenzotlc@gmail.com
lorenzo.amato@nsdi.it

Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l’Analisi Ambientale - geoSDI