Preparation for migration QGEP -> TEKSI Wastewater & GEP

[sjib]

At the TEKSI General Assembly Stefan Burckhardt presented the planned migration path for switching from qgep (VSA-DSS 2015) to TEKSI wastewater & GEP (VSA-DSS 2020).

Have a look and read how you can already prepare for that migration and be ready when release 1.6.1 (qgep) and 20204.0 (TEKSI Wastewater & QGEP) are available.

20240313_Preparation_for_migration_QGEP_TEKSI_Wastewater_GEP.pdf

Share your thoughts and additional (possible) hurdles or needs in this discussion.

[sjib]

For VSA-DSS 2015 export you have to make sure, that all organisation have been related to one of the seven subclasses of organisation:

To do so add the respective obj_id of the organisation to the respective subclass table - this will link the subclass with the superclass:

A check to count the number of subclass elements and compare it with the number of organisation class elements was added to the qgep INTERLIS export and will help you to control if you referenced all organisations: see QGEP/qgepqwat2ili#116 (comment)

[sjib]

Integrated in pull request for 1.6.2 documentation QGEP/docs#221

[ponceta]

Pully and Belmont-sur-Lausanne : (Going through)

• Step 0 : communicate organisations to the VSA
• Step 1 : rename identifier and add municipalities in the right classes
• Step 2: check empty identifier attributes
• Step 3: check empty relation attributes: owner (EigentuemerRef) operator (BetreiberRef), dataowner (DatenherrRef), provider (DatenlieferantRef), measure.responsible_entity (TraegerschaftRef), measure.responsible_start (Massnahme.Verantwortlich_Ausloesung_StartRef) as they will be converted to "unknown"
• Step 4 : Export data to INTERLIS using the latest QGEP plugin
• Step 5 : Get the credentials to use the VSA transformer CHECKVSA
• Step 6 : Transform INTERLIS using the VSA 2015-2020.1 transformation
• Step 7 : Import INTERLIS data to TWW
• Step 8 : Check it works as well as before
• Step 9 : Adapt your exports (FME, Extract, Geoportal)
• Step 10 : Go to PROD🥇

Step 0

What are my organisations?
Are they registered in VSA Organisation dataset already?
Submit model excel sheet to [email protected]

Step 1

• Get necessary informations in the right tables

[sjib]

The VSA organisation dataset for Privat / privé is: ch20p3q400000094

<SIA405_Base_Abwasser_1_LV95.Administration.Organisation TID="ch20p3q400000094">
	<Letzte_Aenderung>20211116</Letzte_Aenderung>
	<Bemerkung>Für alle privaten Eigentümer (Sammelorganisation)</Bemerkung><Bezeichnung>Privat</Bezeichnung>
        <Organisationstyp>Privat</Organisationstyp>
        <Status>aktiv</Status>
</SIA405_Base_Abwasser_1_LV95.Administration.Organisation>

The VSA organisatin dataset for CFF / SBB is: ch20p3q400000101

<SIA405_Base_Abwasser_1_LV95.Administration.Organisation TID="ch20p3q400000101">
	<Letzte_Aenderung>20150828</Letzte_Aenderung>
	<Bezeichnung>Schweizerische Bundesbahnen</Bezeichnung>
       <Kurzbezeichnung>SBB</Kurzbezeichnung>
       <Organisationstyp>Bund</Organisationstyp>
       <Status>aktiv</Status>
       <UID>CHE102909703</UID>
</SIA405_Base_Abwasser_1_LV95.Administration.Organisation>

The VSA organisatin dataset for inconnu / unbekannt is: ch20p3q400000107

</SIA405_Base_Abwasser_1_LV95.Administration.Organisation>
	<SIA405_Base_Abwasser_LV95.Administration.Organisation TID="ch20p3q400000107">
	<Letzte_Aenderung>20211116</Letzte_Aenderung>
	<Bemerkung>Für alle Objekte mit unklarer Zuständigkeit</Bemerkung>
       <Bezeichnung>unbekannt</Bezeichnung>
       <Organisationstyp>Privat</Organisationstyp>
      <Status>aktiv</Status>
</SIA405_Base_Abwasser_1_LV95.Administration.Organisation>

[ponceta]

And ASTRA (OFROU)

<SIA405_Base_Abwasser_1_LV95.Administration.Organisation TID="ch20p3q400000108">
  <Letzte_Aenderung>20210525</Letzte_Aenderung>
  <Bezeichnung>ASTRA - Bundesamt für Strassen</Bezeichnung><Kurzbezeichnung>Astra</Kurzbezeichnung> 
  <Organisationstyp>Bund</Organisationstyp>
  <Status>aktiv</Status>
  <UID>CHE304459014</UID>
</SIA405_Base_Abwasser_1_LV95.Administration.Organisation>

Thank you!

[sjib]

Step 2 Missing / Empty identifier attributes

INTERLIS Export will do an integrity check on missing identifiers (isNull check)

[ponceta]

QGEP/QGEP#871 Technical reference

[sjib]

Step 3 Missing / Empty / Wrong fk_* referencing to class organisation

Sample Update SQL - turn of triggers to make it faster

ALTER TABLE qgep_od.reach_point DISABLE TRIGGER on_reach_point_update;
ALTER TABLE qgep_od.reach_point DISABLE TRIGGER ws_symbology_update_by_reach_point;

UPDATE 
   qgep_od.reach_point
SET 
   fk_dataowner = 'ch13p7mzOG000013'
   
WHERE  fk_dataowner ISNULL;

UPDATE 
   qgep_od.reach_point
SET 
   fk_provider = 'ch20p3q400001483' -- obj_id of organisation in VSA organisation dataset
   
WHERE  fk_provider ISNULL;

SELECT fk_dataowner, fk_provider FROM qgep_od.reach_point 
WHERE  fk_dataowner ISNULL; 

ALTER TABLE qgep_od.reach_point ENABLE TRIGGER on_reach_point_update;
ALTER TABLE qgep_od.reach_point ENABLE TRIGGER ws_symbology_update_by_reach_point;

To replace existing fk

UPDATE 
   qgep_od.reach_point
SET 
   fk_dataowner = 'ch13p7mzOG000013'
   
WHERE  fk_dataowner = 'old obj_id';

[sjib]

Here a (hopefull complete) SQL for all classes in qgep that you can adapt for your purposes
update dataowner and provider wrong obj_id.sql.zip