Add (option) to run query tww_update_maincover after import

[sjib]

When importing INTERLIS data you cannot select objects on the layer vw_tww_wastewater_structure and symbols are not displayed correctly:

The following query needs to be applied to set a main_cover:

UPDATE tww_od.wastewater_structure WS SET fk_main_cover = structure_cover.co_obj_id FROM ( SELECT WS.obj_id AS obj_id, min(CO.obj_id) AS co_obj_id FROM tww_od.wastewater_structure WS LEFT JOIN tww_od.structure_part SP ON SP.fk_wastewater_structure = WS.obj_id LEFT JOIN tww_od.cover CO ON SP.obj_id = CO.obj_id GROUP BY WS.obj_id ) AS structure_cover WHERE structure_cover.obj_id = WS.obj_id;

@ponceta To discuss in technial group tomorrow how to add this. Also discuss if query update_mainnode is also necessary or not.

[domi4484]

@sjib let me know if and which queries need to be added to complete the import

Would this be an option or something that can be run always?

[sjib]

The new datamodel has a fk_main_cover, so we should not overwrite it if it is existing.
But for display the TEKSI attribut fk_main_node is important - so rather run this query:

UPDATE tww_od.wastewater_structure WS SET fk_main_wastewater_node = structure_node.wn_obj_id FROM ( SELECT WS.obj_id AS obj_id, min(WN.obj_id) AS wn_obj_id FROM tww_od.wastewater_structure WS LEFT JOIN tww_od.wastewater_networkelement NE ON NE.fk_wastewater_structure = WS.obj_id LEFT JOIN tww_od.wastewater_node WN ON NE.obj_id = WN.obj_id GROUP BY WS.obj_id ) AS structure_node WHERE structure_node.obj_id = WS.obj_id;

To discuss - do we want to chose the node with the lowest obj_id or the lowest level instead?

[urskaufmann]

I have reimported again: first got the map with the node-crosses (as shown above).
Then just run the main_node_update.sql -> vw_tww_wastewater_structure symbols are visible, the view works.

But: in the vw_tww_wastewater_structure form, tab "maincover/mainnode", there is no data in the cover-window. Well, I can edit the covers in the "covers" tab - except when digitizing a new wastewater_structure. Thats the reason, why main_cover_update should run after import (only for wastewater_structure, that have NULL as fk_main_cover.

I have also just run the main_cover_update after importing. The result was, that there is no node-data in vw_tww_wastewater_structure, and because there is a not NULL constraint in attribut wn_obj_id, I can not save any changes in the attribut form. So we have to run both updates - main node and main cover.

[cymed]

I have reimported again: first got the map with the node-crosses (as shown above). Then just run the main_node_update.sql -> vw_tww_wastewater_structure symbols are visible, the view works.

But: in the vw_tww_wastewater_structure form, tab "maincover/mainnode", there is no data in the cover-window. Well, I can edit the covers in the "covers" tab - except when digitizing a new wastewater_structure. Thats the reason, why main_cover_update should run after import (only for wastewater_structure, that have NULL as fk_main_cover.

I have also just run the main_cover_update after importing. The result was, that there is no node-data in vw_tww_wastewater_structure, and because there is a not NULL constraint in attribut wn_obj_id, I can not save any changes in the attribut form. So we have to run both updates - main node and main cover.

#36 inserts a new cover on update if there was none before and at least one attribute is used. So as soon as we merge this, the cover problematic is gone.
Also #58 defines the new cover as the main cover if there is no prior cover

[sjib]

Thats the reason, why main_cover_update should run after import (only for wastewater_structure, that have NULL as fk_main_cover.

Here the adjusted query for main_node with WHERE fk_main_wastewater_node IS NULL

UPDATE tww_od.wastewater_structure WS
SET fk_main_wastewater_node = structure_node.wn_obj_id
FROM (
  SELECT WS.obj_id AS obj_id, min(WN.obj_id) AS wn_obj_id, fk_main_wastewater_node
  FROM tww_od.wastewater_structure WS
  LEFT JOIN tww_od.wastewater_networkelement NE ON NE.fk_wastewater_structure = WS.obj_id
  LEFT JOIN tww_od.wastewater_node WN ON NE.obj_id = WN.obj_id
  WHERE fk_main_wastewater_node IS NULL 
  GROUP BY WS.obj_id
  ) AS structure_node
WHERE structure_node.obj_id = WS.obj_id;

[sjib]

And for main_cover:

    SET fk_main_cover = structure_cover.co_obj_id FROM ( 
	   SELECT WS.obj_id AS obj_id, min(CO.obj_id) AS co_obj_id, fk_main_cover 
	   FROM tww_od.wastewater_structure WS 
	   LEFT JOIN tww_od.structure_part SP ON SP.fk_wastewater_structure = WS.obj_id 
	   LEFT JOIN tww_od.cover CO ON SP.obj_id = CO.obj_id
	   WHERE fk_main_cover IS NULL 
	   GROUP BY WS.obj_id 
    ) AS structure_cover 
WHERE structure_cover.obj_id = WS.obj_id;

@domi4484 Please check if IS NULL is placed correctly
We can decide on Friday whether we take the option and improvements from @cymed or go with these first

[sjib]

• Add the two queries main_node and main_cover to datamodel - @sjib @cymed seperate pull request datamodel

• Add the two queries main_node and main_cover to import process @domi4484

• Add REFRESH MATERIALIZED view to import process @domi4484

• not hard code in plugin - have it in a function @domi4484

• run it always @domi4484

• integrate Insert cover only if a cover column is not null #36 and update main cover if new cover is added and fk is null #58 after testing / reviewing by @ponceta @sjib

[3nids]

should be fixed in last plugin demo, please re-open if needed