I work in forest and I need to export plans for students. They have to designate some trees on field (a forest plot with all trees numbered) and I get a CSV list with the student's number, the tree's numbers and a choice (tree stay = s, tree cut = c), like this:
I perform a Left Join in a Virtual Layer "Choice" between this list and the layer containing all the trees, this way I get all the student's trees with coordinates and this layer updates itself as soon as I replace the student file:
SELECT * FROM list_students_trees as l LEFT JOIN trees as t ON l.tree_no = t.id
Green = base layer Trees with all trees id | Black = virtual layer Choice with student's trees
And I can export atlases, one plan by student, all works fine, and is dynamic using the Virtual Layer.
But now, for each student, I need to add the distance of the closest selected tree "s".
Example for student 1:
Trees 12 and 15 are the only ones with condition
student_id = 1 and choice = "s"
I've tried with this SELECT code and it works, but QGIS freezes during a while each time I move the map or when I open the attribute table:
SELECT c1.*, c2.tree_id as closest_id, ROUND(MIN(ST_Distance(c1.geometry, c2.geometry)), 2) AS closest_distance FROM Choice AS c1, Choice AS c2 WHERE c1.tree_id <> c2.tree_id AND c1.choice = "s" AND c2.choice = "s" AND c1.student_no = c2.student_no GROUP BY c1.student_no, c1.tree_id
Is there a way to optimize all this in Virtual Layer?
I know that is possible with PostGIS, but students can't all install it on their notebook (and they're not able to use it).