Wednesday 24 June 2015

spatialite - Calculating point layer values within polygon features in QGIS 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?



Answer



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:




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...