[pgrouting-users] Import large OSM data for routing
lorenzo amato
lorenzo.amato at geosdi.org
Fri Jan 21 10:33:33 EST 2011
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 at geosdi.org>
> Hi Daniel, hi list
>
> I noticed another strange behaviour:
>
> On the same Postgres/postGIS + pgRouting I have 2 database:
>
> 1. prov_roma_osm (created using osm2pgrouting)
> 2. 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 at 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 at 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 at 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 at 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 at 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 at 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>
>>>>>> 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>
>>>>>> 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 at geosdi.org>lorenzo.amato at geosdi.org
>>>>>> <lorenzotlc at gmail.com>lorenzotlc at gmail.com
>>>>>> <lorenzo.amato at nsdi.it>lorenzo.amato at nsdi.it
>>>>>>
>>>>>> Consiglio Nazionale delle Ricerche
>>>>>> Istituto di Metodologie per l'Analisi Ambientale - geoSDI
>>>>>>
>>>>>> _______________________________________________
>>>>>> Pgrouting-users mailing list
>>>>>> Pgrouting-users at lists.osgeo.org
>>>>>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Pgrouting-users mailing list
>>>>>> Pgrouting-users at lists.osgeo.org
>>>>>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Lorenzo Amato
>>>>> lorenzo.amato at geosdi.org
>>>>> lorenzotlc at gmail.com
>>>>> lorenzo.amato at nsdi.it
>>>>>
>>>>> Consiglio Nazionale delle Ricerche
>>>>> Istituto di Metodologie per l'Analisi Ambientale - geoSDI
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Lorenzo Amato
>>>> lorenzo.amato at geosdi.org
>>>> lorenzotlc at gmail.com
>>>> lorenzo.amato at nsdi.it
>>>>
>>>> Consiglio Nazionale delle Ricerche
>>>> Istituto di Metodologie per l'Analisi Ambientale - geoSDI
>>>>
>>>> _______________________________________________
>>>> Pgrouting-users mailing list
>>>> Pgrouting-users at lists.osgeo.org
>>>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>>>
>>>>
>>>
>>>
>>> --
>>> Georepublic UG & Georepublic Japan
>>> eMail: daniel.kastl at georepublic.de
>>> Web: http://georepublic.de
>>>
>>> _______________________________________________
>>> Pgrouting-users mailing list
>>> Pgrouting-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>>
>>>
>>
>>
>> --
>> Lorenzo Amato
>> lorenzo.amato at geosdi.org
>> lorenzotlc at gmail.com
>> lorenzo.amato at nsdi.it
>>
>> Consiglio Nazionale delle Ricerche
>> Istituto di Metodologie per l'Analisi Ambientale - geoSDI
>>
>
>
>
> --
> Lorenzo Amato
> lorenzo.amato at geosdi.org
> lorenzotlc at gmail.com
> lorenzo.amato at nsdi.it
>
> Consiglio Nazionale delle Ricerche
> Istituto di Metodologie per l'Analisi Ambientale - geoSDI
>
--
Lorenzo Amato
lorenzo.amato at geosdi.org
lorenzotlc at gmail.com
lorenzo.amato at nsdi.it
Consiglio Nazionale delle Ricerche
Istituto di Metodologie per l'Analisi Ambientale - geoSDI
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110121/84865164/attachment-0001.html
More information about the Pgrouting-users
mailing list