Use osm2pgsql to prepare data that can be imported in a routing service

[tordans]

During the FOSSGIS OSM Unconference we talked about how to scale and add routing features to the cycling quality index project https://www.osm-verkehrswende.org/cqi/.

First, we want to scale the project to all of Germany using the osm2pgsql setup from radverkehrsatlas.de. This will allow us to create road segments with just the right data processed in the way we need it. Those are categories, cleaned up tags and – of course – the quality index value per segment.

The issue we discussed was, that most / all(?) routing services are build to import a raw or filtered (osmium) OSM file.

However, we end up with a PostgreSQL database.

So the question is, is there any tooling in the ecosystem of osm2pgsql or outside that we could use to export this database back into a file that in turn can be used to be imported by a routing service?

---

Update: One thing that will make this more "interesting" is, that we "need" to create up to 5 geometries (usually only up to 3) per centerline road segment because that simplifies the infrastructure categorization process. Those geometries are all identical to the centerline and from a OSM data format point of view should be separate ways but use the same notes as the original centerline.

[pnorman]

Not really. osm2pgsql is a lossy transform by design and does not include topology. You could use ogr2ogr to export to another format and convert that to OSM format, but that seems like the wrong way to do it.

I know some people who have processed data with osm2pgsql then with OSRM called out to the DB to get quality factors on routes which were then used with routing weights.

They then used the OSRM graph to identify major bike routes and then fed that into a second osm2pgsql run.

cc @systemed

[joto]

As pnorman said the job of osm2pgsql is normally to do a lossy transformation of OSM data into some format better suited for the use case. That being said, osm2pgsql also has the "middle" tables (usually needed for updates) which contain all the OSM raw data. I do have some experimental code to turn those middle tables back into an OSM file. So you could use osm2pgsql to just import the middle tables, do some processing on them and write them out again. But with this approach you can only output modified OSM data, not completely new data, or the same road centerline several times, because the ids would clash.

It would, of course, be possible to generate a completely "fake" OSM file from the data in an osm2pgsql table, but that would involve writing code to turn geometries back into nodes and all that. Doable, but a rather cumbersome way, especially if you want to use that in something like OSRM which will have to reverse that.

Is it important that you do the routing in an external program? Another option would be to use pgRouting. I have thought about an osm2pgsql add-on program that would generate a routing graph, that's something a lot of people have asked for. There are some import tools for OSM to a database for pgRouting, but from what I have seen they are not that great/maintained.