Data model: Manhole: usage_current about qgep HOT 6 CLOSED

This is a good idea with the trigger. But it is not the goal that the user inputs this field else we can have conflicts in data and that this is exported in the INTERLIS VSA-DSS. VSA-DSS-Mini has a new Attribut Knoten.Nutzungsart_Ist to export usage_current to make checks about Primary and secondary channels (PAA und SAA Kanäle) and needed mandatory attributes. These rules could be implemented later also on the database after publication of the new release of Wegleitung GEP-Daten (in the next 3 months)

from qgep.

Good idea with trigger. But consider: the "usage_current" is not from the incoming, but from the outgoing "od_channel"s. If there are two or more outgoing channels, you must have rules, which of the channels is decisive. Ist normally the one with the „greater“ Funktion_Hierarchisch. If they are even, then there is the following „ranking“: schmutz-, misch-, entl.misch-, industrie-, regen-, rein-, …

from qgep.

How about another approach:
Add a new view (if we run into performance troubles, we can make it materialized) and create new views that require this attribute from manhole based on the intermediate view? This keeps us closer to the upstream datamodel and solves the same problem without redundancy.

from qgep.

Will first create SQL function to derive usage_current from outgoing reaches. We will first try with a view. If performance is an issue we may try a materialized view. If it is still too bad we can try to add an additional field and add insert/update triggers.

from qgep.

We added three aditional columns to od_manhole - all extra columns are prefixed with and underscore (_) to distinguish them from the standard columns of the data model:

• _usage_current
• _function_hierarchic
• _orientation

We still need triggers on the reaches to update connected manholes.

from qgep.

Already on github - see af5ec93 and some following corrections

from qgep.