Feature request #18026
Geopackage views and other types as layer in Qgis + DB manager
|Pull Request or Patch supplied:||Yes||Resolution:||invalid|
|Easy fix?:||No||Copied to github as #:||25922|
QGIS could add layers from geopackage database, but if i add into database new view (by SQL command, with geometry attribute geom etc.), it is added, but it is not shown in DB manager window. If i want add this view as new layer into Qgis, it is not possible, QGIS does not see it.
#1 Updated by Richard Duivenvoorde over 2 years ago
Hi, can you please add some more info?
- which version are you talking about that it is NOT working?
- can you provide some example data/queries that you used?
- is it possible that you did not 'registre' the view as spatial in the gpkg_geometry_columns
Because with me (in master AND in 2.18.16) the view we created in the attached gpkg is seen both in DB manager and, QGIS and sqlitebrowser (see screendump).
Please provide more info/data
#2 Updated by Ondrej Remes over 2 years ago
- Assignee set to Richard Duivenvoorde
- File orechov.gpkg added
Hello, Richard. Thanks for reply. It looks like i did a mistake. Im using dev version of QGIS (daily). I have created database orechov.gpkg with 2 tables (table strom, ker with point and mpolygon geometry) and simple view as selection from table strom where attribute zasah is not null. The problem is that when i try add the view as new layer in data provider, i don't see this view in table list. The same problem is in QGIS DB Manager, in SQLITE DB browser is all shown. I attach a database file. Thanks for quick help.
#3 Updated by Richard Duivenvoorde over 2 years ago
- Status changed from Open to Closed
Hi, ok. I managed to get it working.
You are right, creating (only) a simple view is not enough. You need to add more information to the gpkg to make this work:
- add the view name to the table `gpkg_contents`
- add the geometry column to the table `gpkg_geometry_columns`
So in your case:
INSERT INTO `gpkg_contents`(`table_name`,`data_type`,`identifier`,`min_x`,`min_y`,`max_x`,`max_y`,`srs_id`)
INSERT INTO `gpkg_geometry_columns`(`table_name`,`column_name`,`geometry_type_name`,`srs_id`,`z`,`m`)
As soon as that is done, all is fine: QGIS will show the view as a geometry view and not a plain table
Actually all works just like in most other spatial databases (Postgis, Oracle)
Closing this one, as I do not think it is possible to predict geometry column and type just from the view query, but correct me if I'm wrong