Z dimension of PostGIS geometries not recognized when added via DB Manager

[nwanner]

Describe the bug
A PostGIS layer can be added to the QGIS canvas through either the menus Layer-->Add Layers-->Add PostGIS Layers, or by right-clicking on the table/view in the DB Manager and choosing Add to Canvas. There is no issue adding via the menus. However, when layers with 3-dimensional geometry (PointZ, LineStringZ) are added using the DB Manager, they are recognized as 2-dimensional Point and LineString layers.

How to Reproduce

1. Create table with 3D geometry in PostGIS (PointZ or LineStringZ)

2. Add the layer to the canvas using the menus

3. Add the layer to the canvas using DB Manager

4. Right-click on the layer, choose Properties, and go to the Information tab.

5. Under Information from Provider at the top, the Geometry enter is "Line (LineStringZ)" for the layer added via the menus, and "Line (LineString)" (no Z) for the layer added from DB Manager.

QGIS and OS versions

QGIS version
3.8.0-Zanzibar
QGIS code revision
11aff65
Compiled against Qt
5.11.2
Running against Qt
5.11.2
Compiled against GDAL/OGR
2.4.1
Running against GDAL/OGR
2.4.1
Compiled against GEOS
3.7.2-CAPI-1.11.0
Running against GEOS
3.7.2-CAPI-1.11.0 b55d2125
PostgreSQL Client Version
9.2.4
SpatiaLite Version
4.3.0
QWT Version
6.1.3
QScintilla2 Version
2.10.8
Compiled against PROJ
5.2.0
Running against PROJ
Rel. 5.2.0, September 15th, 2018
OS Version
Windows 10 (10.0)

Additional context
As an additional demonstration, using the plugin qgis2threejs the layer added from the menus will render correctly and recognize the Z value, while the Z value option is not available for layers added form DB Manager and can only be set to match a particular value or a DEM.

[gioman]

3.8.0-Zanzibar

@nwanner way too old, you must test also on 3.10.3/3.12.0. Thanks!

[nwanner]

@gioman. Thanks for the reminder - didn't realize I'd had this computer 8 months already. I installed version 3.12 and confirmed the same behavior regarding LineString vs LineStringZ.

The behavior wasn't exactly like it was previously - I can no longer add 3D views via the DB Manager window. In the previous the 3.8 version it (presumably) took the first field in the view and used it as the oid (the view is written so that the field serves as a primary key). Adding the layer still works correctly through the layer menus.

Thanks!

Updated QGIS and OS versions
QGIS version
3.12.0-București
QGIS code revision
cd14149
Compiled against Qt
5.11.2
Running against Qt
5.11.2
Compiled against GDAL/OGR
3.0.4
Running against GDAL/OGR
3.0.4
Compiled against GEOS
3.8.0-CAPI-1.13.1
Running against GEOS
3.8.0-CAPI-1.13.1
Compiled against SQLite
3.29.0
Running against SQLite
3.29.0
PostgreSQL Client Version
11.5
SpatiaLite Version
4.3.0
QWT Version
6.1.3
QScintilla2 Version
2.10.8
Compiled against PROJ
6.3.1
Running against PROJ
Rel. 6.3.1, February 10th, 2020
OS Version
Windows 10 (10.0)
Active python plugins
Qgis2threejs;
db_manager;
MetaSearch;
processing

[RobiFag]

I confirm the same problem with table of type PointZ. Tested with QGIS 3.14.1-Pi (code revision de08d6b) and QGIS 3.10.8-A Coruña (code revision d09a4f2) on Windows 10.

Other information about both versions:
Compiled against Qt 5.11.2
Running against Qt 5.11.2
Compiled against GDAL/OGR 3.0.4
Running against GDAL/OGR 3.0.4
Compiled against GEOS 3.8.1-CAPI-1.13.3
Running against GEOS 3.8.1-CAPI-1.13.3
Compiled against SQLite 3.29.0
Running against SQLite 3.29.0
PostgreSQL Client Version 11.5
SpatiaLite Version 4.3.0
QWT Version 6.1.3
QScintilla2 Version 2.10.8
Compiled against PROJ 6.3.2
Running against PROJ Rel. 6.3.2, May 1st, 2020
OS Version Windows 10 (10.0)

[lbartoletti]

Hmmm I can not reproduce on Qgis 3.16. Can you give more informations please (postgresql, postgis versions), etc. ?

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[RobiFag]

I have already tested with Qgis 3.16.2 on Windows 10 and I still have the same problem. Adding a PointZ PostGIS table via DB Manager the layer Geometry is Point while adding the same table via browser panel or data source manager the layer geometry is PointZ. We use PostgreSQL v. 11 and PostGIS v. 3

Other information about QGIS version:
Versione di QGIS 3.16.2-Hannover
Revisione codice QGIS f1660f9
Compilato con Qt 5.11.2
Esecuzione con Qt 5.11.2
Compilato con GDAL/OGR 3.1.4
Esecuzione con GDAL/OGR 3.1.4
Compilato con GEOS 3.8.1-CAPI-1.13.3
Esecuzione con GEOS 3.8.1-CAPI-1.13.3
Compilato su SQLite 3.29.0
In esecuzione su SQLite 3.29.0
Versione client PostgreSQL 11.5
Versione SpatiaLite 4.3.0
Versione QWT 6.1.3
Versione QScintilla2 2.10.8
Compilato con PROJ 6.3.2
Esecuzione con PROJ Rel. 6.3.2, May 1st, 2020

[nwanner]

Sorry for the delayed response - you caught me in the middle of some database schema changes. I just verified that the issue is still present in QGIS 3.16.1. Adding a layer via the Layer menu results in LineStringZ while adding via Database Manager results in just LineString. QGIS, PostgreSQL and PostGIS versions follow.

QGIS version
3.16.1-Hannover
QGIS code revision
b381a90
Compiled against Qt
5.11.2
Running against Qt
5.11.2
Compiled against GDAL/OGR
3.1.4
Running against GDAL/OGR
3.1.4
Compiled against GEOS
3.8.1-CAPI-1.13.3
Running against GEOS
3.8.1-CAPI-1.13.3
Compiled against SQLite
3.29.0
Running against SQLite
3.29.0
PostgreSQL Client Version
11.5
SpatiaLite Version
4.3.0
QWT Version
6.1.3
QScintilla2 Version
2.10.8
Compiled against PROJ
6.3.2
Running against PROJ
Rel. 6.3.2, May 1st, 2020
OS Version
Windows 10 (10.0)
Active python plugins
geometry_paster;
pluginbuilder3;
Qgis2threejs;
quick_map_services;
db_manager;
MetaSearch;
processing

PostgreSQL:

Server version:
PostgreSQL 11.3, compiled by Visual C++ build 1914, 64-bit

PostGIS:

Library:
2.5.2
GEOS:
3.7.0-CAPI-1.11.0 3.7.1
Proj:
Rel. 4.9.3, 15 August 2016

[gioman]

I just verified that the issue is still present in QGIS 3.16.1

@nwanner you must test on 3.16.3 or master anyway.

[nwanner]

The issue is still present in QGIS 3.18.0

QGIS version
3.18.0-Zürich
QGIS code revision
bdef9fb
Compiled against Qt
5.11.2
Running against Qt
5.11.2
Compiled against GDAL/OGR
3.1.4
Running against GDAL/OGR
3.1.4
Compiled against GEOS
3.8.1-CAPI-1.13.3
Running against GEOS
3.8.1-CAPI-1.13.3
Compiled against SQLite
3.29.0
Running against SQLite
3.29.0
PostgreSQL Client Version
11.5
SpatiaLite Version
4.3.0
QWT Version
6.1.3
QScintilla2 Version
2.10.8
Compiled against PROJ
6.3.2
Running against PROJ
Rel. 6.3.2, May 1st, 2020
OS Version
Windows 10 (10.0)
Active python plugins
geometry_paster;
pluginbuilder3;
Qgis2threejs;
quick_map_services;
db_manager;
MetaSearch;
processing

PostgreSQL:

Server version:
PostgreSQL 11.3, compiled by Visual C++ build 1914, 64-bit

PostGIS:

Library:
2.5.2
GEOS:
3.7.0-CAPI-1.11.0 3.7.1
Proj:
Rel. 4.9.3, 15 August 2016

[lbartoletti]

@nwanner I reproduced the error. I think I have a fix

[nwanner]

@lbartoletti That is awesome! Thank you!