7

I often use PostGIS tables with a geometry column of type "Geometry" containing varying geometry types e.g. polygons and points. When opening the table in QGIS the Connections dialogue lists the table once for each different geometry type as expected. Today I converted a PostGIS table from MultiLineString to Geometry and added some point and polygon objects to the table. But QGIS does not recognize the change and has only one entry for the table in the Connections dialog with a Spatial Type of LineString. However, in PostGIS, if I query:

select distinct st_geometrytype(wkb_geometry)
from tracts_test;

I get:

"ST_LineString"
"ST_Polygon"
"ST_Point"

as expected.

I've tried adding and removing the spatial index, adding and removing the primary key, and creating a new table with create NEW_TABLE as select * from OLD_TABLE; but in no case does QGIS see any geometry type other than LineString. What else should I be doing, or where else should I be looking, coerce QGIS into seeing the other geometry types in the table?

5
  • QGIS can only handle one geometry type per layer, so maybe you need to use a Provider Feature Filter (load the layer, Props, Source...) to select one geometry type, and repeat three times for each geometry type... I don't have a PG server handy to test at the moment.
    – Spacedman
    May 11 at 17:58
  • "I converted a PostGIS table from MultiLineString to Geometry". How? You may try ALTER TABLE your_table ALTER COLUMN your_geom_name type geometry(Geometry, your_srid); Also what does SELECT f_table_name, f_geometry_column, srid, type FROM geometry_columns WHERE f_table_name = 'your_table_name'; return?
    – ThomasG77
    May 11 at 18:28
  • What is your QGIS version?
    – user30184
    May 11 at 18:29
  • @ThomasG77 I did ALTER TABLE tracts ALTER COLUMN wkb_geometry type geometry(Geometry, 2242); and SELECT f_table_name, f_geometry_column, srid, type FROM geometry_columns WHERE f_table_name = 'tracts';' returns: "tracts" "wkb_geometry" 2242 "GEOMETRY"` May 11 at 18:43
  • @ user30184 I'm running QGIS 3.18.2 on Ubuntu 20.04 May 11 at 18:46
10

I'll answer my own question but credit goes to user30184 for giving me a couple clues. The problem seems to be that the first few thousand rows in my table are LineStrings and apparently QGIS isn't examining all of the rows and finding the other geometry types in the table.

The solution, at least in QGIS 3.18.2, is to check the "Don't resolve type of unrestricted columns (GEOMETRY)" in the PostGIS Connections options. enter image description here

After reconnecting to the Postgres database the Connections list will now have a "Select" in the "Spatial Type" column for tables with a spatial type of Geometry as shown below. enter image description here

You can only pick one geometry type at a time and add it to your map. To add another geometry type from the same table you must reconnect to the database to get the Select list again.

I opened a issue on github.

5

Perhaps the only thing that you need to do is to refresh the connection by pressing the Connect button again.

This was my test case:

CREATE TABLE geometrytest (id integer, geom geometry(MultiLineString));
INSERT INTO geometrytest values (1,ST_GeomFromText('MultiLineString ((0 0,3 3))'));
ALTER TABLE geometrytest ALTER COLUMN geom type geometry(Geometry);
INSERT INTO geometrytest values (2,ST_GeomFromText('POINT (1 2)'));
INSERT INTO geometrytest values (3,ST_GeomFromText('LINESTRING (1 2, 0 2)'));
INSERT INTO geometrytest values (4,ST_GeomFromText('POLYGON ((1 2, 1 3, 2 3, 1 2))'));

I read the data first with QGIS after line 2 when the table was still MultiLineString. Then I changed the geometry type and added three more geometries into the table. When I added this layer again into QGIS, without adding any data from other connections in between, QGIS still considered that the table was MultiLineString. Obviously QGIS is caching the details of the tables for speeding up things. Pressing the Connect button forces QGIS to read and interpret the metadata tables again and the different geometry types appear on the list.

enter image description here

2
  • Thanks, but I've tried many variations of that including on a different computer, creating a new connection to the database, and opening the connection in a new, empty project. I pasted your 6 lines into my database and got the expected three entries in the Connections list. This seems to narrow it down to my data, but I can't figure out what. ogrinfo -so shows Geometry: Unknown (any) as expected and shows objects of all three geometry types that I expect to see. May 11 at 19:35
  • 1
    I fear I can't help more. I believe that you have "Don't resolve type of unrestricted columns" unchecked in your connection. I do not know if QGIS is analyzing the whole table or if it takes just a sample from the beginning of the table but that would not feel like a good idea. But you can have a try with create new_table ... as select *, st_geometrytype(wkb_geometry) as type ... order by type desc.
    – user30184
    May 11 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.