5

Working with QGIS 3.10. Details:

enter image description here

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.

0
3

For me, your query works as expected, see:

https://i.stack.imgur.com/Wdgsd.png

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

enter image description here

11
  • Yeah, there must be an issue in my QGIS installation, since it doesn't work in simpler layers either. – Jesús Pinilla Jan 22 at 16:33
  • Which version do you use? – Babel Jan 22 at 16:36
  • Are your points and polygons single part or multiparts? Multipart could be a problem. – Babel Jan 22 at 16:37
  • 1
    That substantiates the suspicion that the performance to calculate the output of the query crashed your virtual layer. – Babel Jan 23 at 10:49
  • 1
    The very concept of virtual layers implies that the output is not permanent, but temporary and thus is always re-calculated, without being definitively stored. Plus you have filebased data, not a database. As mentioned: in my tiny example, virtual fields were quite slow. Creating permanent fields was fast and easy. – Babel Jan 23 at 10:58

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.