6

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 VirtualKNN() query)

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.

0

Not sure what the latest released version is. From the home[0] and download page it looks like it's 4.3.0a - for the others there are only RCs or betas.

Jürgen E. Fischer

[0] https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatiaLite [1] http://www.gaia-gis.it/gaia-sins/libspatialite-sources/?C=M;O=D

-- qgis-developer: http://osgeo-org.1560.x6.nabble.com/QGIS-Developer-support-for-SpatiaLite-gt-4-5-td5393755.html

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.