Spatialite view appears 'filtered' in attribute table, won't load in canvas

Question

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:

1. 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).
2. 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.

Answer 1

So now that I actually know a little more than nothing about databases, I found out what the issue was. Spatialite views do not seem to load properly in QGIS 2.18.x or QGIS 2.99/3 out of the box; you need to manually define the unique values column (and sometimes geometry) for the view to process. row_id will not cut it.

Here are instructions on how to do so assuming that:

• you already have a view in spatialite
• the view is registered in view_geometry_columns
• Important: the view includes some kind of unique value (primary key?) from the source geometry table (the table referred to by f_table_name in view_geometry_columns for this view)

---

QGIS 2.18.x

( 0. Make sure you have the DB Manager plugin installed )

1. Create a connection to your spatialite database (Add Layer -> Add Spatialite Database or from DB Manager or Browser Panel, etc.).

2. Open DB Manager. Under SpatiaLite, navigate to your database.

3. Select the SQL Query button (or press F2). This will open up a query window for your database.

4. Select records from your view using SQL (e.g. SELECT * from view_name) and hit Execute. Your data should load in the datasheet below. If you encounter an error check your SQL. Don't worry if the geometry column appears blank.

5. Tick the 'Load as new layer' checkbox, and from the resulting options tick the Column with unique values and Geometry column checkboxes and select the appropriate columns from your view.

   For unique values I strongly suggest a primary key from one of the original tables or similar. In the example below, I tried using TreeID (which is also unique, though not with a constraint) and encountered the problem in the original question. With PK_UID, it worked perfectly.

6. Hit Load now!

---

QGIS 2.99 / 3

1. Create a connection to your spatialite database using Layer -> Data Source Manager or Add Layer, etc.

   NOTE: Do not add the spatialite layer from a project folder in the Browser Panel. You need to create a connection or you can't load it from the in-built DB manager.

2. Go to Database -> DB manager and follow steps 2-6 for QGIS 2.18.x.

OR

2. Load your view into the canvas from the Data Source Manager/Browser Panel -> Spatialite

   NOTE: Once again, do not load from the project folder in the Browser Panel.

3. Right click and select Update Sql Layer (if this option doesn't appear, then you didn't load the view from the right place) Your data should already be loaded with the default SQL query and geometry column selected.

   If you get an error and the SQL query looks wrong (e.g. SELECT * FROM "SELECT * FROM ""affected""") with no data, then modify it appropriately and remember to hit Execute. This happens sometimes after selecting Update SQL Layer multiple consecutive times.

4. Select your column with unique values (as with 2.18.x, I suggest a primary key from the original table or similar. It still didn't work when I selected TreeID, but was fine with PK_UID)

5. Hit Update