qgepdss/export.py - rohrprofil_geometry etc. add if filtered

[sjib]

Missing filter for selection in INTERLIS export configuration:

• [x] rohrprofil_geometrie

First test to solve https://github.com/QGEP/QGEP/issues/856

[sjib]

@urskaufmann Can you copy this adaption to your installation and test? If ok I will work on other missing classes.

[urskaufmann]

not ok. Sorry, the "correct" error is here:

Ein Fehler trat bei der Ausführung von Python-Code auf:

sqlalchemy.exc.InvalidRequestError: Don't know how to join to . Please use the .select_from() method to establish an explicit left side, as well as providing an explcit ON clause if not present already to help resolve the ambiguity. Traceback (most recent call last): File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\qgep161_prerealise/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui__init__.py", line 613, in action_do_export qgepdss_export(selection=export_dialog.selected_ids, labels_file=labels_file_path, orientation=eorientation) File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\qgep161_prerealise/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\qgepdss\export.py", line 2086, in qgep_export query = query.join( File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\query.py", line 2391, in join return self._join( File "", line 2, in _join File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\base.py", line 227, in generate fn(self, *args[1:], **kw) File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\query.py", line 2587, in _join self._join_left_to_right( File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\query.py", line 2611, in _join_left_to_right ) = self._join_determine_implicit_left_side(left, right, onclause) File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\query.py", line 2756, in _join_determine_implicit_left_side raise sa_exc.InvalidRequestError( sqlalchemy.exc.InvalidRequestError: Don't know how to join to . Please use the .select_from() method to establish an explicit left side, as well as providing an explcit ON clause if not present already to help resolve the ambiguity.

Python-Version: 3.9.18 (heads/master:5eba59e, Feb 1 2024, 20:02:10) [MSC v.1929 64 bit (AMD64)] QGIS-Version: 3.34.4-Prizren Prizren, decd43dc

[sjib]

@urskaufmann rohrprofil_geometrie is not directly connected with reach - I have to adjust the join:

[sjib]

@urskaufmann Can you test again with the updated join statement?

[sjib]

I used this example as role model for a join with several classes:

    logger.info("Exporting QGEP.dryweather_downspout -> ABWASSER.trockenwetterfallrohr, ABWASSER.metaattribute")
    query = qgep_session.query(QGEP.dryweather_downspout)
    if filtered:
        query = query.join(QGEP.wastewater_structure, QGEP.wastewater_networkelement).filter(
            QGEP.wastewater_networkelement.obj_id.in_(subset_ids)
        )

[urskaufmann]

There is another error. Seems that the pipe_profile - code now works. Special: there is for sure no Anschlussobjekt connected to the 6 reaches and there nodes that I have selected to start...

Ein Fehler trat bei der Ausführung von Python-Code auf:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "anschlussobjekt" violates foreign key constraint "anschlussobjekt_abwassernetzelementref_fkey" DETAIL: Key (abwassernetzelementref)=(546) is not present in table "abwassernetzelement". (Background on this error at: http://sqlalche.me/e/13/gkpj) Traceback (most recent call last): File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 771, in _commit_impl self.engine.dialect.do_commit(self.connection) File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 561, in do_commit dbapi_connection.commit() psycopg2.errors.ForeignKeyViolation: insert or update on table "anschlussobjekt" violates foreign key constraint "anschlussobjekt_abwassernetzelementref_fkey" DETAIL: Key (abwassernetzelementref)=(546) is not present in table "abwassernetzelement".

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\qgep161_prerealise/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui__init__.py", line 613, in action_do_export qgepdss_export(selection=export_dialog.selected_ids, labels_file=labels_file_path, orientation=eorientation) File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\qgep161_prerealise/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\qgepdss\export.py", line 3613, in qgep_export abwasser_session.commit() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 1046, in commit self.transaction.commit() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 508, in commit t[1].commit() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1762, in commit self._do_commit() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1793, in _do_commit self.connection._commit_impl() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 773, in _commit_impl self._handle_dbapi_exception(e, None, None, None, None) File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in _handle_dbapiexception util.raise( File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_ raise exception File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 771, in _commit_impl self.engine.dialect.do_commit(self.connection) File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 561, in do_commit dbapi_connection.commit() sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "anschlussobjekt" violates foreign key constraint "anschlussobjekt_abwassernetzelementref_fkey" DETAIL: Key (abwassernetzelementref)=(546) is not present in table "abwassernetzelement".

(Background on this error at: http://sqlalche.me/e/13/gkpj)

[sjib]

@urskaufmann The reason is, that we do not have defined a filter criteria for Anschlussobjekte and subclasses:

    logger.info("Exporting QGEP.building -> ABWASSER.gebaeude, ABWASSER.metaattribute")
    query = qgep_session.query(QGEP.building)
    for row in query:

With the current configuration all Anschlussobjekt (Brunnen, Einzelflaechen, Gebaeude, Reservoir) will be exported, even if they are not attached to a selected Abwassernetzelement.

[urskaufmann]

then nobody will be able to export a dss-selection if there are this filter missing, because the unfiltered records will in most cases have foregin keys to elements, that are not in the selection, and there we will get always an error.

If the export of a selection should be an option, then all this filter have to be set. At least for those tables, that are also in DSS 2020. (If somebody really has data in Subsystem Gewässer, he can not use the selection-export)

[sjib]

Progress of pull request and still needed:

• [x] Added filter to qgepdss
• [ ] Review and tests filters in qgepdss (@urskaufmann )
• [x] add filters to qgepsia405 (@sjib)
• [ ] discuss if filters should be available in qgep (VSA-KEK also or not) (Technical group meeting @ponceta

[urskaufmann]

Seems to be a step further, but got this error:

Ein Fehler trat bei der Ausführung von Python-Code auf:

sqlalchemy.exc.ArgumentError: Expected mapped entity or selectable/table as join target Traceback (most recent call last): File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\qgep161_prerealise/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui__init__.py", line 613, in action_do_export qgepdss_export(selection=export_dialog.selected_ids, labels_file=labels_file_path, orientation=eorientation) File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\qgep161_prerealise/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\qgepdss\export.py", line 2865, in qgep_export query = query.join( File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\query.py", line 2391, in join return self._join( File "", line 2, in _join File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\base.py", line 227, in generate fn(self, *args[1:], **kw) File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\query.py", line 2490, in _join raise sa_exc.ArgumentError( sqlalchemy.exc.ArgumentError: Expected mapped entity or selectable/table as join target

Python-Version: 3.9.18 (heads/master:5eba59e, Feb 1 2024, 20:02:10) [MSC v.1929 64 bit (AMD64)] QGIS-Version: 3.34.4-Prizren Prizren, decd43dc

[sjib]

I reworked the selection again.

We still have to define how to deal with reach_point_to - I have re-added it for the moment as when I make a selection "upstream" then the reach_point will be missing if I don't have it in the selection.

    logger.info("Exporting QGEP.reach_point -> ABWASSER.haltungspunkt, ABWASSER.metaattribute")
    query = qgep_session.query(QGEP.reach_point)
    if filtered:
        query = query.join(
            QGEP.reach,
            or_(
                QGEP.reach_point.obj_id == QGEP.reach.fk_reach_point_from,
           # 4.6.2024 again added
           QGEP.reach_point.obj_id == QGEP.reach.fk_reach_point_to,
            ),
        ).filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))

Is that is different if we have a reach - reach connection? I think yes - because then we will have a missing fk_abwassernetzelement (reach) that will cause problems.

To be continued ...

[sjib]

    logger.info("Exporting QGEP.measuring_point -> ABWASSER.messstelle, ABWASSER.metaattribute")
    query = qgep_session.query(QGEP.measuring_point)
    if filtered:
        query = query.join(QGEP.wastewater_structure, QGEP.wastewater_networkelement).filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids)
        )
          #  QGEP.wastewater_networkelement,
          # or does not work with this - currently do not support other connections
          #  or_(
          #     (QGEP.waste_water_treatment_plant, QGEP.wastewater_networkelement),
          #      (QGEP.wastewater_structure, QGEP.wastewater_networkelement),
           # currently do not support other connections
           #     (QGEP.water_course_segment, QGEP.river, QGEP.sector_water_body, QGEP.discharge_point, QGEP.wastewater_networkelement),
           #    )

I do not know how to have the or_ statements here

[sjib]

I do not know how to have the or_ statements here I thought about this and think we could try with UNION instead

https://www.geeksforgeeks.org/python-sqlalchemy-performing-union-with-three-queries/

[sjib]

    logger.info("Exporting QGEP.measuring_point -> ABWASSER.messstelle, ABWASSER.metaattribute")
    query = qgep_session.query(QGEP.measuring_point)
    if filtered:
        query1=query.join(QGEP.wastewater_structure, QGEP.wastewater_networkelement)
        query3=query.join(QGEP.water_course_segment, QGEP.river, QGEP.sector_water_body, QGEP.discharge_point, QGEP.wastewater_networkelement)
        query=query.union(query1, query3)
        query = query.filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids)
        )

This seems to work

[sjib]

For the 2nd option of relationship I get this error:

Ein Fehler trat bei der Ausführung von Python-Code auf:

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'measuring_point' and 'FromGrouping object'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly. Traceback (most recent call last): File "C:\Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui__init.py", line 620, in action_do_export qgepdss_export(selection=export_dialog.selected_ids, labels_file=labels_file_path, orientation=eorientation) File "C:\Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\qgepdss\export.py", line 2943, in qgep_export query2=query.join(QGEP.waste_water_treatment_plant, (QGEP.wastewater_structure, QGEP.waste_water_treatment_plant.obj_id == QGEP.wastewater_structure.fk_owner), (QGEP.wastewater_structure, QGEP.waste_water_treatment_plant.obj_id == QGEP.wastewater_structure.fk_provider),QGEP.wastewater_networkelement, File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\orm\query.py", line 2391, in join return self._join( File "", line 2, in _join File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\orm\base.py", line 227, in generate fn(self, *args[1:], **kw) File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\orm\query.py", line 2587, in _join self._join_left_to_right( File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\orm\query.py", line 2663, in _join_left_to_right orm_join( File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\orm\util.py", line 1158, in join return _ORMJoin(left, right, onclause, isouter, full) File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\orm\util.py", line 1057, in init expression.Join.init(self, left, right, onclause, isouter, full) File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\sql\selectable.py", line 765, in init__ self.onclause = self._match_primaries(self.left, self.right) File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\sql\selectable.py", line 914, in _match_primaries return self._join_condition(left, right, a_subset=left_right) File "", line 2, in _join_condition File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\util\deprecations.py", line 139, in warned return fn(*args, **kwargs) File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\sql\selectable.py", line 964, in _join_condition cls._joincond_trim_constraints( File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\sql\selectable.py", line 1080, in _joincond_trim_constraints raise exc.AmbiguousForeignKeysError( sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'measuring_point' and 'FromGrouping object'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

wastewater_structur and waste_water_treatment_plant (organisation) have two relations: fk_owner and fk_provider

        # needs to add QGEP.wastewater_structure as waste_water_treatment_plant is a subclass of organisation that has a relation to wastewater_structure and then wastewater_networkelement
        #variant1 for query2

        query2=query.join(QGEP.waste_water_treatment_plant, (QGEP.wastewater_structure, QGEP.waste_water_treatment_plant.obj_id == QGEP.wastewater_structure.fk_owner), (QGEP.wastewater_structure, QGEP.waste_water_treatment_plant.obj_id == QGEP.wastewater_structure.fk_provider),QGEP.wastewater_networkelement,
         )

[sjib]

@urskaufmann Can you test again and give me a feedback if it works with your data? I was able to export a selection. It is not perfect yet but a step forward.

[urskaufmann]

If I export a very small selection with node on each side, then it works. (seems the filtering works no) If I export a very small selection with Blindanschluss at the end of the selection, I get this error (seems to be the same error as at the beginning):

Ein Fehler trat bei der Ausführung von Python-Code auf:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "haltungspunkt" violates foreign key constraint "haltungspunkt_abwassernetzelementref_fkey" DETAIL: Key (abwassernetzelementref)=(24) is not present in table "abwassernetzelement". (Background on this error at: http://sqlalche.me/e/13/gkpj) Traceback (most recent call last): File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 771, in _commit_impl self.engine.dialect.do_commit(self.connection) File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 561, in do_commit dbapi_connection.commit() psycopg2.errors.ForeignKeyViolation: insert or update on table "haltungspunkt" violates foreign key constraint "haltungspunkt_abwassernetzelementref_fkey" DETAIL: Key (abwassernetzelementref)=(24) is not present in table "abwassernetzelement".

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\qgep161_prerealise/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui__init__.py", line 613, in action_do_export qgepdss_export(selection=export_dialog.selected_ids, labels_file=labels_file_path, orientation=eorientation) File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\qgep161_prerealise/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\qgepdss\export.py", line 3796, in qgep_export abwasser_session.commit() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 1046, in commit self.transaction.commit() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 508, in commit t[1].commit() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1762, in commit self._do_commit() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1793, in _do_commit self.connection._commit_impl() File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 773, in _commit_impl self._handle_dbapi_exception(e, None, None, None, None) File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in _handle_dbapiexception util.raise( File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_ raise exception File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 771, in _commit_impl self.engine.dialect.do_commit(self.connection) File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 561, in do_commit dbapi_connection.commit() sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "haltungspunkt" violates foreign key constraint "haltungspunkt_abwassernetzelementref_fkey" DETAIL: Key (abwassernetzelementref)=(24) is not present in table "abwassernetzelement".

(Background on this error at: http://sqlalche.me/e/13/gkpj)

Python-Version: 3.9.18 (heads/master:5eba59e, Feb 1 2024, 20:02:10) [MSC v.1929 64 bit (AMD64)] QGIS-Version: 3.34.4-Prizren Prizren, decd43dc

[sjib]

then we have two conflicting cases - either we filter out all to_reach_points or all from_reach_points. But we should only not fill in data in reach_point.fk_wastewater_network element of the last reach_point of the selection.

This can currently not be done with this kind of filter structure - this can not change one single attribute of one specific data set- and needs more thoughts.

[sjib]

Idea:

filter out reach_point.fk_wastewater_networkelement that point to elements that are not in subset_ids https://www.slingacademy.com/article/use-in-and-not-in-operators-in-sqlalchemy/#Using_IN_and_NOT_IN_with_SQLAlchemy

query_rp_wwn = (~QGEP.reach_point.fk_wastewater_networkelement.in_(subset_ids))

and in this case not write the abwassernetzelementref

    haltungspunkt = ABWASSER.haltungspunkt(
        # FIELDS TO MAP TO ABWASSER.haltungspunkt
        # --- baseclass ---
        # --- sia405_baseclass ---
        **base_common(row, "haltungspunkt"),
        # --- haltungspunkt ---

        **abwassernetzelementref=get_tid(row.fk_wastewater_networkelement__REL),**

Needs a new function check_fk_in_subsetid that checks, whether the result of get_tid(row.fk_wastewater_networkelement__REL) is in the subset_ids

[sjib]

New function check_fk_in_subsetid

    def check_fk_in_subsetid (foreignkey2, subset):
        """
        checks, whether foreignkey is in the subset_ids - if yes it return the foreignkey, if no it will return NULL
        """
        logger.info(f"check_fk_in_subsetid -  Subset ID's '{subset}'")
        logger.info(f"check_fk_in_subsetid -  foreignkey '{foreignkey2}'")

        if foreignkey2 in subset:
            logger.info(f"check_fk_in_subsetid - '{foreignkey2}' is in subset ")
        else:
            logger.info(f"check_fk_in_subsetid - '{foreignkey2}' is not in subset - replaced with None instead!")
            foreignkey2 = None
        return foreignkey2

That is called like this

abwassernetzelementref=get_tid(check_fk_in_subsetid((row.fk_wastewater_networkelement__REL), subset_ids)),

Any idea why it is not passing on row.fk_wastewater_networkelement__REL

2024-06-12T17:24:35        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils: fk_wastewater_networkelement = 
2024-06-12T17:24:35        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - Subset ID's '['ch18ynpb00000003', 'ch18ynpb00000008', 'ch18ynpb00000013', 'ch18ynpb00000018', 'ch18ynpb00000023', 'ch18ynpb00000028', 'ch18ynpb00000033', 'ch18ynpb00000038', 'ch18ynpb00000043', 'ch18ynpb00000048', 'ch18ynpb00000053', 'ch18ynpb00000058', 'ch18ynpb00000063', 'ch18ynpb00000068', 'ch18ynpb00000073', 'ch18ynpb00000078', 'ch18ynpb00000083', 'ch18ynpb00000088', 'ch18ynpb00000093', 'ch18ynpb00000098', 'ch18ynpb00000108', 'ch18ynpb00000113', 'ch18ynpb00000118', 'ch18ynpb00000127', 'ch18ynpb00000118', 'ch18ynpb00000083', 'ch18ynpb00000136', 'ch18ynpb00000058', 'ch18ynpb00000113', 'ch18ynpb00000145', 'ch18ynpb00000003', 'ch18ynpb00000118', 'ch18ynpb00000154', 'ch18ynpb00000008', 'ch18ynpb00000013', 'ch18ynpb00000163', 'ch18ynpb00000033', 'ch18ynpb00000003', 'ch18ynpb00000172', 'ch18ynpb00000038', 'ch18ynpb00000033', 'ch18ynpb00000181', 'ch18ynpb00000048', 'ch18ynpb00000038', 'ch18ynpb00000190', 'ch18ynpb00000053', 'ch18ynpb00000048', 'ch18ynpb00000199', 'ch18ynpb00000063', 'ch18ynpb00000053', 'ch18ynpb00000208', 'ch18ynpb00000043', 'ch18ynpb00000063', 'ch18ynpb00000217', 'ch18ynpb00000068', 'ch18ynpb00000043', 'ch18ynpb00000226', 'ch18ynpb00000083', 'ch18ynpb00000058', 'ch18ynpb00000235', 'ch18ynpb00000078', 'ch18ynpb00000068', 'ch18ynpb00000244', 'ch18ynpb00000073', 'ch18ynpb00000078', 'ch18ynpb00000253', 'ch18ynpb00000113', 'ch18ynpb00000028', 'ch18ynpb00000262', 'ch18ynpb00000028', 'ch18ynpb00000008', 'ch18ynpb00000271', 'ch18ynpb00000093', 'ch18ynpb00000088', 'ch18ynpb00000289', 'ch18ynpb00000023', 'ch18ynpb00000018', 'ch18ynpb00000298', 'ch18ynpb00000018', 'ch18ynpb00000098', 'ch18ynpb00000307', 'ch18ynpb00000098', 'ch18ynpb00000108', 'ch18ynpb00000316', 'ch18ynpb00000088', 'ch18ynpb00000023', 'ch18ynpb00000325', 'ch18ynpb00000013', 'ch18ynpb00000093']'
2024-06-12T17:24:35        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - foreignkey ''
2024-06-12T17:24:35        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - '' is not in subset - replaced with None instead!
2024-06-12T17:24:35        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils: fk_wastewater_networkelement = 
2024-06-12T17:24:35        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - Subset ID's '['ch18ynpb00000003', 'ch18ynpb00000008', 'ch18ynpb00000013', 'ch18ynpb00000018', 'ch18ynpb00000023', 'ch18ynpb00000028', 'ch18ynpb00000033', 'ch18ynpb00000038', 'ch18ynpb00000043', 'ch18ynpb00000048', 'ch18ynpb00000053', 'ch18ynpb00000058', 'ch18ynpb00000063', 'ch18ynpb00000068', 'ch18ynpb00000073', 'ch18ynpb00000078', 'ch18ynpb00000083', 'ch18ynpb00000088', 'ch18ynpb00000093', 'ch18ynpb00000098', 'ch18ynpb00000108', 'ch18ynpb00000113', 'ch18ynpb00000118', 'ch18ynpb00000127', 'ch18ynpb00000118', 'ch18ynpb00000083', 'ch18ynpb00000136', 'ch18ynpb00000058', 'ch18ynpb00000113', 'ch18ynpb00000145', 'ch18ynpb00000003', 'ch18ynpb00000118', 'ch18ynpb00000154', 'ch18ynpb00000008', 'ch18ynpb00000013', 'ch18ynpb00000163', 'ch18ynpb00000033', 'ch18ynpb00000003', 'ch18ynpb00000172', 'ch18ynpb00000038', 'ch18ynpb00000033', 'ch18ynpb00000181', 'ch18ynpb00000048', 'ch18ynpb00000038', 'ch18ynpb00000190', 'ch18ynpb00000053', 'ch18ynpb00000048', 'ch18ynpb00000199', 'ch18ynpb00000063', 'ch18ynpb00000053', 'ch18ynpb00000208', 'ch18ynpb00000043', 'ch18ynpb00000063', 'ch18ynpb00000217', 'ch18ynpb00000068', 'ch18ynpb00000043', 'ch18ynpb00000226', 'ch18ynpb00000083', 'ch18ynpb00000058', 'ch18ynpb00000235', 'ch18ynpb00000078', 'ch18ynpb00000068', 'ch18ynpb00000244', 'ch18ynpb00000073', 'ch18ynpb00000078', 'ch18ynpb00000253', 'ch18ynpb00000113', 'ch18ynpb00000028', 'ch18ynpb00000262', 'ch18ynpb00000028', 'ch18ynpb00000008', 'ch18ynpb00000271', 'ch18ynpb00000093', 'ch18ynpb00000088', 'ch18ynpb00000289', 'ch18ynpb00000023', 'ch18ynpb00000018', 'ch18ynpb00000298', 'ch18ynpb00000018', 'ch18ynpb00000098', 'ch18ynpb00000307', 'ch18ynpb00000098', 'ch18ynpb00000108', 'ch18ynpb00000316', 'ch18ynpb00000088', 'ch18ynpb00000023', 'ch18ynpb00000325', 'ch18ynpb00000013', 'ch18ynpb00000093']'

I can also not pass it to the logger: logger.info(f" fk_wastewater_networkelement = {(row.fk_wastewater_networkelement__REL)}")

produces this: 2024-06-12T17:24:35 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils: fk_wastewater_networkelement =

Do I have to convert anything?

The original call was working: abwassernetzelementref=get_tid(row.fk_wastewater_networkelement__REL),

@cymed @ponceta Any ideas?

[sjib]

I think I found the solution:

row.fk_wastewater_networkelement__REL is a relation object

So I have to use getattrto get the string of the foreignkey attribute.

 # get the value of the fk_ attribute as str out of the relation to be able to check whether it is in the subset
fremdschluesselstr = getattr(relation, "obj_id")

@domi4484 Does this look ok?

[sjib]

Ein Fehler trat bei der Ausführung von Python-Code auf: 

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) FEHLER:  Einf��gen oder Aktualisieren in Tabelle ��einzugsgebiet�� verletzt Fremdschl��ssel-Constraint ��einzugsgebiet_abwassernetzelmnt_sw_stref_fkey�� DETAIL:  Schl��ssel (abwassernetzelement_sw_istref)=(32) ist nicht in Tabelle ��abwassernetzelement�� vorhanden. (Background on this error at: http://sqlalche.me/e/13/gkpj) 
Traceback (most recent call last):
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\engine\base.py", line 771, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\engine\default.py", line 561, in do_commit
    dbapi_connection.commit()
psycopg2.errors.ForeignKeyViolation: FEHLER:  Einf��gen oder Aktualisieren in Tabelle ��einzugsgebiet�� verletzt Fremdschl��ssel-Constraint ��einzugsgebiet_abwassernetzelmnt_sw_stref_fkey��
DETAIL:  Schl��ssel (abwassernetzelement_sw_istref)=(32) ist nicht in Tabelle ��abwassernetzelement�� vorhanden.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui\__init__.py", line 620, in action_do_export
    qgepdss_export(selection=export_dialog.selected_ids, labels_file=labels_file_path, orientation=eorientation)
  File "C:\Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\qgepdss\export.py", line 3813, in qgep_export
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\orm\session.py", line 1046, in commit
    self.transaction.commit()
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\orm\session.py", line 508, in commit
    t[1].commit()
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\engine\base.py", line 1762, in commit
    self._do_commit()
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\engine\base.py", line 1793, in _do_commit
    self.connection._commit_impl()
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\engine\base.py", line 773, in _commit_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\engine\base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\engine\base.py", line 771, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
  File "C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages\sqlalchemy\engine\default.py", line 561, in do_commit
    dbapi_connection.commit()
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) FEHLER:  Einf��gen oder Aktualisieren in Tabelle ��einzugsgebiet�� verletzt Fremdschl��ssel-Constraint ��einzugsgebiet_abwassernetzelmnt_sw_stref_fkey��
DETAIL:  Schl��ssel (abwassernetzelement_sw_istref)=(32) ist nicht in Tabelle ��abwassernetzelement�� vorhanden.

(Background on this error at: http://sqlalche.me/e/13/gkpj)

Python-Version: 3.9.5 (tags/v3.9.5:0a7dcbd, May  3 2021, 17:27:52) [MSC v.1928 64 bit (AMD64)] 
QGIS-Version: 3.28.4-Firenze Firenze, fd0fb72ab3 

Python-Pfad:
C:\Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins\generate_swmm_inp
C:/PROGRA~1/QGIS 3.28.4/apps/qgis-ltr/./python
C:/Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python
C:/Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins
C:/PROGRA~1/QGIS 3.28.4/apps/qgis-ltr/./python/plugins
C:\Program Files\QGIS 3.28.4\bin\python39.zip
C:\PROGRA~1\QGIS 3.28.4\apps\Python39\DLLs
C:\PROGRA~1\QGIS 3.28.4\apps\Python39\lib
C:\Program Files\QGIS 3.28.4\bin
C:\Users\Stefan\AppData\Roaming\Python\Python39\site-packages
C:\PROGRA~1\QGIS 3.28.4\apps\Python39
C:\PROGRA~1\QGIS 3.28.4\apps\Python39\lib\site-packages
C:\PROGRA~1\QGIS 3.28.4\apps\Python39\lib\site-packages\win32
C:\PROGRA~1\QGIS 3.28.4\apps\Python39\lib\site-packages\win32\lib
C:\PROGRA~1\QGIS 3.28.4\apps\Python39\lib\site-packages\Pythonwin
C:/Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python
C:/Daten/QGEP/testing_importspeed
C:\Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui\..\..
C:\Users/Stefan/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui\..\..
.

[sjib]

Check if it is needed to add check_fk_in_subsetid also to relations from catchment_area to wastewater_structure like abwassernetzelement_sw_istref, as rw and sw can be on different nodes that you possibly have not selected.

@urskaufmann What do you think?

[sjib]

@urskaufmann I updated qgepdss in catchment_area with check_fk_in_subsetid instead of get_tid. Any other classes and fk we should adapt?

[sjib]

General question: Should we change the logger info in check_fk_in_subsetid from info to warning as we are changing existing data on the export?

      if fremdschluesselstr in subset:
            logger.info(f"check_fk_in_subsetid - '{fremdschluesselstr}' is in subset ")
            logger.info(f"check_fk_in_subsetid - tid = '{tid_maker.tid_for_row(relation)}' ")
            return tid_maker.tid_for_row(relation)
        else:
            logger.info(f"check_fk_in_subsetid - '{fremdschluesselstr}' is not in subset - replaced with None instead!")
            return None

And adapt the text: replaced with None instead for this selection export!

@urskaufmann @TriformMoritz @cymed @tproduit @varrieta

[sjib]

Urs has checked this morning. It seems to work generally. One reach more is selected than expected on reach - reach connections. And there was an issues when a wwtp_structure was included. I will check on this right away.

[sjib]

Documentation: Selected reach for export:

List of exported elements contains 2 reaches and it's reach points - meaning the second reach was also selected:

Second reach is in Subset_ID's - why?

2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:Exporting QGEP.reach_point -> ABWASSER.haltungspunkt, ABWASSER.metaattribute
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - Subset ID's '['ch000000RE000001', 'NULL', 'ch000000RE000000']'
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - fremdschluesselstr 'ch000000WN000001'
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - 'ch000000WN000001' is not in subset - replaced with None instead!
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - Subset ID's '['ch000000RE000001', 'NULL', 'ch000000RE000000']'
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - fremdschluesselstr 'ch000000WN000000'
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - 'ch000000WN000000' is not in subset - replaced with None instead!
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - Subset ID's '['ch000000RE000001', 'NULL', 'ch000000RE000000']'
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - fremdschluesselstr 'ch000000RE000000'
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - 'ch000000RE000000' is in subset 
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:check_fk_in_subsetid - tid = '22' 
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:done
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:Exporting QGEP.wastewater_node -> ABWASSER.abwasserknoten, ABWASSER.metaattribute
2024-06-18T15:46:01        qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:done

[sjib]

Subset is already like this, when we pass it to export.py

2024-06-18T17:44:36 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:print subset_ids '['ch000000RE000001', 'NULL', 'ch000000RE000000']'

[sjib]

I think I found why this is like that: gui_export.py has this:

    @property
    def selected_ids(self):
        if self.limit_checkbox.isChecked():
            ids = []
            for struct in self.structures:
                ids.append(str(struct["wn_obj_id"]))
            for reach in self.reaches:
                ids.append(str(reach["obj_id"]))
                ids.append(str(reach["rp_from_fk_wastewater_networkelement"]))
                ids.append(str(reach["rp_to_fk_wastewater_networkelement"]))
            return ids
        else:
            return None

If rp_to_fk_wastewater_networkelement is another reach then the connected reach is also in selection

[sjib]

This also leads to that if I just select the one reach - the connected wastewater_nodes (and structures) are selected automatically.

@urskaufmann So I think we have to decide what we want - as those two things seem conflicting.

[sjib]

With wwt_structure I did not have problems.

[sjib]

This adds turn on flag_test to allow subclass test for wastewater_structures

2024-06-19T11:31:23 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:INTEGRITY CHECK wastewater_structures subclass data... 2024-06-19T11:31:23 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:Number of wastewater_structure datasets: 2383 2024-06-19T11:31:23 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:Number of manhole datasets: 969 2024-06-19T11:31:23 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:Number of channel datasets: 1386 2024-06-19T11:31:23 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:Number of special_structure datasets: 17 2024-06-19T11:31:23 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:Number of infiltration_installation datasets: 3 2024-06-19T11:31:23 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:Number of discharge_point datasets: 7 2024-06-19T11:31:23 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:Number of wwtp_structure datasets: 1 2024-06-19T11:31:23 qgepplugin.qgepqwat2ili.qgepqwat2ili.utils:OK: number of subclass elements of class wastewater_structure OK in schema qgep_od!

[urskaufmann]

@sjib : as discussed: selecting all reaches that have Blindanschluss (with field function_hierarchic to distinguish pwwf and swwf)

SELECT re.obj_id, re.progression_geometry, ch.function_hierarchic, reto.obj_id AS to_obj_id FROM qgep_od.reach re LEFT JOIN qgep_od.reach_point rpto ON rpto.obj_id::text = re.fk_reach_point_to::text LEFT JOIN qgep_od.wastewater_networkelement neto ON neto.obj_id::text = rpto.fk_wastewater_networkelement::text LEFT JOIN qgep_od.reach reto ON reto.obj_id::text = neto.obj_id::text LEFT JOIN qgep_od.wastewater_networkelement ne ON ne.obj_id::text = re.obj_id::text LEFT JOIN qgep_od.channel ch ON ch.obj_id::text = ne.fk_wastewater_structure::text WHERE reto.obj_id IS NOT NULL;

[sjib]

@urskaufmann Can you check the subclasses with this code to make sure the number of wws is the same as the sum of all subclasses of wws.

-- function for counting number of object in tables (german classnames)

-- SELECT qgep_od.count_elements();

-- TO DO - alle Subklassen von Abwasserbauwerk auch für Erweiterungen (ALR)
-- TO DO anpassen auf Release 2020

CREATE OR REPLACE FUNCTION qgep_od.count_elements()
  RETURNS text AS
$BODY$
DECLARE
  table_elements text;
--  table_elements_organisation text;
  list text;
  check_structure_parts integer;
  check_organisations integer;
  check_wastewater_networkelements integer;
  check_wastewater_structures integer;
  -- neu 13.4.2022
  check_overflows integer;

  structure_parts integer;
  organisations integer;
  wastewater_networkelements integer;
  wastewater_structures integer;
  -- neu 13.4.2022
  overflows integer;

BEGIN

list = 'List of elements in tables: ,';

-- organisation und subklassen

list = list  || 'organisation: ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.organisation;
list = list || table_elements || ', ';

-- anzahl einträge organisation auf organisations schreiben
organisations = table_elements::integer;
-- totalanzahl auf check_organisations kopieren
check_organisations = organisations;

list = list  || 'administrative_office: ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.administrative_office;
list = list || table_elements || ', ';

-- anzahl administrative_office abziehen
check_organisations = check_organisations - table_elements::integer;

list = list  || 'abwasserverband: ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.waste_water_association;
list = list || table_elements || ', ';

-- anzahl abwasserverband abziehen
check_organisations = check_organisations - table_elements::integer;

list = list  || 'gemeinde: ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.municipality;
list = list || table_elements || ', ';

-- anzahl gemeinde abziehen
check_organisations = check_organisations - table_elements::integer;

list = list  || 'canton: ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.canton;
list = list || table_elements || ', ';

-- anzahl canton abziehen
check_organisations = check_organisations - table_elements::integer;

list = list  || 'cooperative: ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.cooperative;
list = list || table_elements || ', ';

-- anzahl cooperative abziehen
check_organisations = check_organisations - table_elements::integer;

list = list  || 'privat: ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.private;
list = list || table_elements || ', ';

-- anzahl privat abziehen
check_organisations = check_organisations - table_elements::integer;

-- neu 11.4.2022 zusätzlich abwasserreinigungsanlage
list = list  || 'abwasserreinigungsanlage: ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.waste_water_treatment_plant;
list = list || table_elements || ', ';

-- anzahl abwasserreinigungsanlage abziehen
check_organisations = check_organisations - table_elements::integer;

-- falls null ist gut, sonst 
IF check_organisations <> 0 THEN 
    list = list || 'ERROR: number of subclass elements of organisations NOT CORRECT in schema vsa_dss_2015_2_d: checksum = ' || check_organisations || ', ';
ELSE
    list = list || ' - OK: number of subclass elements of organisations OK in schema vsa_dss_2015_2_d !' || ', ';
END IF;

-- abwasserbauwerk und subklassen

list = list  || 'abwasserbauwerk (wastewater_structure): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.wastewater_structure;
list = list || table_elements || ', ';

-- anzahl einträge bauwerksteil auf wastewater_structures schreiben
wastewater_structures = table_elements::integer;
-- totalanzahl auf check_wastewater_structures kopieren
check_wastewater_structures = wastewater_structures;

list = list  || 'abwasserbauwerk_text (wastewater_structure_text): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.wastewater_structure_text;
list = list || table_elements || ', ';
list = list  || 'abwasserbauwerk_symbol (wastewater_structure_symbol): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.wastewater_structure_symbol;
list = list || table_elements || ', ';

list = list  || 'kanal (channel): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.channel;
list = list || table_elements || ', ';

-- anzahl kanal abziehen
check_wastewater_structures = check_wastewater_structures - table_elements::integer;

list = list  || 'normschacht (manhole): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.manhole;
list = list || table_elements || ', ';

-- anzahl normschacht abziehen
check_wastewater_structures = check_wastewater_structures - table_elements::integer;

list = list  || 'einleitstelle (discharge_point): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.discharge_point;
list = list || table_elements || ', ';

-- anzahl einleitstelle abziehen
check_wastewater_structures = check_wastewater_structures - table_elements::integer;

list = list  || 'spezialbauwerk (special_structure): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.special_structure;
list = list || table_elements || ', ';

-- anzahl spezialbauwerk abziehen
check_wastewater_structures = check_wastewater_structures - table_elements::integer;

list = list  || 'versickerungsanlage (infiltration_installation): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.infiltration_installation;
list = list || table_elements || ', ';

-- anzahl versickerungsanlage abziehen
check_wastewater_structures = check_wastewater_structures - table_elements::integer;

list = list  || 'ARABauwerk (wwtp_structure): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.wwtp_structure;
list = list || table_elements || ', ';

-- anzahl ARABauwerk abziehen
check_wastewater_structures = check_wastewater_structures - table_elements::integer;

-- falls null ist gut, sonst 
IF check_wastewater_structures <> 0 THEN 
    list = list || 'ERROR: number of subclass elements of abwassrbauwrk NOT CORRECT in schema vsa_dss_2015_2_d: checksum = ' || check_wastewater_structures || ', ';
ELSE
    list = list || ' OK: number of subclass elements of abwasserbauwerk OK in schema vsa_dss_2015_2_d !' || ', ';
END IF;

-- rohrprofil
list = list  || 'rohrprofil (pipe_profile): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.pipe_profile;
list = list || table_elements || ', ';

-- abwassernetzelemente und subklassen
list = list  || 'abwassernetzelement (wastewater_networkelement): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.wastewater_networkelement;
list = list || table_elements || ', ';

-- anzahl einträge abwassernetzelement auf check_wastewater_networkelements schreiben
wastewater_networkelements = table_elements::integer;
-- totalanzahl auf check_wastewater_networkelements kopieren
check_wastewater_networkelements = wastewater_networkelements;

list = list  || 'abwasserknoten (wastewater_node): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.wastewater_node;
list = list || table_elements || ', ';

-- anzahl abwasserknoten abziehen
check_wastewater_networkelements = check_wastewater_networkelements - table_elements::integer;

list = list  || 'haltung (reach): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.reach;
list = list || table_elements || ', ';

-- anzahl haltung abziehen
check_wastewater_networkelements = check_wastewater_networkelements - table_elements::integer;

list = list  || 'haltung_text (reach_text): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.reach_text;
list = list || table_elements || ', ';

-- falls null ist gut, sonst 
IF check_wastewater_networkelements <> 0 THEN 
    list = list || 'ERROR: number of subclass elements of abwassernetzelement NOT CORRECT in schema vsa_dss_2015_2_d: checksum = ' || check_wastewater_networkelements || ', ';
ELSE
    list = list || ' OK: number of subclass elements of abwassernetzelement OK in schema vsa_dss_2015_2_d !' || ', ';
END IF;

-- haltungspunkte
list = list  || 'haltungspunkt (reach_point): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.reach_point;
list = list || table_elements || ', ';

-- bauwerksteil und subklassen
list = list  || 'bauwerksteil (structure_part): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.structure_part;
list = list || table_elements || ', ';

-- anzahl einträge bauwerksteil auf structure_parts schreiben
structure_parts = table_elements::integer;
-- totalanzahl auf check_structure_parts kopieren
check_structure_parts = structure_parts;

list = list  || 'trockenwetterfallrohr (dryweather_downspout): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.dryweather_downspout;
list = list || table_elements || ', ';

-- anzahl trockenwetterfallrohr abziehen
check_structure_parts = check_structure_parts - table_elements::integer;

list = list  || 'einstiegshilfe (access_aid): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.access_aid;
list = list || table_elements || ', ';

-- anzahl einstiegshilfe abziehen
check_structure_parts = check_structure_parts - table_elements::integer;

list = list  || 'trockenwetterrinne (dryweather_flume): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.dryweather_flume;
list = list || table_elements || ', ';

-- anzahl trockenwetterrinne abziehen
check_structure_parts = check_structure_parts - table_elements::integer;

list = list  || 'deckel (cover): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.cover;
list = list || table_elements || ', ';

-- anzahl deckel abziehen
check_structure_parts = check_structure_parts - table_elements::integer;

list = list  || 'bankett (benching): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.benching;
list = list || table_elements || ', ';

-- anzahl bankett abziehen
check_structure_parts = check_structure_parts - table_elements::integer;

-- falls null ist gut, sonst 
IF check_structure_parts <> 0 THEN 
    list = list || 'ERROR: number of subclass elements of bauwerksteil NOT CORRECT in schema vsa_dss_2015_2_d: checksum = ' || check_structure_parts || ', ';
ELSE
    list = list || ' OK: number of subclass elements of bauwerksteil OK in schema vsa_dss_2015_2_d !' || ', ';
END IF;

-- neu 13.4.2022
-- ueberlauf und subklassen
list = list  || 'ueberlauf (overflow): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.overflow;
list = list || table_elements || ', ';

-- anzahl einträge ueberlauf auf overflows schreiben
overflows = table_elements::integer;
-- totalanzahl auf check_overflows kopieren
check_overflows = overflows;

list = list  || 'streichwehr (prank_weir): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.prank_weir;
list = list || table_elements || ', ';

-- anzahl streichwehr abziehen
check_overflows = check_overflows - table_elements::integer;

list = list  || 'leapingwehr (leapingweir): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.leapingweir;
list = list || table_elements || ', ';

-- anzahl leapingwehr abziehen
check_overflows = check_overflows - table_elements::integer;

list = list  || 'foerderaggregat (pump): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.pump;
list = list || table_elements || ', ';

-- anzahl foerderaggregat abziehen
check_overflows = check_overflows - table_elements::integer;

-- falls null ist gut, sonst 
IF check_overflows <> 0 THEN 
    list = list || 'ERROR: number of subclass elements of ueberlauf NOT CORRECT in schema vsa_dss_2015_2_d: checksum = ' || check_overflows || ', ';
ELSE
    list = list || ' OK: number of subclass elements of ueberlauf OK in schema vsa_dss_2015_2_d !' || ', ';
END IF;

-- einzugsgebiet
list = list  || 'einzugsgebiet (catchment_area): ';
SELECT COUNT(obj_id) INTO table_elements FROM qgep_od.catchment_area;
list = list || table_elements || ', ';

RETURN list;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

And then call with SELECT qgep_od.count_elements();

You should then get something like that with OK or NOT OK

"List of elements in tables: ,organisation: 15, administrative_office: 8, abwasserverband: 0, gemeinde: 1, canton: 1, cooperative: 0, privat: 5, abwasserreinigungsanlage: 0,  - OK: number of subclass elements of organisations OK in schema vsa_dss_2015_2_d !, 

abwasserbauwerk (wastewater_structure): 2383, abwasserbauwerk_text (wastewater_structure_text): 0, abwasserbauwerk_symbol (wastewater_structure_symbol): 0, kanal (channel): 1386, normschacht (manhole): 969, einleitstelle (discharge_point): 7, spezialbauwerk (special_structure): 17, versickerungsanlage (infiltration_installation): 3, ARABauwerk (wwtp_structure): 1,  OK: number of subclass elements of abwasserbauwerk OK in schema vsa_dss_2015_2_d !, 

rohrprofil (pipe_profile): 3, abwassernetzelement (wastewater_networkelement): 2412, abwasserknoten (wastewater_node): 1026, haltung (reach): 1386, haltung_text (reach_text): 0,  OK: number of subclass elements of abwassernetzelement OK in schema vsa_dss_2015_2_d !, haltungspunkt (reach_point): 2772, bauwerksteil (structure_part): 881, trockenwetterfallrohr (dryweather_downspout): 0, einstiegshilfe (access_aid): 1, trockenwetterrinne (dryweather_flume): 0, deckel (cover): 880, bankett (benching): 0,  OK: number of subclass elements of bauwerksteil OK in schema vsa_dss_2015_2_d !, ueberlauf (overflow): 5, streichwehr (prank_weir): 2, leapingwehr (leapingweir): 1, foerderaggregat (pump): 2,  OK: number of subclass elements of ueberlauf OK in schema vsa_dss_2015_2_d !, einzugsgebiet (catchment_area): 634, "

[sjib]

@urskaufmann As discussed I have added the issue about additional reaches out of this pull request and opened a new issue for that: https://github.com/QGEP/qgepqwat2ili/issues/150

We can do the same for wwtp_structure if needed.

[sjib]

@ponceta ready to review and then merge.

Currently working on same adaptions for tww https://github.com/teksi/wastewater/pull/266

[sjib]

• [x] leapingweir - wrong class in OR statement

  logger.info("Exporting QGEP.leapingweir -> ABWASSER.leapingwehr, ABWASSER.metaattribute") query = qgep_session.query(QGEP.leapingweir)

  to check if fk_overflow_char also has to be considered

  if filtered: query = query.join( QGEP.wastewaternode, or( QGEP.wastewater_node.obj_id == QGEP.prank_weir.fk_wastewater_node, QGEP.wastewater_node.obj_id == QGEP.prank_weir.fk_overflow_to, ), ).filter(QGEP.wastewater_networkelement.objid.in(subset_ids))

[sjib]

to do backporting from tww

• [x] hq_relation missing join with overflow_char - to backport on qgep, but no filter - always export all with a link to overflow_char
• [x] filter hydraulic_char_data - wwn instead of wn - backport to qgep also

[sjib]

Testing export individual_surfaces

DSS_2020_1_LV95.Siedlungsentwaesserung BID=DSS_2020_1_LV95.Siedlungsentwaesserung
Info:      12 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Abwasserknoten
Info:      11 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Deckel
Info:       1 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Einzugsgebiet
Info:       6 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.HQ_Relation
Info:      13 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Haltung
Info:      26 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Haltungspunkt
Info:      13 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Kanal
Info:       3 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Massnahme
Info:      11 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Normschacht
Info:       1 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Rohrprofil
Info:       1 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Stammkarte
Info:       2 objects in CLASS DSS_2020_1_LV95.Siedlungsentwaesserung.Ueberlaufcharakteristik
Info: ...validation done

Not yet as expected - to check if data is correct