[Tile-serving] [openstreetmap/osm2pgsql] Allow propagating tags to ways during relations processing (#230)

[Sarah Hoffmann]

@kennykb Two problems I see with that proposal: first, it is focused too narrowly on routes. Anything included in osm2pgsql directly has to be able to work with any kind of relation. The proposal has potential to be generalised to arbitrary relations though, so that's not a real blocker.

More importantly, the proposal suggest to add additional tables which are joined during render time. I really don't think this is a good way forward. The necessary information for the way should be collected when the database is created. It is less time critical at that point and we can cache the necessary relation information more efficiently.

That said, if joining is fast enough for you you can do all that relation-member lookup relatively efficiently already with a stock osm2pgsql in slim mode without creating any additional tables: Route relations are already processed by osm2pgsql. The route relation information (`planet_osm_route` in your proposal) is available in `planet_osm_line` (negative id because it is a relation). The relation-member information is in the `planet_osm_rels` and there are even convenient indexes created. Putting all together, you should write a lua tagtransform script that pre-computes the appropriate shied information for each highway route you are interested in and save that in a custom `shields` column. Then you can collect all shields for a way like that:

```
SELECT osm_id, 
       ARRAY(SELECT DISTINCT l.shield
             FROM planet_osm_rels r JOIN planet_osm_line l ON r.id = -l.osm_id
             WHERE o.osm_id = ANY(parts) AND 'w' || o.osm_id::text = ANY(members))
FROM planet_osm_line o
```
`parts` of planet_osm_rels has an index, so you need to use that to look up membership. It only stores raw ids though that do not distinguish between ids for nodes, ways and relations. That's why there is the second match against the `members` column. This is just an outline to give you an idea how this could work. The indexes used are a little bit larger but I doubt that you see much of a difference in rendering performance compared to the extra tables and you save the updates of the tables.

As for using the slim tables here, officially speaking that is indeed frowned upon. However, fact is that they are so widely used, that we can't really change the format at a whim. It might happen at some point (there is a discussion about using JSON) but is more something of a once in ten years occurrence.

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/230#issuecomment-426570704
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20181003/de7f6e05/attachment-0001.html>