I have a points table (all_trees) and polygons table (workareas) and a view (affected_trees_view) as follows:
SELECT * FROM all_trees, workareas WHERE intersects(st_buffer( all_trees.geometry, CASE WHEN rpz_m IS NULL THEN 1 ELSE rpz_m END), workareas.geometry) GROUP BY all_trees.treeid
This returns 92 point features, which show up in DB manager/Qspatialite as expected.
However, when I try to load the view into QGIS I have two issues:
- Points don't always visually appear on the canvas (if I load it as a spatial view using Qspatialite it works, sometimes... whether or not I manually insert the views_geometry_columns information).
- Only see a single point in the attribute table even if the points visually appear on the canvas.
The feature count in Qspatialite, the layers panel, and attribute table status bar show the right number (92), but the attribute table shows "Filtered: 1" - see highlighted sections below
So I then created a table affected_trees from the view using
CREATE TABLE affected_trees AS SELECT * FROM affected_trees view;'
and that works perfectly fine - correct feature count, features appear, attribute table shows everything. (see second attribute table behind the first one below)
Why and how does the data get filtered like that?
It appears to me that the data itself is not exactly corrupted, it's just got something to do with how QGIS handles spatial views.
I've looked up whatever I could with my limited understanding of databases; the issue I have is somewhat different to this, for example. I've followed the latest answer from Can QGIS read Spatialite views? regarding updating views_geometry_columns - I've tried hand writing the view, I've tried using Qspatialite to generate the spatial view, I've used the following code:
INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only ) VALUES ('affected_trees_view', 'geometry', 'ROWID', 'all_trees', 'geometry', 1);
But I still encounter the same issues (particularly #2). I've posted a question about a similar issue with QGIS Virtual Layers here, though I have no idea if the two are connected.
I would like to be able to show the spatial view in QGIS so it shows points as soon as I change the dependent factors (workareas geometry, rpz_m value), rather than keep having to update the table with triggers.