Bug report #5904
'Add PostGIS layer' ignores multiple geometrytype constraint
| Status: | Closed | ||
|---|---|---|---|
| Priority: | Normal | ||
| Assignee: | - | ||
| Category: | - | ||
| Affected QGIS version: | master | Regression?: | No |
| Operating System: | Easy fix?: | No | |
| Pull Request or Patch supplied: | No | Resolution: | worksforme |
| Crashes QGIS or corrupts data: | No | Copied to github as #: | 15352 |
Description
When opening a PostGIS database with large tables, the QGis UI displays 'detecting...' for a while and scans the tables to see what geometrytypes it contains. If more than one geometrytype has been detected in the table, the UI displays separate layers for each of the detected geometrytypes.
The detection speed is improved dramatically if a database constraint is applied to the table. This works well if the constraint checks for only one geometry type, but if you define multiple geometry types in the constraint, like:
ALTER TABLE mytable
ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) in ('POINT'::text, 'LINESTRING'::text, 'POLYGON'::text) OR geom IS NULL);
Only the first geometry constraint is recognized by QGIS (POINT in the above example).
On inspection of the source code for the postgres dataprovider, it seems like the software is enumerating all geometrytypes from the constraint. Maybe the other geometrytypes are dropped or ignored somewhere else?
History
#1
Updated by Jürgen Fischer over 12 years ago
The constraint aren't inspected at all. The geometry type of the geometries in the table are. If you are using "use estimated meta data" only a few geometries are inspected, if those don't happen to be of all geometry types, that are in the table, the list will be incomplete. So I suspect that you're using "use estimated meta data".
#2
Updated by Jürgen Fischer over 12 years ago
- Status changed from Open to Feedback
#3
Updated by anne blankert over 12 years ago
I did not check checkbox 'estimated table statistics'
I turned on logging on the postgres database.
If I remove the constraint, the following is logged:
SELECT DISTINCT CASE WHEN upper(geometrytype("geom")) IN ('POINT','MULTIPOINT','POINTM','MULTIPOINTM') THEN 'POINT' WHEN upper(geometrytype("geom")) IN ('LINESTRING','MULTILINESTRING','LINESTRINGM','MULTILINESTRINGM') THEN 'LINESTRING' WHEN upper(geometrytype("geom")) IN ('POLYGON','MULTIPOLYGON','POLYGONM','MULTIPOLYGONM') THEN 'POLYGON' END, st_srid("geom") FROM "myschema"."mytable"
If I restore the constraint, the above query is not logged (verified twice) and there is no 'Detecting...' message for the table. It seems QGis thinks there is a shortcut, probably by inspecting the database meta tables?
I was looking at the source code of the postgres provider and there is a query using column consr from table pg_constraint in the pg_catalog, but according to the database log, the pg_constraint query is not used.
#4
Updated by anne blankert over 12 years ago
Aha, the problem seems to be caused by Postgis 2.0 view 'geometry_columns'.
The view somehow takes into account the geometry constraints and sets the 'type' to the first type mentioned in the constraint.
The problem is not a QGis bug. Issue can be closed.
#5
Updated by Jürgen Fischer over 12 years ago
- Resolution set to worksforme
- Status changed from Feedback to Closed