When adding PostGIS layer in QGIS (version does not matter) i observe duplicate entries in the list as can be seen below (e.g. ax_gebaeude, ax_flurstueck, ax_gehoelz...), one with a valid geometry, the other(s) with a warning and a 'select geometry type' hint.

enter image description here

Adding the ones with valid geometry to the project causes no issue, but i am not sure what this means.

Can anyone clarify this behaviour?


geometry_columns view:

geoemtry_columns view

and everything's fine in browser panel:

enter image description here

and another behaviour in the DB Manager plugin. For geometry_types 'GEOMETRY' are marked with a '?' and it says 'no entry in geometry_columns' (wich is definitively not the case) but preview is no problem:

enter image description here

enter image description here

  • 1
    how did these tables get into PostGIS in the first place? And, what does the QGIS 'browser panel' view of your PostGIS database look like? – DPSSpatial Mar 16 '16 at 19:54
  • various ways, e.g. with the norGIS ALKIS import tools wich comes with OSGEO4W installer (sample data: ldbv.bayern.de/file/zip/2407/Testdaten_Lurchingen_ALKIS.zip) or as described here: gis.stackexchange.com/questions/183222/…. – Jochen Schwarze Mar 17 '16 at 8:18
  • The browser panel shows no issue, wich bothers me a bit. And see edit above for a screenshot of geometry_columns view. As stated below, the issue happens exactly in the cases the geometry_type ist defined as generic 'GEOMETRY'. – Jochen Schwarze Mar 17 '16 at 8:24

QGIS is happiest with PostGIS layers when they have complete information in the geometry_columns view. To achieve this state, you have to make sure the geometry columns are created with useful metadata. This kind of issue usually comes up with tables created using CREATE TABLE AS SELECT ... style queries.

Try something like this:

SELECT ST_Union(geom)::Geometry(Point, 4326), gid 
FROM bar;

Note that in defining the geometry output of the query, we are very specifically telling the system what kind of geometry to expect. This will get stored in the system catalogues and then reflected in the geometry_columns table, which will then make QGIS happy.

You can also update columns after the fact with an ALTER TABLE query:

ALTER COLUMN geom type Geometry(Point, 4326) USING geom::Geometry(Point, 4326)

Note we also included a primary key, gid. This is something you might have to add by hand afterwards, using an ALTER TABLE to define it as a primary key.

  • I wonder why the browser window doesn't care about this, but the 'add postgis layer' dialogue is the one showing the dupes... – DPSSpatial Mar 16 '16 at 22:17
  • cp. comment above. – Jochen Schwarze Mar 17 '16 at 8:41

The list shows the available geometry types. ax_flurstueck doesn't have a geometry type constraint. The additional line in the list enables you to add the layer as point or line layer so you can also add points or lines.

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.