1

I need to create 4 views of a spatial table in a geopackage or SpatiaLite (no preference at the moment). I tried with geopackage, but the view is displayed as a datatable and not displayed as a layer in QGIS.

The code I used was:

CREATE VIEW view_name AS
SELECT * 
FROM spatial_table
WHERE attribute LIKE 'Something%'

Are there additional references to add to the geopackage? The documentation could not help me get through it : https://www.geopackage.org/guidance/modeling.html

I did not try with SpatiaLite.

Vince
  • 20,017
  • 15
  • 45
  • 64
ThiPa
  • 649
  • 2
  • 9
  • I think you can't use " SELECT * " but that you need to list all the field in order to QGIS to detect the geometry column... – J.R Aug 18 '22 at 08:51
  • 1
    You got already an answer about how to add the required metadata into GeoPackage. This (perhaps partly outdated) document explains what must be done for the metadata with SpatiaLite http://www.gaia-gis.it/gaia-sins/Using-Views-Basic.pdf. – user30184 Aug 18 '22 at 23:49

2 Answers2

4

Create a only a view doesn't enought to QGIS Geopackage. Read this Issue

- 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);
Marco Reliquias
  • 769
  • 1
  • 11
1

A successful way I use:

  1. Load gpkg in DB Manager with SpatiaLite tool
  2. Create a view with an unique id and a geometry column
  3. Add view name and details in table gpkg_contents with an INSERT
  4. Add geometry column and details in table gpkg_geometry_columns with an INSERT
  5. Diconnect gpkg and close QGIS
  6. Open QGIS again and load gpkg with GeoPackage tool. Layer is now visible as a geometric layer and can be loaded.
GeoGyro
  • 1,636
  • 1
  • 15
  • 35