Loosing coordinates on INTERLIS Import in classes cover, wastewater_node and reach_point when attribute lage / sohlenkote is missing

[sjib]

Describe the bug
A clear and concise description of what the bug is.

INTERLIS Import with dataset that has missing attributes deckel.kote, abwasserknoten.sohlenkote and/or haltungspunkt.kote
The objects with no z attribute are not getting a value in cover.situation3d_geometry, wastewater_node.situation3d_geometry and reach_point.situation3d_geometry

To Reproduce
Exact steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior
A clear and concise description of what you expected to happen.

situation3d_geometry is always filled in and not ommited even if z coordinate is not in object of imported dataset

Screenshots / data
If applicable, add screenshots or data to help explain your problem.

Example dataset wastewater_node with geometry (lage) and cote (kote)

Example dataset wastewater_node with geometry (lage) and without cote (kote)

Examples dataset cover with geometry (lage) and without cote (kote)

Same with reach_points

Desktop (please complete the following information):

• TWW version 2024.0.2 / 2024.0.3
• QGIS Version 3.34.8
• OS Windows 10

Additional context
Add any other context about the problem here.

[sjib]

It looks like the ST_Force3D funktion is not working as expected if the z parameter is not provided:

wastewater\plugin\teksi_wastewater\interlis\interlis_model_mapping\interlis_importer_to_intermediate_schema.py (3 Treffer)

Zeile 1940:                 situation3d_geometry=self.session_tww.scalar(ST_Force3D(row.lage, row.kote)),
Zeile 1972:                 situation3d_geometry=self.session_tww.scalar(ST_Force3D(row.lage, row.sohlenkote)),
Zeile 2082:                 situation3d_geometry=self.session_tww.scalar(ST_Force3D(row.lage, row.kote)),

@ponceta can you check on this? That is a very critical error.

[sjib]

[sjib]

This creates problems with network following: #456

[sjib]

https://postgis.net/docs/ST_Force_3D.html

[ponceta]

Try SELECT ST_FORCE3D(ST_GeomFromText('POINT(2540642.81 1151868.287)',2056),NULL)

Result is NULL, therefore there is no error message since it is the "right" result postgis side

When NULL or empty, we should use 'nan'

This returns the 3SELECT ST_FORCE3D(ST_GeomFromText('POINT(2540642.81 1151868.287)',2056),'nan')

Result is POINT Z (2540642.81 1151868.287 NaN)

So with a coalesce postgis side it would be something like :

SELECT ST_ASTEXT(ST_FORCE3D(ST_GeomFromText('POINT(2540642.81 1151868.287)',2056),(COALESCE (NULL,'NaN'))::numeric)) Where NULL would be the attribute value row.kote, row.sohlenkote

(At least on postgis 3.2.3) Maybe this has been fixed on later version of postgis.
(At least on postgis 3.4.2) Maybe this has been fixed on later version of postgis.

[ponceta]

Another this is that as long as we allow NULL values for nodes geometries, this is considered as not a bug on any step of the process.

NULL geometries on nodes make the network following functions crash.

[sjib]

Another this is that as long as we allow NULL values for nodes geometries, this is considered as not a bug on any step of the process.

NULL geometries on nodes make the network following functions crash.

Then we should log this and add a warning

[urskaufmann]

I had the same problem, 800 nodes without geometry because bottom_level is NULL. Did unload release 2024.03, in release 2024.02 the import of the nodes worked also not. In the pg2ili_abwasser schema, the "lage" field has coordinates for every record, in tww_od.wastewater_note, the situation3d_geometry is empty where sohlenkote is empty.

[ponceta]

https://trac.osgeo.org/postgis/ticket/5804#comment:1 Raised to postgis community too.

[ponceta]

@urskaufmann can you check if it fixes the issue for you too with this fixed version of the plugin :

https://github.com/teksi/wastewater/actions/runs/11608981159/artifacts/2127277991

[urskaufmann]

is fixed. Wastewater nodes without bottomlevel (NULL) have now a geometry, z= nan. Nodes with bottomlevel have z=bottomlevel