Feature request #18026

Geopackage views and other types as layer in Qgis + DB manager

Added by Ondrej Remes almost 3 years ago. Updated almost 3 years ago.

Status:Closed
Priority:Normal
Assignee:Richard Duivenvoorde
Category:Data Provider
Pull Request or Patch supplied:Yes Resolution:invalid
Easy fix?:No Copied to github as #:25922

Description

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.

Total_gamma_dose_rate.gpkg - Example GPKG working (2.24 MB) Richard Duivenvoorde, 2018-02-02 10:13 AM

gpkgview.png - Screendump working situation QGIS Master (264 KB) Richard Duivenvoorde, 2018-02-02 10:13 AM

orechov.gpkg (272 KB) Ondrej Remes, 2018-02-02 11:47 AM

History

#1 Updated by Richard Duivenvoorde almost 3 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 almost 3 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 almost 3 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`
AFTER that
- 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`)
VALUES ('strom_zasah','features','strom_zasah',-549173.0,-1185720.0,-548426.0,-1185130.0,5514);

INSERT INTO `gpkg_geometry_columns`(`table_name`,`column_name`,`geometry_type_name`,`srs_id`,`z`,`m`)
VALUES ('strom_zasah','geom','Point',5514,0,0);

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

#4 Updated by Ondrej Remes almost 3 years ago

Ok. Thanks. it's working. I have created this view through SQL window in DB manager in QGIS and it isnt useful for normal users. For next time DB manager could offer this option automaticaly (future adding as layer into project with geometry etc).

#5 Updated by Giovanni Manghi almost 3 years ago

  • Resolution set to invalid
  • Pull Request or Patch supplied changed from No to Yes

Also available in: Atom PDF