2

There are two layers

  • polygon BereichBerechnung with a field "Are_Number"
  • points EW2017 with a field "EWjeAdr"

Example_of_data

In QGIS with a Virtual Layer, I want to calculate the sum of the field "EWjeAdr" for points that are within each feature from the layer BereichBerechnung.

I have found Updating field to give count of points in polygon using STIntersects? which seems related but I cannot figure out how to adjust my expression properly.

With this code:

SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON BereichBerechnung.ogr_geometry.STContains(EW2017.ogr_geometry) = 1;
GROUP BY Are_Number

I am getting the following error:

Error

How can I do it?

3

With a bit of luck and suggestions from @Kazuhito, I ended up with

SELECT ST_UNION(B.geometry), B.Are_Number, SUM(D.EWjeAdr)
FROM BereichBerechnung AS B
JOIN EW_Data AS D ON contains(B.geometry, D.geometry)
GROUP BY B.Are_Number

In case if there is a necessity to preserve geometries for which there are no overlaps between polygons and points in other words contains(B.geometry, D.geometry) command gives NULL use LEFT JOIN which will do the trick.

SELECT ST_UNION(B.geometry), B.Are_Number, SUM(D.EWjeAdr)
FROM BereichBerechnung AS B
LEFT JOIN EW_Data AS D ON contains(B.geometry, D.geometry)
GROUP BY B.Are_Number

References:

2

For QGIS 3.4

In the Processing Toolbox use the tool Join attributes by location (summary), make sure it's the summary tool and not the other one.


For QGIS 2.18

In the Processing Toolbox use the tool Join attributes by location and change the Attribute summary option to Take summary of intersecting features.


The Input layer should be the polygon and the Join Layer should be the points.

I created two scratch layers in a project, in the point layer I populated a field with a random number ("rand_num"), see the result of the joined polygon here:

Result_of_execution

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.