Postgis mixed geometry data table causes QGIS > 3.8 issues as more recent QGIS versions see all records instead of those of the right type

[swa66]

Describe the bug

In QGIS more recent than 3.8 a postgis database that has (multi)points, lines and (multi)polygons into one table causes issues as QGIS sees all rows in the database in each of the layers.
In QGIS 3.8 (and earlier), this problem was not present. QGIS 3.10 and 3.12 both have the issue.

FWIW: the database is not used by QGIS alone, so splitting the table into 3 tables it is simply not an option (need for unique identifiers and other tables not even used in QGIS that refer to this data)

This causes problems when viewing the table data in QGIS, when rendering labels ( a layer will generate labels for data of the wrong geometry type), and when exporting shape files for that layer.

How to Reproduce

Have a postgis database table containing in a single table multipoint, point, multipolygon,
multilinestring, and linestring data.

e.g.:

=> \d eo
                                          Table "our.eo"
     Column      |          Type           | Collation | Nullable |            Default             
-----------------+-------------------------+-----------+----------+--------------------------------
 id              | integer                 |           | not null | nextval('eo_id_seq'::regclass)
 mapname         | character varying(50)   |           |          | 
 geom            | geometry(Geometry,4326) |           |          | 
[....]

=> select count(*), geometrytype(geom) from eo group by geometrytype(geom);
 count |  geometrytype   
-------+-----------------
     3 | 
  8674 | MULTIPOINT
   140 | MULTIPOLYGON
   229 | POINT
     3 | MULTILINESTRING
    25 | LINESTRING
(6 rows)

Create a new project in QGIS 3.10 , connect to the postgis database to add a vector layer and see the database there 3 times (as expected), once for (multi)points, once for lines and once for (multi)polygons:

Add either the 3 layers to the project (one by one or all in one go doesn't matter)

Click open Attributes table for each of the layers and see the counts:

E.g. for the points one:

compare that 9075 to the rows in the database above, and confirm data for known polygon data is included as well.

e.g. for the polygon layer:

Doing the same in QGIS 3.12 yields exactly the same results as in 3.10

Doing the same in QGIS 3.8 works without a problem:
polygons:

lines:

points:

Once these rows get seen in multiple layers, at least the following additional problems get triggered:

• Labels get shown for elements that have the wrong Geometry type. (to
  Green labels on the points, red labels on the polygons, placement set so they don't overlap:
  zoomed in to a single point, no overlapping polygon anywhere near:
  

• exporting to shapefiles fails with errors
  e.g. exporting to an ESRI shapefile of the points layer gives
  

Instructions don't work on a mac.

Tested versions:
QGIS 3.8.2 - macOS Catalina 10.15.3 - works as expected
QGIS 3.10.2 - macOS Catalina 10.15.3 - bug
QGIS 3.12.0 - macOS Catalina 10.15.3 - bug

Additional context

[elpaso]

Can you please attach an SQL dump?

[swa66]

Can't make that database public, sorry. But I'll try to create a sample I can share.

[swa66]

OK, here's a small test case that triggers the bug on the point layer (not on the others, but as it's small I suppose that will help in debugging already.

=> create table our.test (id integer not null primary key, mapname character varying(50), geom geometry(Geometry,4326) );
CREATE TABLE
=> \d test
                          Table "our.test"
 Column  |          Type           | Collation | Nullable | Default 
---------+-------------------------+-----------+----------+---------
 id      | integer                 |           | not null | 
 mapname | character varying(50)   |           |          | 
 geom    | geometry(Geometry,4326) |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

=> insert into test  (id,mapname,geom) values (1,'line', ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)',4326) );
INSERT 0 1
=> insert into test  (id,mapname,geom) values (2,'multiline', ST_GeomFromText('MULTILINESTRING((-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932))',4326) );
INSERT 0 1
=> insert into test  (id,mapname,geom) values (3,'point', ST_GeomFromText('POINT(-71.064544 42.28787)',4326) );
INSERT 0 1
=> insert into test  (id,mapname,geom) values (4,'polygon', ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))',4326) );
INSERT 0 1
=> insert into test  (id,mapname,geom) values (5,'multipolygon', ST_GeomFromText('MULTIPOLYGON(((-71.1031880899493 42.3152774590236,-71.1031627617667 42.3152960829043,-71.102923838298 42.3149156848307,-71.1023097974109 42.3151969047397,-71.1019285062273 42.3147384934248,-71.102505233663 42.3144722937587,-71.10277487471 42.3141658254797,-71.103113945163 42.3142739188902,-71.10324876416 42.31402489987,-71.1033002961013 42.3140393340215,-71.1033488797549 42.3139495090772,-71.103396240451 42.3138632439557,-71.1041521907712 42.3141153348029,-71.1041411411543 42.3141545014533,-71.1041287795912 42.3142114839058,-71.1041188134329 42.3142693656241,-71.1041112482575 42.3143272556118,-71.1041072845732 42.3143851580048,-71.1041057218871 42.3144430686681,-71.1041065602059 42.3145009876017,-71.1041097995362 42.3145589148055,-71.1041166403905 42.3146168544148,-71.1041258822717 42.3146748022936,-71.1041375307579 42.3147318674446,-71.1041492906949 42.3147711126569,-71.1041598612795 42.314808571739,-71.1042515013869 42.3151287620809,-71.1041173835118 42.3150739481917,-71.1040809891419 42.3151344119048,-71.1040438678912 42.3151191367447,-71.1040194562988 42.3151832057859,-71.1038734225584 42.3151140942995,-71.1038446938243 42.3151006300338,-71.1038315271889 42.315094347535,-71.1037393329282 42.315054824985,-71.1035447555574 42.3152608696313,-71.1033436658644 42.3151648370544,-71.1032580383161 42.3152269126061,-71.103223066939 42.3152517403219,-71.1031880899493 42.3152774590236)),((-71.1043632495873 42.315113108546,-71.1043583974082 42.3151211109857,-71.1043443253471 42.3150676015829,-71.1043850704575 42.3150793250568,-71.1043632495873 42.315113108546)))',4326) );
INSERT 0 1
=> select count(*), geometrytype(geom) from test group by geometrytype(geom);                                                                                                                                                                      
 count |  geometrytype   
-------+-----------------
     1 | MULTIPOLYGON
     1 | MULTILINESTRING
     1 | POINT
     1 | POLYGON
     1 | LINESTRING
(5 rows)

When looking at the attribute table of the point layer after adding all 3 of them:

(in this case the line and area are ok, but that's obviously not always the case, just don't trigger it with this data)

The geometry WKT data comes from a postgis manual: https://postgis.net/docs/ST_GeomFromText.html

[gioman]

@swa66 my observation is that it only happens when this layers are added from DB Manager not from the browser or the "add postgis layer" dialog. Can you confirm? @m-kuhn

[m-kuhn]

Interesting finding, I wonder what the postgres provider does here ...

Out of curiosity, what is expected to happen with NULL geometries?

[swa66]

@swa66 my observation is that it only happens when this layers are added from DB Manager not from the browser or the "add postgis layer" dialog. Can you confirm? @m-kuhn

What I used to recreate the problem was adding from
Layer > Add Layer > Add PostGIS Layers ...
Then connect to the PostGIS database server and select the table in the GUI there. It shows the table 3 times with different "Spatial Type"

In the example above I didn't use the DB manager at all.
When looking at the table in DB Manager (I did just now):
I see the table 4 times: 3 times with the point/line/polygon and once with a generic "GEOMETRY" as geometry.
It also shows all rows in the table in each of them.

Also: in saved projects that work perfectly fine in 3.8 show the bug in 3.10 and 3.12 so it's not just while adding the layers.

I tried it again from a new project:
Database > DB Manager
then drilling down to the table, double clicking on each of the 3 versions of it (point/line/polygon) and then all 3 show up with all 5 rows.

So I can confirm that it seems adding layers through DB Manager makes it happen more often, but it DOES happen without using DB Manager as well.

That difference in behaviour depending on how the layer was added triggered me to write out the project files as XML so that I could compare them.
What struck me was that the type attribute on <qgis><layer-tree-group><layer-tree-layer> for the layers when they are added via the add layer interface or via DB manager are different!

When adding via the Add Layer: they are respectively:

type=Polygon
type=LineString
type=Point

While when adding via DB manager they end up being:

type=MultiCurve
type=MultiSurface
type=Point

And the same repeats for the type attribute on <qgis><projectlayers><maplayer><datasource>

[swa66]

Out of curiosity, what is expected to happen with NULL geometries?

I looked into those: from what I can tell at least one was a real entry, made manually in QGIS. that somehow managed to not end up with geometry data. No clue how, when or why.
The other two rows had no clues as to when or how they got entered and had no other data linking them to ever have been "used" or being interesting.
I'm not sure how they ended up in there, there's no process we use that inserts lines in this table aside of QGIS. [They get referenced externally from QGIS and can have some of their field updated as well, but there's nothing adding rows or blanking out all fields or so]
In all honesty: not sure how it ended up in there, but it's gone now (and no it didn't solve this issue - if only it were that simple)

[m-kuhn]

@swa66 sorry, I did not mean to question or criticize your data.

Let me explain:

I don't normally work with mixed geometry types often. And for the record, I am not aware what the source of this issue here is.

In our projects, we do have features with NULL geometries and we are expected to deal with them. On such features we can use the add part tool to set the geometry. These features are listed in the attribute table for a long time (since before QGIS 2.0). This is on plain Polygon tables (no mixed geometry involved so far).

If we solve this issue by filtering features by geometry types (only show points if the layer type is point etc.) we will also lose NULL geometries, which we cannot do - because it breaks a long-supported workflow.

We could show NULL geometry features on all geometry type layers. This would work for me, but I am not sure if you want that. And it seems not that consistent within the filtering approach.

After all - I wonder if this filter should be an option on the layer. "Show all records" or "Show only matching geometry type records".

Actually one can already do this now by setting a ST_GeomtryType(geometry) = 'ST_LineString' on the layer, it's just not yet a super user friendly button to enable this.

[swa66]

@swa66 sorry, I did not mean to question or criticize your data.

no worries, I had already criticised it myself. When I saw it as I found it rather odd. As removing them didn't solve the issue, I didn't mention it earlier here, that's all.

If we solve this issue by filtering features by geometry types (only show points if the layer type is point etc.) we will also lose NULL geometries, which we cannot do - because it breaks a long-supported workflow.

We could show NULL geometry features on all geometry type layers. This would work for me, but I am not sure if you want that. And it seems not that consistent within the filtering approach.

I fully understand the issue of how to deal with things that don't have a type if you have to split it up by type, no worries.
For Postgis: geometries can have a type and be empty, no need to use NULL for that.
e.g. here are some examples: https://postgis.net/docs/ST_IsEmpty.html

Your explanation might also show how those NULL entries got created: deletion of the last point in a multipoint object e.g.

After all - I wonder if this filter should be an option on the layer. "Show all records" or "Show only matching geometry type records".

I'm seeing inconsistencies in the names used for the geometry types not just between postgis and QGIS (that's understandable, no reason to have an exact match there), but also between how layers got added to a project within QGIS. -> might be a starting point to find the cause for somebody with a far better understanding of the internals than I have.

Actually one can already do this now by setting a ST_GeomtryType(geometry) = 'ST_LineString' on the layer, it's just not yet a super user friendly button to enable this.

Interesting! I had looked at filtering, but from my limited experience with it, I didn't see the geom column as an option that could be used. Will look again at it as it might be a (start of a) workaround, as these phantom labels start to be really annoying, and having to fall back to 3.8 to be able to export an offline ESRI shapefile is also far from a handy way of working.

[m-kuhn]

Interesting! I had looked at filtering, but from my limited experience with it, I didn't see the geom column as an option that could be used.

The feature filter in the layer properties is sent directly to the database as WHERE clause when data is requested. This means that you can use anything that postgres/postgis have available.

[elpaso]

@gioman I didn't test this case yet, but maybe DB manager creates a "query layer" while adding the layer from the data source manager does not.

You should be able to print layer.publicSource() and compare them.

[gioman]

What I used to recreate the problem was adding from
Layer > Add Layer > Add PostGIS Layers ...
Then connect to the PostGIS database server and select the table in the GUI there. It shows the table 3 times with different "Spatial Type"

@swa66 well... that is not what I observe here on a clean testing machine with the dataset ypu provided.

the layer with 6 features was added via DB manager and shows the issues you report, the other two where added from the browser and the "add postgis layer" dialog.

So it is either that the dataset you provided is not enough to fully replicate the issue or that is just a DB manager problem, IMHO.

[swa66]

@swa66 well... that is not what I observe here on a clean testing machine with the dataset ypu provided.

That's not the sample I provided. The table I provided only has 5 rows, not 6. And there is point, 2 lines and 2 polygons in it, yours only shows polygons.

[gioman]

That's not the sample I provided. The table I provided only has 5 rows, not 6. And there is point, 2 lines and 2 polygons in it, yours only shows polygons.

@swa66 it is, it was me that added another manually (from QGIS) another polygon.

[gioman]

You should be able to print layer.publicSource() and compare them.

@elpaso

this is loaded from browser:

layer.publicSource()
'dbname=\'postgis_30_sample\' host=localhost port=5432 user=\'postgres\' sslmode=disable key=\'id\' srid=4326 type=MultiSurface checkPrimaryKeyUnicity=\'1\' table="public"."test" (geom) sql='

this is loaded from DB Manager:

layer.publicSource()
'dbname=\'postgis_30_sample\' host=localhost port=5432 user=\'postgres\' key=\'id\' type=Polygon checkPrimaryKeyUnicity=\'1\' table="public"."test" (geom) sql='

[swa66]

That's not the sample I provided. The table I provided only has 5 rows, not 6. And there is point, 2 lines and 2 polygons in it, yours only shows polygons.

@swa66 it is, it was me that added another manually (from QGIS) another polygon.

I see what you did: no the sample does not trigger on the polygon variant of the data, add the point variant (there should be only 1 there, and it shows all 5 rows)

On more complex examples it triggers in more of the variants as well, that somehow is lost in the shorter example, no idea why. But I did note it with the sample when I provided it "(in this case the line and area are ok, but that's obviously not always the case, just don't trigger it with this data)"

[gioman]

I see what you did: no the sample does not trigger on the polygon variant of the data, add the point variant (there should be only 1 there, and it shows all 5 rows)

@swa66 confirmed. So my observation is now:

with the polygon and line layers the issue arises only if loaded from DB Manager

with the point layer the issue arises regardless the way the layer is loaded

[gioman]

In QGIS 3.8 (and earlier), this problem was not present.

@swa66 if this statement means that on 3.8 it worked as expected then I cannot confirm it. In fact I just installed 3.8 and in the QGIS browser and the "add postgis layer" dialog the only layer that shows in the line one, and in DB manager the layer only shows once with the "?" icon (because this was the case when the geometry column was not declared as point,line or polygon). Adding this layer always results in a table with 5 records where it should be 2.

[gioman]

In QGIS 3.8 (and earlier), this problem was not present.

@swa66 if this statement means that on 3.8 it worked as expected then I cannot confirm it. In fact I just installed 3.8 and in the QGIS browser and the "add postgis layer" dialog the only layer that shows in the line one, and in DB manager the layer only shows once with the "?" icon (because this was the case when the geometry column was not declared as point,line or polygon). Adding this layer always results in a table with 5 records where it should be 2.

[swa66]

QGIS 3.8 works as expected with real data, I don't have a machine with 3.8 handy to redo the short sample with.
In fact a saved project that shows the error in 3.10 doesn't have a problem when opened in 3.8.

And again: I do not use DB Manager.

[gioman]

And again: I do not use DB Manager.

@swa66 ok but we have to figure all the possible issues, not just yours. Thanks for understanding.

QGIS 3.8 works as expected with real data, I don't have a machine with 3.8 handy to redo the short sample with.

then please paste a (even small) sample of your data.

[m-kuhn]

For reference, this was introduced by #32292

[m-kuhn]

For the record, this should be solved (and tested) with #35367 .

The issue explained in #34629 (comment) remains: It's not possible to "fix" NULL geometries on "geometry" layers through QGIS. This is an inconsistency with correctly typed layers/db columns, but IMO an acceptable one for now.