5

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?

Added/Edit:

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

3

2 Answers 2

11

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:

CREATE TABLE foo AS 
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 TABLE foo
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.

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

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 and acknowledge you have read our privacy policy.

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