Add status to automatically created nodes

[rbovard]

Currently, each pipe has a node at both of its ends.

The problem is that if we want to display the node layer with only the pipes that are in service, it’s not possible because the pipe's status is not associated with the node. This leads to strange visual outputs like the one shown here:

Would it be possible to implement a mechanism to transfer the pipe’s status to the node? Currently, it’s not possible to delete nodes because they are referenced in non-nullable fields of the pipes.

I wrote this query to list all the nodes that are only connected to pipes that are not in service (i.e., if a node touches both an in-service pipe and an out-of-service pipe, it is not included). However, it would also be worth considering a hierarchy of statuses to determine which one should take precedence over the others.

SELECT n.*
FROM qwat_od.node n
WHERE EXISTS (
    SELECT 1
    FROM qwat_od.pipe p
    WHERE ST_Touches(n.geometry, p.geometry)
      AND p.fk_status IN (1302, 1303, 1304, 1305)
)
AND NOT EXISTS (
    SELECT 1
    FROM qwat_od.pipe p
    WHERE ST_Touches(n.geometry, p.geometry)
      AND p.fk_status NOT IN (1302, 1303, 1304, 1305)
);