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.

[mboeringa]

Or a member_table.complete boolean field added next to member_table.type/ref/role that can tell if the member's geometry is available.

Although likely the most robust, as not relying on order, would still be a member_table.geometry property that could return a NULL geometry that could be tested with geometry.isnull() whenever a member's geometry is not available due to incomplete relation due to extracts.

[joto]

As you noticed relations and their members are quite a complex issue. That's why there isn't a better solution for these kinds of problems yet. It is just difficult to define and implement a good (and performant!) solution that takes all cases into account.

[mboeringa]

I agree about the complexity, but as suggested a member_table.geometry property that returns a NULL geometry whenever a member's geometry cannot be build and that could be tested with geometry.isnull() to allow deciding what to do next with it (skip insertion in a table), would I think be really useful. That would at least allow the partial insertion of incomplete relations by inserting those members that are available including their corresponding osm ID as already available via member_table.ref.

[mboeringa]

In my specific use case, I am not trying to build a single representative geometry for the entire relation, but just grab the individual member ways and insert them as separate ways, while storing a reference to the corresponding relation ID and way ID.

[joto]

That doesn't work because
a) there isn't one geometry for a member, there are lots of possible geometries you can create from a member (if the member is a closed way for instance, a linestring or a polygon)
b) for a property we'd need to create the geometry even if it isn't used which creates a lot of overhead

We'll figure something out eventually, but for the moment: this is harder than it looks which is partly due to the OSM data model but partly also due to the way osm2pgsql handles these things internally.