[Tile-serving] [osm2pgsql-dev/osm2pgsql] Linking between nodes and ways (Discussion #2455)

[Jochen Topf]

> explicit "node/way correlation table" with a row for every "connection of interest"

And that's something you can already do with osm2pgsql, just not very efficiently. You can create one table with, say, all nodes tagged with `level_crossing` and another table with all ways tagged `railway` and a third with all ways tagged `highway`. You have the list of nodes referenced from a way available in Lua and you can use that to write a table that connects all way node members with all ways. Problem is: This table contains **all** nodes referenced from highway/railway ways, not just the ones connecting a "level crossing node" to a "highway/railway way", so this table is huge.

The problem for osm2pgsql is: It can only see one object at a time. For a node it imports it can know whether it contains a `level_crossing` tag, not whether it is part of a highway/railway. For a way it can know whether it has a `highway` or `railway` tag, but not whether one of the nodes it uses has a `level_crossing` tag. This information is simply not available due to the streaming processing. The two-stage processing you mentioned is a way around that, but it currently only works for relations and their ways members. And it is unintuitive and hard to use.

A way to solve this with current osm2pgsql is to just import that huge table as described above and then run an SQL query after the import removing all the node/way combinations that are no interesting. Not great either, but it would work. An alternative would be to add a trigger to that table connection nodes with ways that will check whether a node added has a `level_crossing` tag and only then insert it, ignoring all other inserts. That way you can avoid creating the huge table, but need to write and maintain the trigger.

And another possibility: When you have an updatable database, ie. in slim mode, you aready have the list of member nodes for all ways available in the database. So you can run an SQL query after import that finds all ways having a `level_crossing` node in them and add some data from the ways to the nodes row.

The way to think about this is: Osm2pgsql can only work on one object at a time, but after the import (or update) you do have a database with all sorts of clever functions in it. So you can prepare the data in osm2pgsql in a useful way but then in a second step
use the power of the database to connect all sorts of data. Where this becomes tricky is with updates, it is not always easy to make this work correctly with updates.

So this is already something that can arguably be solved with current osm2pgsql and some basic SQL and doesn't necessarily need spatial joins or other complex spatial queries. A way to make this easier could be to add some functionality in osm2pgsql that would allow you to query member nodes from Lua when importing ways. But that adds a huge overhead because of the many database queries needed, not a great solution either, but certainly something we are considering.

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

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