Bug report #11317

Updated by Jürgen Fischer almost 4 years ago

QGIS is not handling views correctly demonstrated by some examples.

1) Shape file containing multiple polygons imported into postgreSQL. View created to extract one polygon.

in QGIS correctly shows with geometry MULTIPOLYGON, and icon next to the view as multipolygon

2) Table created in PostgreSQL from x.y data, using the command

UPDATE table SET the_geom = ST_SetSRID(ST_Point(x,y),27700);

DBManager in QGIS correctly shows this as POINT data

3) simple View created in PostgreSQL from the table 2) above, say of just the first 100 records

DBManager in QGIS displays a question mark symbol next to the view, and states in the info tab under PostGIS

"There isn't entry in geometry_columns"

The Geometry is shown as GEOMETRY, even though the source table the Geometry is shown as POINT.

This View can be added as a layer into QGIS via the DBManager using the command Add to Convas.

However, if the command Add PostGIS layer is used, an error message is returned (see example 4 below)

Changing the primary key to the primary key of the base table enables the layer to be added using the Add PostGIS layer command

4) complex aggregated view created in PostgreSQL from the table in 2) and another table, with the view containing the original geometry column. This is possible as the original table contained multiple records at the same point.

DBManager in QGIS shows the same information as in 3) above.

However, when attempting to add this as a layer using DBManager, nothing happens.

If the command Add PostGIS layer is used, the following error message is given

dbname='mydb' host=localhost port=5432 user='postgres' password='XXXX{2G.N' sslmode=disable key='months_visited' srid=27700 type=POINT table="myschema"."vc17_tetrad_months" (the_geom) sql= is an invalid layer - not loaded

Changing the Primary key from the arbitrary value chosen by QGIS to something that is unique enables the layer to added via the Add PostGIS layer command

There are no obvious errors in examining the view in PostgreSQL

In the table used in the Add PostGIS layer, examples 2,3 and 4 are all shown as two lines, the first greyed out and the second line showing the information in all cases as

Column = the_geom

Data Type = Geometry

Spatial Type = Point

SRID = 27700

For example 1 above, Spatial Type = Multi Polygon and SRID = 0

The Primary key column seems to pick an arbitrary column as the primary Key

PostgreSQL version 9.3.5

PostGIS version 2.1

Windows 8.1 64 bit