Osm2po Part 2 – PgRouting on OSM the Easy Way
This is the follow up post to “An osm2po Quickstart” which covers loading the OSM network into PostGIS and using the result with pgRouting. After parsing the OSM file, e.g.
C:\Users\Anita\temp\osm2po-4.2.30>java -jar osm2po-core-4.2.30-signed.jar prefix=at "C:\Users\Anita\Geodaten\OpenStreetMap Data\austria.osm.pbf"
you should find a folder with the name of the prefix you chose inside the osm2po folder. It contains a log file which in turn provides a command line template for importing the OSM network into PostGIS, e.g.
INFO commandline template: psql -U [username] -d [dbname] -q -f "C:\Users\Anita\temp\osm2po-4.2.30\at\at_2po_4pgr.sql"
Using this template, we can easily import the .sql file into an exiting database. My pgRouting-enabled database is called wien_ogd.
C:\Users\Anita\temp\osm2po-4.2.30\at>psql -U [username] -d wien_ogd -q -f C:\Users\Anita\temp\osm2po-4.2.30\at\at_2po_4pgr.sql
Now, the data is ready for usage in QGIS:
Using “pgRouting Layer” plugin, it’s now straightforward to calculate shortest paths. I had to apply some changes to the plugin code, so please get the latest version from Github.
Using osm2po turned out to be far less painful than I expected and I hope you’ll find this post useful too.
well this is awesome i have downloaded osm2po for a long time but i don’t know how to configure it on windows well mam can you tell me is this a terminal command C:\Users\Anita\temp\osm2po-4.2.30>java -jar osm2po-core-4.2.30-signed.jar prefix=at “C:\Users\Anita\Geodaten\OpenStreetMap Data\austria.osm.pbf”
Yes, osm2po can be run using the above command in Windows command line.
I’ve been wanting to get around to importing OSM into pgRouting for a while but just never invested the time. Your tutorial couldn’t have made it any easier! Thanks for all the great work.
I need your help if you could please explain this , as i am really close to loading osm data in pg Routhing.
I need to load osm road data using pgRouting and display on openlayer and show the start and end point and give routing suggestions. I have data in .osm format (lets call it sample.osm)
I would appreciate if you could tell me step by step process for getting the data to postGIS to shortest path search for windows. I have read most of the blog and tutorials but still could not figure out.
I really appreciate you time and effort.
It’s all covered in my posts. What exactly is causing you troubles? A step-by-step guide for the whole process would be far out of scope of a comment. If you can tell me where you are stuck, I’ll try to help.
This is great..just what i was looking for..but i have a question..to make pgrouting do you work with polygons or lines?..It may sound foolish but it’s my doubt..
pgRouting works with lines.
It would be great to have your tutorials for google maps styling of osm data updated for this osm2po imported data. Do you have some rules and qgs files you could share? Thanks!
Here you go: https://underdark.wordpress.com/2012/02/15/new-download-style-for-osm2po-layers/
Many thanks for the tutorial, great job!
I have downloaded osm2po and I have managed to import some osm data without any problem. However, I have noticed that the osm_id column doesn’t contain unique ids. That column lists basically the road ids right? So, my question is, is there any way so we can change that and eventually end up with a table that holds records for unique osm_ids only?
osm2po creates a unique “id” field. I assume “osm_ids” contains the original IDs as found in the OSM file. Why would you want to change them.
I translated Greece.osm to PostgreSQL table with osm2po.
I would like to know how they calculate km,kmh, cost and reverse_cost in the table?
Also I don’t know the meaning of the fields clazz and flag?
The meaning of clazz can be looked up and changed in osm2po.config file. You’ll probably find flag there too. Please get in touch with the author of osm2po for support.
My pgRouting Layer is disabled. When I open Plugin manager on the list I see this:
pgRouting Layer (Version 0.1) [incopatible] this is grey color
What is wrong ?
Which QGIS version are you using?
pgRoutingLayer requires minimum version 1.7. That’s why it is greyed out in 1.6.
Thank you, now it works!
I hope everything is fine!
Do you know how can I load a table with multiple Start points and multiple endpoints to generate multiple and different routes at the same time? I don´t know exactly how to explain, but I have a table like this:
And I need automatically generate
Latlong1 to Latlong2
Latlong 3 to Latlong4
and so on.
All the routes by the shortest path.
Do you have any idea?
Very thank´s for any help and congratulations for the beautifull and very, “very usefull posts”!
I don’t have a ready solution for this problem but I’ll keep it in mind and write about it once I know.
Do you think it´s possible do this with pgrouting? Or a solution with QGis and PostGIS?
It’s certainly possible with pgRouting. The easiest approach would be to write a short script e.g. in Python that creates a series of pgRouting SQL queries.
Well, I will try. Thank you!
I have the same problem as the one you describe above.
Could you please let me know if you managed to solve the problem?
Hi. Just stumbled across this and am giving it a go. Does step one, parsing the OSM file, take quite a while? It seems to be running in the command prompt window, but I’m not really sure if it’s running or just crashed/waiting for input. It has generated the log.txt file in the folder, but there is only ‘INFO’ in there at the moment.
Ok. Sorry. I’ve got it working now. Must say – WOW! :-)
Hi.. first of all let me thank you for these amazing tutorials!! i really dont ve words to express how grateful I am to you for providing this tutorial.M done with all the above steps.However I am stuck in the use of PGrouting.I Ll be more thankful if you could help.
I ve loaded my city’s OSM data into PostGIS using Osm2po.And the database seems fine with the table name ata_2po_4gr which has a total of 18 columns.
Next, i also installed Pgrouting(I use windows 7 ) according to your earlier tutorial.Also executed the SQL files..now when i use the pgrouting function it returns an error saying “gid column doesnt exist”. I wish yo get access to the geometry that will form the path .. however :(
Thank you in advance.
I would like to thank you for the great info, too.
It might seem too dummy but I was wondering if I have to have .pbf file to put into osm2po.
I use JOSM and as far as I noticed as a beginner, I can only get .osm file from JOSM for a selected area. Can I use these .osm files in osm2po, or do I need .pbf files? If latter, how can I get .pbf file for any desired area?
Now, by using an existing .osm.pbf file from geofabrik, I managed to come to the step where the log file including this line is created:
INFO commandline template: psql -U [username] -d [dbname] -q -f “C:\Users\Anita\temp\osm2po-4.2.30\at\at_2po_4pgr.sql”
Then, when I try to use the following command, but I get an error saying “psql” is not recognized:
:\Users\Anita\temp\osm2po-4.2.30\at>psql -U [username] -d wien_ogd -q -f C:\Users\Anita\temp\osm2po-4.2.30\at\at_2po_4pgr.sql
I would appreciate if you could help me fix this issue.
psql.exe has to be in your path (something like C:\Program Files (x86)\PostgreSQL\8.4\bin;). Also, I guess your user is not called “Anita”. You have to specify the correct path.
Thanks very much for your answer.
1. Of course, I use my own path.
2. In the original description above, you wrote:
C:\Users\Anita\temp\osm2po-4.2.30\at>psql -U [username] -d wien_ogd -q -f C:\Users\Anita\temp\osm2po-4.2.30\at\at_2po_4pgr.sql
Therefore, I thought we call “psql” command from the recently created path: C:\Users\Anita\temp\osm2po-4.2.30\at\ (the “at” folder which was created after osm2po operation)
However, you now say psql should be called within PostgreSQL folder. Okay, I can see psql in there and try with it. But am I correct? Is there any confusion?
3. , I have to do this operation on a pgrouting enabled database, don’t I? I haven’t installed pgrouting yet but I plan to follow your instructions here:
Wouldn’t it be enough to come back here and import the .sql file into my pgrouting enable database?
I really apppreciate you help and guidance.
This query does execute the SQL file against the specified database which has to exist already. I don’t think pgRouting has to be installed at this point but it wouldn’t hurt either.
No. Windows has to be able to locate psql.exe. This is only possible, if the Postgres bin folder is part of your PATH system variable. Please google how to set it.
Thanks very much for your patient answers.
I have added the Postgres bin folder to my PATH.
then, I run the command and got such an error:
It basically says that
“ERROR: addgeometrycolumn function does not exist” + and continuously it says “ERROR: it has more columns than the target”
I can now see on pgAdmin that it created the table “at_2po_4pgr” on my database.(as written on sql file)
However, at the next step SQL file requires ” SELECT AddGeometryColumn “. I think I receive an error at this stage.
I would appreciate if it seems familiar to you and you could provide a hint to solve it. In any case, I thank you very much for the valuable guidance.
Sounds like the database isn’t PostGIS-enabled. If PostGIS is installed, there should be a postgis_template available. This template should be used when creating the routing database. Then it should find the AddGeometryColumn function.
I am a big fan of your blog and a grateful consumer of all the advices you give here and on stackexchange. I would like to ask for your ideas and advices with one problem I have when importing road network data.
When importing road network data from OpenStreetMaps, you find some groups of ways forming isolated networks. This is mostly due to incorrect data contributed by users, but is a major source of problems if you try to use it for routing purposes. For instance, imagine you have the lat/long of a user, you find the nearest road to his/her position, and then try to route him/her to a target node. If the closest road to the user happens to be one of those isolated networks, you will not be able to provide any route. Therefore, I would like to get rid of them.
Most of these isolated networks are formed by only some ways (1 to 3), and are very short. One idea would be to scan each node in your table, to check for connected ways, and to see if the length of the connected ways is smaller than, say, 50 meters, in which case you could consider it is an isolated network and delete it.
I don’t know if this is the most efficient way to proceed, neither how to check for those connected ways. What do you think? Your experience with pgrouting and GIS is highly appreciated!
You could try pgrouting’s assign_vertex_id() function with a high tolerance setting (compare http://www.pgrouting.org/docs/foss4g2008/ch06.html). But the general rule “garbage in – garbage out” is also true for routing. The only good solution is to improve the underlying road network.
I just want to display the map of a region with the data like paris.osm, and i want to show some points special on the map, what can i do with osm2po?
Osm2po creates a routable road network from OSM data that can be imported into PostGIS. It’s also a stand-alone router. It doesn’t sound like that’s what you are looking for.
thanks for your answer.
and do you konw other projects that can do what i want ?
The OpenLayers plugin for QGIS might be right for your project.
still a question, can it be used in java application, not for the web.
and can it work offline, or it must connect to the tile server?
I followed your post in a search to see my data also generated with osm2po.
Generation of the .sql went fine, loading seems also to be ok.
To see my loaded data QGis seemed to be an interesting option.
Though, using the pgRouting plugin causes an error at start after activation.
The error: getConnection
“exec( “from connectors import %s as connector” % conntype)”
In QGis, I added PostGis layer, and also there I can find an error:
“Db conn was successful, but accessible tables could not be determined. … please verify that you have SELECT privilege on table carrying PostGis geometry”
This error is fixed & gone now, but still the pgRouting plugin keeps failing with the given message.
Is there anything you can point me to?
I didn’t write the connection code in postgis.py myself. It was taken from “RT Sql Layer” plugin by Giuseppe Sucameli who in turn used “PostGIS Manager” by Martin Dobias. Do those plugins work for you?
Hi, thanks for quick replying.
PostGIS: I guess you mean the “add PostGIS layer” functionality? In there, I can connect. I see the schema and its table. Nothing is shown though. Nothing I can do with QGis as yet because of that.
same thing for the SPIT plugin, btw.
Please write to the QGIS user mailing list. This comment section is too limited to figure out what’s going wrong on your system.
found a solution in one of your other posts.
Thank you so much for all this valuable info you’re providing.
osm2po went fine, I see something in QGis now.
Routing is not possible I think.
My confusion is the following:
In your case, you seem to have tables in you db like “road”
(see this post: https://underdark.wordpress.com/2011/02/07/a-beginners-guide-to-pgrouting/)
In my case however, after loading data from osm2po I only have 3 tables. (geometry*, spatial* and the my_2po_4pgr table)
When are those tables added?
Is it while loading the shape file?
Are they still needed if you loaded with osm2po?
How are pgRouting functions supposed to work, without those tables?
Your QGis pgRouting plugin seems to be working fine however.
Can you please point me to some docs or so?
I’m clearly missing some info :)
All you need for basic routing is a table of edges (defined by start and end nodes) with some costs. Your “my_2po_4pgr” table contains the same information as the “road” table in “Beginner’s guide”. Table and attribute names don’t matter. You just have to adjust the pgrouting queries.
Thanks for the quick reply Anita,
It brings more clarity now.
This means that all pgRouting routines have to be rewritten I guess?
Isn’t that a bit weird?
Or is it the mainstream that everyone designs his own model in spatial databases?
Any clue why did happened like that?
If by “routines” you mean SQL queries – then yes, they have to be adjusted to fit your table layout. The underlying pgRouting code does not have to be touched.
Yes, in spatial databases (like in non-spatial db) everyone can design his own model, because everyone might need a slightly different one. There is no one size fits all. For example you might have different edge costs: distance, travel time, energy cost, … all in one table.
Dear Anita, I am a new user so I am sorry if I do not express myself in the proper way. I have problems when I import the OSM network into PostGIS.I run the command psql -U [username] -d [dbname] -q -f “C:\osm2po\at\at_2po_4pgr.sql” that gives me a list of messages such this one: “psql: C:/osm2po/at/at_2po_4pgr.sql:*number*: ERROR: permission denied for relation at_2po_4pgr”.
Moreover, the last three messages provided by the command prompt are of this type: psql: C:/osm2po/at/at_2po_4pgr.sql:*number*:ERROR: must be owner of relation at_2po_4pgr”.
Do you know which might be the problem?
Thank you in advance.
The user doesn’t have permissions for the database. Have you checked that the database exists and your users has all rights (make it super user)?
Dear Anita, thank you for your help. I managed to solve permission issues by adding another user role in postgre. Then, following your post, I loaded the osm into qgis (I have also created a spatial index). However, the the layer looks like a normal .shp with polyline not like your picture above.
Did I do something wrong?
Thank you. I found the answer in this post: http://gis.stackexchange.com/questions/27481/how-to-create-a-qgis-road-style-for-osm-data
First of all thanks for this pretty much helpful tutorials!
I’d like to ask you a question, dont know if you had or have heard of this situation. I installed everything to enable the pgrouting, after that I did the extraction from an OSM (modified by me in JOSM) file, to PostgreSQL using Osm2Po but after trying to route it through QuantumGis and the Routing Plugin I had the following issue:
“No se puede ejecutar la consulta.
El mensanje de error de la base de datos fue:
ERROR: Start vertex was not found.”
Thing is that after I checked I notice that in my “spatial” table I have:
–7,347 Nodes which are never sources
–6,966 Nodes which are never targets
–6,369 Rows which are never sources nor targets (including above)
(Note: I have 19,191 nodes)
So I would like to know if you know any reasons why this could had happened or how I couId fix it.
Aside of that I tried routing using existing targets and sources ids, but then I got routes which were not from nor to the nodes I had used… which like dont know why could had happen.
Finally the routing with Osm2Po in the Localhost do worked like a charm.
Thanks in advance for your time! xD
Answered on gis.stackexchange.com
Thank you for this clear explanation. I have posted a question that is bothering me here at the link below. More generally, have you any views on how to improve the “cost” for each possible route segment. I understand that there is historic travel time data from roving GPS vehicles but I don’t know if this is free. Thanks, John.
Is there any way to send parameter through a wms to qgis draw the result and get s back the result of the shortest path?
No, that’s not what a WMS does. But there are tutorials for creating a routing service in OpenLayers with pgRouting backend. http://www.pgrouting.org/docs/ol-workshop/index.html