Error upon import V - fk_wastewater_structure not present in table wastewater_structure

[meierrom]

Hi folks,

This issue seems to be related to a bug in qgepqwat2ili.

This is the xtf-file containing just two entries:

my_export_sia405_ns_knoten_min1x.xtf.zip

I'm getting the following output when issuing the following import command:

python -m qgepqwat2ili qgep import arbon/my_export_sia405_ns_knoten_min1x.xtf --log

roman@tjener:~/qgisfolder$ python -m qgepqwat2ili qgep import arbon/my_export_sia405_ns_knoten_min1x.xtf --log INFO VALIDATING XTF DATA... INFO EXECUTING: "java" -jar "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/bin/ilivalidator-1.11.9/ilivalidator-1.11.9.jar" --modeldir "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/ili" --log "arbon/my_export_sia405_ns_knoten_min1x.xtf.230517093057.ilivalidate.log" "arbon/my_export_sia405_ns_knoten_min1x.xtf" INFO CONNECTING TO DATABASE... INFO DROPPING THE SCHEMA pg2ili_abwasser... INFO CREATING THE SCHEMA pg2ili_abwasser... INFO ILIDB SCHEMAIMPORT INTO pg2ili_abwasser... INFO EXECUTING: "java" -jar "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/bin/ili2pg-4.5.0-bindist/ili2pg-4.5.0.jar" --schemaimport --dbhost "localhost" --dbport "5434" --dbusr "" --dbpwd "" --dbdatabase "" --dbschema pg2ili_abwasser --setupPgExt --createGeomIdx --createFk --createFkIdx --createTidCol --importTid --noSmartMapping --defaultSrsCode 2056 --log "arbon/my_export_sia405_ns_knoten_min1x.xtf.230517093101.ilicreate.log" --nameLang de /skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/ili/VSA_KEK_2019_2_d_LV95-20210120.ili INFO IMPORTING XTF DATA... INFO EXECUTING: "java" -jar "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/bin/ili2pg-4.5.0-bindist/ili2pg-4.5.0.jar" --import --deleteData --dbhost "localhost" --dbport "5434" --dbusr "" --dbpwd "" --dbdatabase "" --dbschema "pg2ili_abwasser" --modeldir "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/ili" --disableValidation --skipReferenceErrors --createTidCol --noSmartMapping --defaultSrsCode 2056 --log "arbon/my_export_sia405_ns_knoten_min1x.xtf.230517093120.iliimport.log" "arbon/my_export_sia405_ns_knoten_min1x.xtf" INFO Importing ABWASSER.organisation, ABWASSER.metaattribute -> QGEP.organisation INFO done INFO Importing ABWASSER.kanal, ABWASSER.metaattribute -> QGEP.channel INFO done INFO Importing ABWASSER.normschacht, ABWASSER.metaattribute -> QGEP.manhole WARNING Could not find value None in value list "qgep_vl.wastewater_structure_accessibility". Setting to None instead. WARNING Could not find value None in value list "qgep_vl.wastewater_structure_financing". Setting to None instead. WARNING Could not find value None in value list "qgep_vl.wastewater_structure_rv_construction_type". Setting to None instead. WARNING Could not find value None in value list "qgep_vl.wastewater_structure_structure_condition". Setting to None instead. WARNING Could not find value None in value list "qgep_vl.manhole_material". Setting to None instead. WARNING Could not find value None in value list "qgep_vl.manhole_surface_inflow". Setting to None instead. INFO done INFO Importing ABWASSER.einleitstelle, ABWASSER.metaattribute -> QGEP.discharge_point INFO done INFO Importing ABWASSER.spezialbauwerk, ABWASSER.metaattribute -> QGEP.special_structure INFO done INFO Importing ABWASSER.versickerungsanlage, ABWASSER.metaattribute -> QGEP.infiltration_installation INFO done INFO Importing ABWASSER.rohrprofil, ABWASSER.metaattribute -> QGEP.pipe_profile INFO done INFO Importing ABWASSER.haltungspunkt, ABWASSER.metaattribute -> QGEP.reach_point INFO done INFO Importing ABWASSER.abwasserknoten, ABWASSER.metaattribute -> QGEP.wastewater_node INFO done INFO Importing ABWASSER.haltung, ABWASSER.metaattribute -> QGEP.reach INFO done INFO Importing ABWASSER.trockenwetterfallrohr, ABWASSER.metaattribute -> QGEP.dryweather_downspout INFO done INFO Importing ABWASSER.einstiegshilfe, ABWASSER.metaattribute -> QGEP.access_aid INFO done INFO Importing ABWASSER.trockenwetterrinne, ABWASSER.metaattribute -> QGEP.dryweather_flume INFO done INFO Importing ABWASSER.deckel, ABWASSER.metaattribute -> QGEP.cover INFO done INFO Importing ABWASSER.bankett, ABWASSER.metaattribute -> QGEP.benching INFO done INFO Importing ABWASSER.untersuchung, ABWASSER.metaattribute -> QGEP.examination INFO done INFO Importing ABWASSER.normschachtschaden, ABWASSER.metaattribute -> QGEP.damage_manhole INFO done INFO Importing ABWASSER.kanalschaden, ABWASSER.metaattribute -> QGEP.damage_channel INFO done INFO Importing ABWASSER.datentraeger, ABWASSER.metaattribute -> QGEP.data_media INFO done INFO Importing ABWASSER.datei, ABWASSER.metaattribute -> QGEP.file INFO done Traceback (most recent call last): File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context cursor, statement, parameters, context File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute cursor.execute(statement, parameters) psycopg2.errors.ForeignKeyViolation: insert or update on table "wastewater_networkelement" violates foreign key constraint "rel_wastewater_networkelement_wastewater_structure" DETAIL: Key (fk_wastewater_structure)=(ch13p7mzMA002320) is not present in table "wastewater_structure".

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

Traceback (most recent call last): File "/usr/lib/python3.7/runpy.py", line 193, in _run_module_as_main "main", mod_spec) File "/usr/lib/python3.7/runpy.py", line 85, in _run_code exec(code, run_globals) File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/main.py", line 8, in main(sys.argv[1:]) File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/init.py", line 175, in main qgepimport() File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/qgep/import.py", line 1103, in qgep_import qgep_session.commit() File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1046, in commit self.transaction.commit() File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 504, in commit self._prepare_impl() File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl self.session.flush() File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2540, in flush self._flush(objects) File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush transaction.rollback(_capture_exception=True) File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit with_traceback=exctb, File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise raise exception File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush flush_context.execute() File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 419, in execute n.executeaggregate(self, set) File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 698, in execute_aggregate mapper, [self.state] + [r.state for r in our_recs], uow File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj insert, File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1083, in _emit_insert_statements c = cached_connections[connection].execute(statement, multiparams) File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1011, in execute return meth(self, multiparams, params) File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1130, in _execute_clauseelement distilled_params, File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context e, statement, parameters, cursor, context File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception sqlalchemy_exception, with_traceback=excinfo[2], from=e File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_ raise exception File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context cursor, statement, parameters, context File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "wastewater_networkelement" violates foreign key constraint "rel_wastewater_networkelement_wastewater_structure" DETAIL: Key (fk_wastewater_structure)=(ch13p7mzMA002320) is not present in table "wastewater_structure".

[SQL: INSERT INTO qgep_od.wastewater_networkelement (obj_id, identifier, remark, last_modification, fk_dataowner, fk_provider, fk_wastewater_structure) VALUES (%(obj_id)s, %(identifier)s, %(remark)s, %(last_modification)s, %(fk_dataowner)s, %(fk_provider)s, %(fk_wastewater_structure)s)] [parameters: {'obj_id': 'ch13p7mzWN006889', 'identifier': 'SE5', 'remark': None, 'last_modification': datetime.date(2018, 4, 4), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'ch13p7mzMA002320'}] (Background on this error at: http://sqlalche.me/e/13/gkpj) roman@tjener:~/qgisfolder$

Any ideas, what's going wrong?

Kind regards, Roman

[sjib]

Thanks @meierrom for your helpful example with just one manhole and one related wastewater_node.

I was able to reproduce your error with QGIS 3.28.4 and the QGEP Plugin 1.6.1 and Datamodel 1.6.0 qgeg_imporroman_manhole_wwn_fk_wastewater_structure_error.txt

[sjib]

In the importschema pg2ili_abwasser the two datasets have been imported. The error seems to be on the step on copying data from pg2ili_abwasser to qgep_od

to do:

• [ ] check import order of classes - manhole(wastewater_structure) should be added before wastewater_node (wastewater_networkelement) https://github.com/QGEP/qgepqwat2ili/blob/master/qgepqwat2ili/qgep/import.py
• [ ] check if this works as expected - example above has no cyclic dependencies https://github.com/QGEP/qgepqwat2ili/blob/c2d6a88f6ac772f74ee85411f8c3cc8ec080e32c/qgepqwat2ili/qgep/import_.py#L41
• [ ] Could this be a rights management problem?
• [ ] Side effect because there are not organisations in the data set?
• [ ] other?

[meierrom]

Hi @sjib,

Thanks for reproducing this issue!

I went through the check list and none of the points seem relevant here imho.

I think this issue is rather related to commits not issued at all or in a timely matter. After adding an additional qgepsession.commit() around line 325 in file import.py the issue is fixed. I don't know why these commit issues occur.

[...] materialREL=get_vl_instance(QGEP.manhole_material, row.material), surface_inflowREL=get_vl_instance(QGEP.manhole_surface_inflow, row.oberflaechenzulauf), ) qgep_session.add(manhole) print(".", end="")

    qgep_session.commit()

[...]

I hope this helps somehow.

Kind regards, Roman

[meierrom]

Hi @sjib,

The following is another very simple xtf file. This one is however based on a single reach instead.

The import results in a similar error message just like the one above. You should be able to reproduce it just like you did above.

my_export_sia405_reach_min1x.xtf.zip

I hope this can helps to narrow down these commit issues or whatever is causing this.

Kind regards, Roman

[sjib]

@meierrom Thanks for this testdata set. I had no problems importing your reach dataset with the QGEP plugin.

Plugin 1.6.1, Datamodel 1.6.0. QGIS 3.28.4, postgres 12

I will dig deeper in the code and check with your suggestion above of an additional commit()

@meierrom Which postgres version are you using?

[sjib]

If I run 91_mainnode.sql I also see the manholes:

91_maincover_mainnode.zip

[meierrom]

@meierrom Which postgres version are you using?

I'm currently using v15. However, is there a recommended version? I never actually came across an official recommendation.

Kind regards, Roman

P.S. I noticed some time ago, that issues can arise when using the wrong version of postgresql. Even currently I have issues with two missing views, being qgep_od.vw_network_segment and qgep_od.vw_network_node resulting in missing data under Topology. I do suspect an issue with v15 compatibility. However, I'm not getting any error message in QGIS, which is rather strange.

[meierrom]

Hi @sjib,

I switched from Postgresql 15 to 12 and reinstalled the qgep plugin. The import is working now.

Thanks!

[ponceta]

@meierrom here are recommended versions :

https://github.com/teksi/Home/wiki/TEKSI-modules-releases-and-roadmap

@ponceta we should add some reference from the documentation to this matrix.

[sjib]

Should be solved with https://github.com/QGEP/datamodel/pull/235