5

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 = all trees with their ID - Black = student's choice

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:

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

1 Answer 1

3

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

enter image description here


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.

3
  • Your first solution works well (the second one returns indeed the same fid as the source), thanks! And the virtual field approach is also very good. But I have more than 300 trees and up to 20 students who each select 10-20 trees, and QGIS freezes with all this data.
    – romainbh
    Commented Feb 17, 2022 at 17:22
  • I learned that the issue in the second solution has been reported as a bug: github.com/qgis/QGIS/issues/47201. The first solution gets the distance but it doesn't get the corresponding ID, which is a severe limitation, unfortunately.
    – Matt
    Commented Feb 17, 2022 at 17:25
  • Wow, it seems you have found a bug. And, if resolved by QGIS developpers, that could probably solve my problem! Thank you very much for your investigation on my problem.
    – romainbh
    Commented Feb 20, 2022 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.