Error upon import II

[meierrom]

Hi folks,

I'm getting the following error message upon issuing an import command:

python -m qgepqwat2ili qgep import -data/heit/heitsia405export_20230504.xtf --log

[...] INFO Importing ABWASSER.datei, ABWASSER.metaattribute -> QGEP.file INFO done [...] DETAIL: Failing row contains (00000000WE002583, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null). [...] sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "progression_geometry" of relation "reach" violates not-null constraint DETAIL: Failing row contains (00000000WE002583, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null). [...] [SQL: INSERT INTO qgep_od.reach (obj_id, clear_height, coefficient_of_friction, elevation_determination, horizontal_positioning, inside_coating, length_effective, material, progression_geometry, reliner_material, reliner_nominal_size, relining_construction, relining_kind, ring_stiffness, slope_building_plan, wall_roughness, fk_reach_point_from, fk_reach_point_to, fk_pipe_profile, swmm_default_coefficient_of_friction, dss2020_hydraulic_load_current) VALUES (%(obj_id)s, %(clear_height)s, %(coefficient_of_friction)s, %(elevation_determination)s, %(horizontal_positioning)s, %(inside_coating)s, %(length_effective)s, %(material)s, ST_Force3D(NULL), %(reliner_material)s, %(reliner_nominal_size)s, %(relining_construction)s, %(relining_kind)s, %(ring_stiffness)s, %(slope_building_plan)s, %(wall_roughness)s, %(fk_reach_point_from)s, %(fk_reach_point_to)s, %(fk_pipe_profile)s, %(swmm_default_coefficient_of_friction)s, %(dss2020_hydraulic_load_current)s)] [parameters: {'obj_id': '00000000WE002583', 'clear_height': None, 'coefficient_of_friction': None, 'elevation_determination': None, 'horizontal_positioning': None, 'inside_coating': None, 'length_effective': None, 'material': None, 'reliner_material': None, 'reliner_nominal_size': None, 'relining_construction': None, 'relining_kind': None, 'ring_stiffness': None, 'slope_building_plan': None, 'wall_roughness': None, 'fk_reach_point_from': None, 'fk_reach_point_to': None, 'fk_pipe_profile': None, 'swmm_default_coefficient_of_friction': None, 'dss2020_hydraulic_load_current': None}] (Background on this error at: http://sqlalche.me/e/13/gkpj) [...]

Any ideas what's going wrong?

Kind regards, Roman

[sjib]

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "progression_geometry" of relation "reach" violates not-null constraint

You have no geometry for this reach

[sjib]

The TEKSI wastewater datamodel wants a geometry for each reach and is therefore stronger than the INTERLIS model checker

[meierrom]

Thanks @sjib,

I was assuming something like that. Therefore I checked my data and none of them do have a missing geometry.

It's also not helping that qgepqwat2ili is apparently creating a new id which is 00000000WE002583 and not referring to the specific record in my data for further investigation.

Kind regards, Roman

[sjib]

What are the results of the ilivalidation in the first step of the import? Do you have a valid xtf? It is strange that new id's should be generated on an import.

Is there a difference if you do the command line import or via the QGEP plugin?

[sjib]

With the default setting of qgep_sys.oid_prefixes and the value '00000000' as prefix you will not get valid INTERLIS OID's on the INTERLIS export. So please set your own prefix - see https://qgep.github.io/docs/installation-guide/workstation.html#if-you-are-a-data-owner-add-your-own-oid-to-the-project OID prefixes have to be ordered at https://www.interlis.ch/en/dienste/oid-bestellen

[meierrom]

What are the results of the ilivalidation in the first step of the import? Do you have a valid xtf? It is strange that new id's should be generated on an import.

Yep, the xtf seems fine and yep, the new id's are strange.

I can't say yet, why new id's are created in this import. I'll report back on this issue when I know more.

Is there a difference if you do the command line import or via the QGEP plugin?

I can't say. I haven't tried that yet.

[meierrom]

With the default setting of qgep_sys.oid_prefixes and the value '00000000' as prefix you will not get valid INTERLIS OID's on the INTERLIS export. So please set your own prefix - see https://qgep.github.io/docs/installation-guide/workstation.html#if-you-are-a-data-owner-add-your-own-oid-to-the-project OID prefixes have to be ordered at https://www.interlis.ch/en/dienste/oid-bestellen

Ah, ok. Thanks!

[meierrom]

Thanks @sjib,

I was able to fix this.

Although all reaches had a geometry in my case, one single entry had a faulty geometry entry resulting in the error message.

The quick fix for me was to simply delete the entry including the two linked "haltungspunkte".

Kind regards, Roman

[sjib]

@meierrom Can you share the faulty geometry part to check why this was not found by ilivalidator?

[meierrom]

Yep, fortunately I made a screenshot yesterday, when researching the issue. It's the last record in the picture:

Kind regards, Roman