Dynamic closest point with subgroups in unique layer in QGIS

Question

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:

student_id	tree_id	choice
1	12	s
1	15	s
1	26	c
2	5	s
2	8	s
2	12	c
2	15	c
2	22	s
3	8	s

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

Answer 1

It is possible using an expression in the field calculator. It is not using a Virtual Layer, as per your specific request, but it is fast.

Use on a field called nearest_dist (for example).

It makes an array of distances between all points that meet the expression filter, and gets the minimum. Using a CASE statement, it enters the distances for the rows that match the filter.

with_variable('expr', 'student_id = 1 and choice = \'s\'',  -- set your search expression to a variable called 'expr'
    with_variable('min_distance',         -- set the minimum of the array of distances to a variable
        aggregate(
            layer:= @layer,               -- current layer
            aggregate:= 'min',      
            expression:= distance(        -- get the distance between points
                            $geometry, 
                            geometry(@parent)   -- current feature
                         ), 
            filter:= disjoint($geometry, geometry(@parent)    -- avoid the distance between a point and itself 
                     ) 
                     and 
                     eval(@expr)    -- filter using the expression created on the first line 
        ),
        
        ------------- what is actually returned
        CASE
            WHEN eval(@expr)        -- for the rows where the filter expression evaluates true, enter the distance
                THEN round(@min_distance, 2)    -- get the minimum value in the distances array variable and round to 2 decimal places
            ELSE
                NULL
        END
        -------------
    )
)

---

Note:

I feel it should be a much simpler expression, using the overlay_nearest function. However, during testing, the following expression to the get the nearest feature's fid resulted in some features considering themselves as the nearest.

CASE 
    WHEN student_id = 1 AND choice = 's'

    THEN
        overlay_nearest(layer:= @layer, 
                        expression:= fid, 
                        filter:= student_id = 1 AND choice = 's')[0]            
    ELSE
        NULL
END

Adding an additional filter expression, such as and fid <> attribute(@parent, 'fid') to prevent this, resulted in all NULL values.