Virtual layer with spatial query crashes in QGIS

Question

Working with QGIS 3.10. Details:

This should be really simple, but I am unable to solve it: I've got a point layer with two numeric fields "max" and "min", and a polygon layer. I want to create a Virtual layer with the polygons and the sums of "max" and "min" of the points that are within each polygon. All my tries around the following code result in QGIS crash

SELECT polygon.name,
       polygon.geometry,
       SUM(point.max) AS maxim,
       SUM(point.min) AS minim
FROM polygon
JOIN point ON st_within (point.geometry, polygon.geometry) = 1
GROUP BY polygon.name, polygon.geometry

My layers have shapefiles as their data source.

Answer 1

For me, your query works as expected, see:

So the problem does not have to do with your query, but with your layer/data, your project, your QGIS installation or your machine. Without more details, it's difficult to say what caused the problem.

But you can also create new fields with aggregated minim and maxim attributes for points grouped by polygons using QGIS expressions. You can use virtual fields to have dynamic changes.

1. On the point layer, create a new field within_polygon with this expression:

if (
    length (
        make_line (
            $geometry, 
            closest_point (
                array_first (
                    overlay_nearest(
                        'poly', 
                        $geometry
                    )
                ),
                $geometry
            )
        )
    ) =0 , 
    array_first (
        overlay_nearest (
            'poly', 
            name
        )
    ) , 
    false
)

2. On the polygon layer, create a new field minim with this expression (and adapting this to create maxim by simply change expression:="min" to expression:="max"

aggregate( 
    layer:='point', 
    aggregate:='sum', 
    expression:="min",
    filter:= 
        within_polygon = 
        attribute ( 
            @parent, 
            'name'
        )
)