Working with QGIS 3.4.9 and Geopackage. I created a View table. The command runs without errors, the view is created but does not appear in QGIS. What do I need to do?
2 Answers
The current version of GeoPackage (version 1.2.1) lacks a mechanism to register views into the metadata tables. The GitHub issue about that seems to be solved and closed https://github.com/opengeospatial/geopackage/issues/446 but changes will be included only in the version 1.3 of the standard.
For making your view to appear in QGIS you must materialize it with (create table my_materialized_view as select … from my_view). Some additional SQL may be needed for registering the new materialized view properly.
-
1Materialized view is not for my purposes, I need dynamic views. I tried to inform Geopackage by giving an insert in the gpkg_contents and gpkg_geometry_columns tables, but it still did not work properly– DenilsonCommented Jul 9, 2019 at 23:15
-
It seems to be possible to add data from the gpkg view into QGIS with "execute SQL" tool but it converts data for example into a temporary shapefile and is not dynamic. OpenJUMP does not care about metadata and it is perhaps more nice to use with SQL queries, is that software an option for you? Commented Jul 11, 2019 at 8:20
-
In this moment no , i'm very happy with QGIS and I will trying find some ideias to put spatial views inside of Geopackage.– DenilsonCommented Jul 11, 2019 at 16:30
-
How about using SpatiaLite instead of GeoPackage? It does already have a mechanism for registering the metadata of spatial views, with some limitations caused by the SQLite gis.stackexchange.com/questions/232479/…. Commented Jul 11, 2019 at 19:05
-
1Using the db manager 'DB Browser (SQLite)' I was able to create spatial views in geopackage and register them for QGIS to be able to view. It's working really well.– DenilsonCommented Feb 17, 2021 at 13:35
I wanted to do the same thing and haven't found any other post on the topic, so here's what i've found:
Spatiallite makes it possible, has (as fas as i understand) quite a few limitations compared to gpkgs though (i think it is still impossible to delete columns).
So back to the geopacke:
- Views can be created in the DB manager (CREATE VIEW example1 ...)
- Interestingly, they don't show up here, but if you go to the browser in the main qgis window, there it is and can be loaded to the canvas and they also behave like views.
- To delete or work with it, you still need to go over the DB manager, as for example it cannot be deleted like a regular table in a gpkg in the browser window (DROP VIEW example1...)
- The easiest way though, is to create a virtual layer, which does pretty much the same (at least for my case).
Unfortunately, when creating views or virtual layers, virtual fields in the gpkg-table can't be used. Perhaps someone else can give me hint on that, but i'll post it seperatly as well.
-
Follow to make QGIS see the views byDB Browser SQLite --1 - GPKG with spatial layers, ST and EB tables --2 - With DB Browser, Create a view. Example : CREATE VIEW EB_VW AS select E.* FROM EB as E, (select distinct uf, munic from ST) As S where E.uf = S.UF --3 - Edit the table gpkg_content with the view name and their parameters. EB_VW , Features , EB_VW , 2020-03-25T16:17:53.869Z , -73.99 , -33.75, -28.83 , 5.27 , 4674 --4 - Edit the tables gpkg_geometry_columns. EB_VW , geom , MULTIPOLYGON , 4674 , 0 , 0– DenilsonCommented Mar 3, 2021 at 20:36