My task is to calculate the average ground survey level for each tree in my dataset, using the 3 closest survey points (optionally within a 3m radius of the tree). I want to write this in a Spatial VIEW (or a table with triggers) so that any time I add a new tree (or new survey points, though that's unlikely) the values update themselves.
Here is the core of my
VirtualKNN() CREATE statement to show what I'm getting at, minus the 3m radius:
CREATE view tree_avglev_k AS SELECT t.geometry AS geometry, t.pk_uid AS pk_uid, t.treeid AS treeid, AVG(l.lvl) AS avg_lvl FROM (SELECT k.*, t.pk_uid FROM knn k, trees t WHERE f_table_name = 'levels' AND ref_geometry = t.geometry) a LEFT JOIN levels l ON (l.ogc_fid = a.fid) LEFT JOIN trees t ON (t.pk_uid = a.pk_uid) GROUP BY a.pk_uid ORDER BY t.treeid
But as of Jan 2018 QGIS 2.99/3 still does not support the
VirtualKNN() function in Spatialite 4.4.0, so it can't process the information (see feature request)
Is there some other more efficient way to work around this (without using PostGIS/any other client-server database)?
What I have tried (as reference):
I have a long, ugly alternative using
PtsDistWithin() and Virtual Index. It's not the most efficient (still new to SQL) but on ~500 trees and ~5000 survey points, it loads in about 2 seconds in spatialite-gui (bit slower than the
But when loading it in QGIS it is particularly slow - and buggy (despite selecting unique integer column in DB Manager, it loads the geometry but won't load attributes). When I add other constraints (like using only levels within a certain area, again using VirtualIndex) it's practically unloadable.
Maybe I need a different approach (e.g., create normal view with just data, join to geometry separately)
However the main thrust of the question is not so much to fix my alternative approach, but to ideally find a solution as concise and zippy as
VirtualKNN() for QGIS.