Current code is invalid with Postgis 3.2+

[3nids]

We currently use null values for Z coordinates in linestrings using NaN.
For instance in reach insert trigger:

wastewater/datamodel/app/view/vw_tww_reach.py

Lines 176 to 180 in fbc9c16

	NEW.progression3d_geometry = ST_ForceCurve(ST_SetPoint(ST_CurveToLine(NEW.progression3d_geometry),0,
	ST_MakePoint(ST_X(ST_StartPoint(NEW.progression3d_geometry)),ST_Y(ST_StartPoint(NEW.progression3d_geometry)),COALESCE(NEW.rp_from_level,'NaN'))));
	NEW.progression3d_geometry = ST_ForceCurve(ST_SetPoint(ST_CurveToLine(NEW.progression3d_geometry),ST_NumPoints(NEW.progression3d_geometry)-1,
	ST_MakePoint(ST_X(ST_EndPoint(NEW.progression3d_geometry)),ST_Y(ST_EndPoint(NEW.progression3d_geometry)),COALESCE(NEW.rp_to_level,'NaN'))));

Starting with Postgis 3.2, this fails with Geometry contains invalid coordinates error message.

I don't know how to handle this yet and have posted an issue on stackexchange and osgeo trac.

SE: https://gis.stackexchange.com/questions/470332/how-to-handle-null-z-m-values-in-postgis
osgeo ticket: https://trac.osgeo.org/postgis/ticket/5610#ticket

[ponceta]

Handling NaN values has only brought me issues since its introduction in 2019...

On the general idea I think it's a great idea but in the facts I face issues with :
postgis, fme, autocad, qgis, mapserver, exports, ...

I think about removing from qgep since many years yet for Pully but sticking to the idea not to differ too much from the original trunk prevent me from doing it.

[3nids]

What would be your approach?
Use 0s in the geometries and have null values as attributes where needed?

[3nids]

It is worth mentioning that there is no problem for development but if anyone puts TWW in production with PostGIS 3.2+, he will get errors if there is any null altitudes entered.

[ponceta]

What would be your approach? Use 0s in the geometries and have null values as attributes where needed?

It would be simpler if postgis would handle NULL values, but actually in Switzerland, an altitude of 0 is the equivalent of a NULL values in this domain. We created NaN values for people at the sea level and implemented the default Z values for QGIS. Can we have negative values? -999 would be a good candidate for such implementations.

It is worth mentioning that there is no problem for development but if anyone puts TWW in production with PostGIS 3.2+, he will get errors if there is any null altitudes entered.

I've a QGEP working project on postgre 14 postgis 3.2.3 with NULL values in the altimetric fields, but it would be great if we fix that.

[3nids]

Apparently this is fixed in upcoming versions (3.4.2, 3.5.0)

[3nids]

3.4.2: https://trac.osgeo.org/postgis/milestone/PostGIS%203.4.2 (july 24)
3.5.0: https://trac.osgeo.org/postgis/milestone/PostGIS%203.5.0 (sept. 24)

[ponceta]

@ponceta discuss this with @3nids

[3nids]

it seems the bug has been fixed in 3.2.7
https://postgis.net/docs/manual-3.2/release_notes.html#idm43481

and 3.4.2 too

[ponceta]

3.3.6 should be fixed too https://postgis.net/docs/manual-3.3/release_notes.html#idm44579 but docker image has not yet been updated :

It should be proposed on how is interaction and actualisation of available postgis images refreshed could be.