4

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?

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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