[Tile-serving] [osm2pgsql-dev/osm2pgsql] How to omit certain relation members with flex output? (Discussion #2204)

[Jochen Topf]

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](https://osm2pgsql.org/doc/manual.html#stages) and [this architecture description](https://osm2pgsql.org/contribute/how-osm2pgsql-processing-works.html) 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.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2204#discussioncomment-9799006
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2204/comments/9799006 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20240617/a5146393/attachment.htm>