[pgrouting-users] Import large OSM data for routing
lorenzo amato
lorenzo.amato at geosdi.org
Thu Jan 20 02:55:49 EST 2011
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110120/32b3f175/attachment-0001.html
More information about the Pgrouting-users
mailing list