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.

2
  • 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
    Commented Aug 18, 2022 at 8: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 gaia-gis.it/gaia-sins/Using-Views-Basic.pdf.
    – user30184
    Commented Aug 18, 2022 at 23:49

2 Answers 2

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);
0
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.