How to omit certain relation members with flex output?

[cdauth]

I am completely new to osm2pgsql and I'm trying to bring the Openptmap scripts up to date.

The way that it works right now is that it uses Osmfilter to keep only certain tags and only objects with certain tags before importing the planet/region file with osm2pgsql. I am trying to rewrite this into an osm2pgsql flex output Lua script so that I can apply deltas to the database later. One thing that I'm struggling to implement is that the osmfilter file removes any objects with a railway=platform tag. Because the removal happens before the import to osm2pgsql, such objects will also be omitted from the geometries of relations that contain objects with such tags, because the members simply cannot be found in the imported data.

I'm struggling to implement this in my Lua script. I successfully manage to ignore ways with railway=platform in my osm2pgsql.process_way script, but if those ways are a member of any relation, they still end up in the geometry of that relation.

Right now I’m trying to reproduce the layout of the previous pgsql output with 3 tables, one for points, one for lines and one for polygons. From what I read online, one way to do it would be to instead create tables for nodes, ways and relations and make the relations table not contain any actual geometries, but make it reference the nodes/ways and pull them in using joins in mapnik.xml. I'm imagining I would have to create a nodes, ways, routes, routes_members, polygons and polygons_members table, where routes and polygons would contain the relations with their tags and routes_members and polygons_members tables would reference the relation members. However, I’m not sure what the SQL query to construct the actual relation geometries would actually look like. Specifically, I'm wondering about:

• How to support relations that are members of other relations? The tags of a relation should be applied to all its descendent ways in the hierarchy of member relations. Is this even possible with an SQL join?
• How to construct a multipolygon from the members of a polygon relation?

Is there maybe some example code somewhere that already does this?

[joto]

Lets answer the specific questions first:

• Building recursive queries in SQL is painful, but possible with WITH RECURSIVE common table expressions (CTE). But you probably don't want to do that. Just implementing limited levels of recursion should be okay with OSM data, I would think.
• You don't want to implement multipolygon construction from OSM relations in SQL. That is done really well and fast by osm2pgsql (well, actually in libosmium which is used by osm2pgsql) which takes into account bad data and knows about all the corner cases. So I'd recommend that for multipolygon relation assembly you use osm2pgsql processing.

Now to the wider question of how to approach processing OSM public transport data with osm2pgsql: The support for special types of relations in osm2pgsql is not great. type=multipolygon is really well supported, but other types are not. You can create a (multi)linestring from a route relation, but as you noticed, if that route relation contains platforms, that doesn't work correctly. But merging linestrings into longer linestrings in SQL is reasonably easy (easier than creating multipolygons), so you can go with the approach you suggest, importing members of the pt relations into one table and joining the routes together in the database. There are multiple ways to do the details, but you'd probably need to use two-stage processing. See the manual and this architecture description on that and have a look at the flex-config/route-relations.lua example config file in the osm2pgsql repo.

What it boils down to is that in the first stage you identify all type=route relations with route=bus or whatever that you want to import and mark their member ways. Marked ways are then (re)processed in the second stage where you check the tags to ignore the members that are platforms and only add the ways to some table that contain the actual parts of the route. Then in SQL it becomes a simple operation of GROUPing by relation id and using ST_LineMerge to assemble the whole route. I am not totally up to date with all the intricacies of the PT data model, so it is probably more complex in reality.

I am open to suggestions for adding functionality to osm2pgsql to make all of this easier. But the architecture of osm2pgsql, which essentially is built around the idea that you only look at one OSM object at a time, limits what can be done. Two-stage processing lifts this limitation a little bit, but it is quite difficult to use. Maybe this is something which could be added to the osm2pgsql-gen generalization framework which is built around the idea that it can read data after the initial import from SQL tables and processes it again and writes the results back into the database.

[cdauth]

Thank you for your detailed response. I now finally have the time to look at this again, and what you have written has been very helpful so far.

It seems like I will be able to find a way to achieve this by keeping things the way they are for multipolygon relations, but resolving the geometries of route relations in the second stage only, although this will be a bit of work to implement.

I am not deep enough in the internals of how osm2pgsql actually works to make a qualified suggestion how it could be improved to support my use case. From my novice point of view it seems like the object:as_multilinestring() relation method would be the usual approach to achieve what I am trying to do, and it does seem convenient. The specific functionality that I’m missing for my use case is to exclude relation members based on their tags. It seems to me like this is something that could be added somehow, since I supposed the method needs to load the individual member objects anyways to resolve their geometries, so it wouldn’t cause an additional performance regression? Interestingly, even if I ignore a way in the osm2pgsql.process_way(object) function both in the first and second stage, its geometry is still added to the relation. However, I suspect that I might be able to exclude members of the relation based on their role by simply deleting them from the members property before calling object:as_multilinestring(). I will have to try this out, and if it works this could be a feasible solution that wouldn’t even require two-stage processing, as railway platforms can be identified both by their member role as well as by their object tags.

[joto]

Deleting objects from members has no influence on anything in osm2pgsql, not the multilinestring creation nor anything else. Basically you only get a copy of the data to mess with in Lua that doesn't affect anything else.

I agree it would be nice if osm2pgsql would allow some more flexibility in regards to processing relations and their members. There are some words about that in our project ideas list. But so far it is unclear how exactly this is going to work.