[Tile-serving] [openstreetmap/osm2pgsql] Feature request: deletion of relation members (#1325)

[Kevin B Kenny]

I'm still trying to work with a schema that has a table one row per relation membership: that is, a table with columns 'relid', 'wayid', 'role' and 'idx', where a row represents "way W appears in route relation R at index X with role Y". (That's what route relations look like when fully normalised, and my application wants to go in either direction - from a way to a _set_ of routes, or from a route to its constituent ways. (This is the same application as in #1312.)

I may be missing something, but it strikes me that there's trouble with making this work in the flex backend. There are two issues:

(1) The 'osm2pgsql.process_relation' call cannot tell whether it's inserting or deleting a relation.

(2) There's no way to delete rows from the table that represents relation memberships, and they're not auto-deleted either.

What I'm doing:

(1) process_way in stage 1 does nothing special - obviously, ways are imported, but nothing about relation membership happens.

(2) process_relation is called when a route relation is created, updated or deleted, but doesn't know which action is being taken.
If a relation is a route, of the correct type, it creates a row in a 'shieldroute' table in the database: relation_id, route type, network, ref, and a 'changed' flag (set to true by 'osm2pgsql' and reset later by another program).  It also records in a Lua variable the member ways of the relation.

As rather a hack, if process_relation sees that it's being called a second time for the same relation, it removes the previous memberships from the Lua variables. This trick causes updates to function more or less correctly - the first call was the 'delete' portion of the update and the second is the 'insert'.  It does NOT result in correct functioning when relations are deleted, because the values from the first pass are still there.

(3) select_relation_members simply selects all members of route relations of the types that the application is managing.

(4) process_way in stage 2 checks that not only is the way a member of a route relation, but also that it is a highway and not something else (like a guidepost, a turn point, or a terminus). It then creates the rows in the 'shieldway' table that enumerates
relation memberships.  This has to be a way table and not a relation table, because osm2pgsql does not allow adding to a relation table from 'process_way'.

So, I face the following.

1. If a relation is modified, I can reinsert the ways in step (4). There's no way for me to clean up the obsolete relation memberships, so they will linger in 'shieldway'.  I deal with this - and it appears to work - on the SQL side, by doing

```sql
ALTER TABLE @PREFIX at _shieldway
ADD CONSTRAINT @PREFIX at _shieldway_fk_shieldroute
FOREIGN KEY (relid)
REFERENCES @PREFIX at _shieldroute(relid)
ON DELETE CASCADE;
```
This discards the excess rows when the old version of the relation is deleted.

2. If a relation is simply deleted, the foreign key constraint above will indeed discard the excess rows, but I'll still have them in the Lua variables from steps (2) and (3) above, so step (4) will immediately re-insert them!  I don't have a workaround for this; fortunately, deleting route relations is extremely rare.

At the very least, what I'd like to have is that when process_relation is called, the 'object' parameter should have a 'delete' flag that is true when deleting and false when inserting (or an 'action' parameter with values 'insert' and 'delete'), so that the code can at least not put back the rows representing members of a deleted relation.

Preferably, I'd also like to have an interface to delete from a node, way or relation table given the node ID, way ID or relation ID respectively, so that the ON DELETE CASCADE constraint can be replaced with explicit deletion from the Lua script. This might be tricky to design, since there's nothing that guarantees that the ID will be unique in any given table (for instance, if multiple routes run over the same way, shieldway will have multiple lines for that way. I'd welcome a discussion of this. For right now, the referential constraint is at least enough to keep me moving forward.

@joto What do you think? Am I making sense here?

-- 
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/1325
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20201116/18fb139b/attachment.htm>