Robin's Blog

How to fix osm2pgrouting error ‘relation “__waysXXXX” does not exist’ and speed up the import

I don’t seem to have time or energy for long posts these days, but here’s another quick post which might save you some time and frustration.

Recently, I’ve been trying to import OpenStreetMap data into a PostGIS database to use with pgRouting. I wanted to import data for the whole of England – which has quite a lot of roads!

I initially tried following standard guides online, to do roughly the following:

1. Download the OSM data from Geofabrik
2. Convert the osm.pbf file to an osm file by running osmconvert file.osm.pbf > file.osm (see osmconvert)
3. Import the data using osm2pgrouting. I used the following command:

osm2pgrouting -f england-latest.osm -c mapconfig_for_cars.xml
-d $DATABASE -U $USER -h $HOST -W $PASSWORD
--schema public --prefix osm_roads_ --clean > output.log 2>&1

This takes the england_latest.osm file, uses a config for cars (so it doesn’t try to route down paths, but only routes that are accessible to cars), specifies how to connect to the database, what schema to use and what prefix to use.

I tried running this and it ran for hours and hours and eventually crashed with loads of errors of the form:

While processing FROM 1800000th          to: 1820000th way
count1820000 While processing FROM 1800000th     to: 1820000th way
[********************|                              ] (40%) Total processed: 1840000
ERROR:  relation "__ways8380" does not exist

I eventually worked out that the way to solve this is to add a --chunk 10000000 parameter to the command line call. This is a big increase on the default chunk size, and has two benefits:

• It stops it crashing – which is always good!
• It massively speeds up the import, taking it from running for many hours (and then failing) to finishing successfully in about 45mins

I found it a bit tricky to find sources about this online, and the official documentation isn’t very detailed. However, this post and this issue were helpful.

I’m not entirely sure how the larger chunk size stops the crash, but the speedup seems to come because osm2pgrouting uses the Postgres COPY command (which is very efficient), and with a larger chunk size it runs the COPY command a few times with large chunks of data rather than loads of times with small chunks of data.

Warning: You will need a computer with a lot of RAM to run this successfully with a large chunk size. I used a temporary cloud VM with a large amount of RAM which only cost me about £5.

If you found this post useful, please consider buying me a coffee.
This post originally appeared on Robin's Blog.

Tagged with:

Categorised as: GIS, How To