[GIS] QGIS and geopackage spatial views

geopackageqgis

I'm working on a project where I want to deal with one-to-many relationships using a geopackage. I can create layers and non-spatial tables but I'm having trouble with the spatial view. I want to have many condition audits conducted on a footpath and want a view to display only the most recent condition audit but QGIS doesn't seem to be working with a spatial view in the geopackage.

Software versions:
QGIS 2.18.3,
'DB Browser for SQLite' version 3.9.1

workflow:

Using QGIS, create a polygon layer using 'Layer>Create Layer>New Geopackage Layer'
Then create a non-spatial layer using the same method and selecting the same geopackage but select the ‘Geometry type’ as 'non spatial'
Using DB Browser enable foreign keys checkbox in pragams and add a foreign key constraint to non-spatial table (FootpathCondition)

Then create a spatial view:

CREATE VIEW FootpathConditionRatings as Select fp2.fid as fid, fpc2.fid as AuditID, fpc2.AuditData as DateAudited, fp2.geometry as geometry from Footpath as fp2 INNER JOIN FootpathCondition as fpc2 ON fpc2.FK_Footpath = fp2.fid where AuditID in( select AID from( Select fp.fid as fid, fpc.fid as AID, MAX(fpc.AuditData) as DateAudited from Footpath as fp INNER JOIN FootpathCondition as fpc ON fpc.FK_Footpath = fp.fid group by fp.fid ) )

add values to gpkg_contents and gpkg_geometry_columns to make the view a spatial layer
using QGIS populate tables adding 2 polygons and 8 records in non-spatial table (representing 4 transactions against each polygon)
Using DB Browser check 2 tables' and the view's data. View returns 2 rows with the most recent date, as expected.
```
fid AuditID DateAudited

"2" "3" "2017-02-14"

"1" "8" "2016-05-14"
```
Add view to QGIS using DB Manager and check data

Outcome:
QGIS displays the view's geometry correctly however the 2 records in the attribute table are the same values. If I copy the 2 records to the clipboard and paste then these are the values I get:

    wkt_geom    fid AuditID DateAudited

    NULL

If I select a row in the attribute table both rows are selected and both geometries are also selected.

I'm wondering if anyone else is getting this behaviour with spatial views in a geopackage?

Best Answer

I retested the geopackage I created using QGIS 2.18.10. I didn't make any changes to the geopackage and the spatial views worked correctly when viewed with 2.18.10.

Related Solutions

QGIS – How QGIS 3.2.3 Handles or Creates Spatial Indexes in a Geopackage

When you create a new GeoPackage layer you can select if spatial index is created or not from the advanced options

If you want to add the index later goto layer properties and press the button

I could not find any way to check with QGIS if spatial index exists or not. The index is stored into virtual layer that is named as [rtree][tablename][geometry column] and you can use any SQLite capable application and see if the table is there.

Ogrinfo belongs to SQLite capable applications and spatial index exists if this command prints some ogrfeatures on screen

ogrinfo indextest.gpkg -sql "select * from sqlite_master where type='table' and name like 'rtree_indextest%' LIMIT 1"

Result:

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
type: String (0.0)
name: String (0.0)
tbl_name: String (0.0)
rootpage: Integer64 (0.0)
sql: String (0.0)
OGRFeature(SELECT):0
  type (String) = table
  name (String) = rtree_indextest_geometry
  tbl_name (String) = rtree_indextest_geometry
  rootpage (Integer64) = 0
  sql (String) = CREATE VIRTUAL TABLE "rtree_indextest_geometry" USING rtree(id, minx, maxx, miny, m
axy)

QGIS – GeoPackage SQLite Feature Count Issue

GDAL and QGIS want to know how many features there are on a layer but SELECT COUNT(*) is slow for big tables. Therefore GDAL creates an additional metadata table gpkg_ogr_contents from where the feature count can be fetched directly. The structure of the table can be checked from sqlite_master:

SELECT sql FROM sqlite_master
WHERE type = 'table' AND name = 'gpkg_ogr_contents';

CREATE TABLE gpkg_ogr_contents(table_name TEXT NOT NULL PRIMARY KEY,feature_count INTEGER DEFAULT NULL);

The gpkg_ogr_metadata table does not have triggers for guarding the integrity and adding such trigger would probably be a bad idea because the table is not used by other programs than GDAL.

The GDAL GPKG driver understands the meaning of the extra metadata table and feature_count field. I made a test by deleting one feature with ogrinfo and the feature_count was correctly updated.

ogrinfo test.gpkg -sql "delete from test where id=100"

QGIS also updated the feature count for me when I deleted some features from the attribute table by selecting and pressing Del or by using the Delete selected feature button. The count got updated immediately in the table of contents during the editing session and the change was permanent when I stopped editing and saved the edits.

I did not test the QGIS Execute SQL method because I found a few slightly different Execute SQL tools from the processing toolbox but none of them looked alike your screen capture. I guess anyway that the SQL tool that you used edits the SQLite table directly in the same way than DB Browser for SQLite without going through the GDAL driver and therefore does not update the feature_count. A workaround would be to update also the gpkg_ogr_contents table manually

UPDATE gpkg_ogr_contents set feature_count=(SELECT count(*) from table) where table_name='table';

I used QGIS 3.24 for testing.

Best Answer

Related Solutions

QGIS – How QGIS 3.2.3 Handles or Creates Spatial Indexes in a Geopackage

QGIS – GeoPackage SQLite Feature Count Issue

Related Question