Easier customization of the _label in wastewater_structure table

[kandre]

As I understand, _label attribute in wastewater_structure table is generated on database side through a trigger (or manually) with the general function called update_wastewater_structure_label. Modifying manually the function in the model is not an option in my opinion because it cout be forgotten during model upgrades.

We would like to get more control on how this _label is written, such as:

• I and O refers to input and output => users would like to see that in french (E and S)
• reorder inputs and outputs levels
• possibly add other attributes (bottom_level, depth, etc.)

[3nids]

I think we have several approaches here:

1. Do everything in QGIS
   While this brings the most flexibility, we can have concerns for performance and it prevents from using this in a map server

2. Do everything in DB
   The current approach. We could extend this by providing a _label column per language.

3. Pre-process in DB, render in QGIS
   a. Create a pre-formatted string in the DB that we use afterwards in QGIS by replacing text ( __INPUT__ would become E in French, etc.
   b. During pre-processing script, outout structured data (JSON) to the field, so this could be used in a very flexible manner in QGIS (language and usage wise).

Of course, my preference goes to 3b, but this approach requires some testing to be sure it's fully applicable.

[urskaufmann]

I prefer also 3.
suggestion: not one pre-formatted one string, but

• a string for cover levels
• a string for input levels
• a string for out levels
• a string for bottom level
• a string for depth

The identifier must of course not be predefined.
Like this, the user can configure his labeltext and can add e.g. function-shortcut or dimension.

Other suggestion: I prefer to label only in-/out-levels of pwwf-channels. I changed therefore the update_wastewater_structure_label-script: WHERE (_all OR NE.fk_wastewater_structure = _obj_id) and CH_to.function_hierarchic in (5062,5064,5066,5068,5069,5070,5071,5072,5074) ----label only reaches with function_hierarchic=pwwf.*
There should be a way to define the function_hierachic-values not in the sql (in the QGIS-project a variable can be used for this).

[kandre]

I also prefer 3, especially 3b because it is really flexible and almost fully answers our needs (except the possibility to choose the calculation method for inputs and outputs order.

By the way, could you confirm that the inputs are ordered clockwise from the output reach orientation in the model ?

[urskaufmann]

If the pre-formatted strings are separate for cover, in and out, then the replace-solution is quit easy because there are no other letters than C, I and O in every of these strings. _depth already exists. So we have to add fields _labelcover, _labelin, _labelout (text fields) and _bottom (numeric 6,3).
Change/add the functions update_bottom and update_wastewater_structure_label should not be very difficult (but to difficult for me - sorry).

[m-kuhn]

How about the following proposal (pretty much like @3nids suggested).

Adding fields (that can eventually replace the current label field) for the "complex" parts only.
Complex are the ones that can consist of multiple parts: cover, input, output and optionally bottom level (if someone insists).

_cover_label = 'C1 123.21\nC2 122.88' // includes main cover
_input_label = 'I1 121.88\nI2 121.77'
_output_label = 'O1 121.23\nO2 121.35'
_bottom_label = 'B1 120.88\nB2 120.77' // required?

The demo project will then be preconfigured with an expression that concatenates these parts and replaces the english default prefixes.

Please add 👍 to this post if you like it or 👎 if you don't like it

[m-kuhn]

Possibly something to consider in the same sprint #9
Save Inlet Outlet indexes in reach points as well, so we can label the I/O as well.

[m-kuhn]

Should we leave _label in the database or remove it, now that we have the parts in the database?
Should we declare it deprecated (meaning, it's there but not recommended and we will remove it in the future)?

[olivierdalang]

@urskaufmann @ponceta Are you able to test and review the proposed fix for this ?

Delta for the schema : delta_1.5.0_labelling.sql
Updated QGIS project : qgis.qgep

[urskaufmann]

wow, its on a good way.
The new label-fields are in the wastewater_structure-table, but not in the vw_qgep_wastewater_structure-view. I did add it to the view, did change the variables to german, moved in the label-definition the bottom_label to the end and it looks great:

If I move one of the reaches, the input-reaches are renumbered:

Something is not as expected: I expect E1, E2, E3 and not E2, E4, E5. Reaches that have now level or better reaches that should not be labeled do not count. I think we should discuss this with the users (code sprint?)

[m-kuhn]

@olivierdalang can you have a look at the numbering (E2, E4, ...) and the qgep_wastewater_structure view?

For the rest, let's have a look at the sprint 👍

[olivierdalang]

The new label-fields are in the wastewater_structure-table, but not in the vw_qgep_wastewater_structure-view.

I added the fields to the view creation script, so normally you'd have them if you do a normal pum upgrade (and not just run the delta I provided).

Something is not as expected: I expect E1, E2, E3 and not E2, E4, E5. Reaches that have now level or better reaches that should not be labeled do not count. I think we should discuss this with the users (code sprint?)

Indeed, it seems reaches without level were counted but not displayed. Instead of just skipping them, wouldn't it be better to display E1=? ? It would make it easier to identify the reaches and also help identify missing data.

[olivierdalang]

Indeed, it seems reaches without level were counted but not displayed. Instead of just skipping them, wouldn't it be better to display E1=? ? It would make it easier to identify the reaches and also help identify missing data.

That would be it : https://raw.githubusercontent.com/olivierdalang/datamodel/524_labelling/delta/delta_1.5.0_labelling.sql

[olivierdalang]

3 more fixes to add :

• do not show unknown bottom levels
• order of inputs/outputs accord to angle doesn't work properly
• put a deprecation notice in the old label field instead of identifier

[olivierdalang]

• order of inputs/outputs accord to angle doesn't work properly

After in depth inspection with Urs, it seems the order of inputs/ouputs in the label is computed correctly. The issues is due to the demo data, where there's a mess around special structures. It is automatically with the reach update trigger... So it's probably worth running that trigger on the whole table to fix the data ?

[urskaufmann]

order of inputs/outputs accord to angle doesn't work properly:

The angle is now calculated with the first or last percent of the reach. This works not in all cases.
Correct would be, if the angle is calculated between the point, the reach enters the manhole and the center of the manhole. With normal manholes, the angle is the intersection with reach and a circle of Dimension1/2 or 0.6m and the main node. Special structures with geometry its the intersection geometry - reach and the main node.
If there is no intersection, then the angle is defined with reachpoint and the main node.

[ponceta]

Should we leave _label in the database or remove it, now that we have the parts in the database?
Should we declare it deprecated (meaning, it's there but not recommended and we will remove it in the future)?

I would not deprecate the _label attribute if possible! It is useful and makes sense to have an aggregation already set. The cost per transaction does not seems too high to me?

[olivierdalang]

@ponceta You're right about cost per transaction, it's probably negligible. It's more about avoiding duplication (in the code, the docs, the future bugs, etc...). As far as I understood, currently, there's no backwards compatibility policy with QGEP, so that users should anyways update their QGIS project when changing datamodel version.

And if we think deprecating it later, then we're just as good doing it today, as it won't cause less trouble later :-)

[varrieta]

@olivierdalang I'm doing some test with version 1.5 and the fields _bottom_level_label, _input_label and _output_label don't get updated when I update the corresping data..

But _cover_level_label does get updated.

Does it work for you?

[olivierdalang]

@varrieta That's strange ! It works here and the functionality is covered by automatic tests... so there may be something unexpected going on.

Are the labels correctly updated before your update to 1.5.x ?
Is the data not updated at all, or is it not correctly generated (for example empty label, while there are input/outputs) ?
Can you provide steps to reproduce the issue ? (ideally with the demo data, or with an extract of your data where it happens)

(also you wrote _bottom_level_label and _cover_level_label , while the fields are named _bottom_label and _cover_label, but I guess that's just a typo ?)

[varrieta]

Are the labels correctly updated before your update to 1.5.x ?

Not sure, I didn't use the functionality before. I'll try and let you know. It is really strange because it works for the cover..

Is the data not updated at all, or is it not correctly generated (for example empty label, while there are input/outputs) ?

No it is updated but the label not generated

(also you wrote _bottom_level_label and _cover_level_label , while the fields are named _bottom_label and _cover_label, but I guess that's just a typo ?)

Yes just a typo sorry

[sjib]

How can I manually run this function: qgep_od.update_wastewater_structure_label? What are the parameters if I want to update all labels?

[bogdanvaduva]

Have you tried SELECT qgep_od.update_wastewater_structure_label(null, true)?

[sjib]

@DflGruBoe Can you test whether it does what you want? The query itself runs on my system, but I cannot check whether it solves your problem.

[m-kuhn]

See also #601

[sjib]

Please reopen if still an issue