Calculating mean value for points within polygon in QGIS

Question

I have square polygons that follow the route of a boat. There is also a point layer with different measurements like salinity, temperature etc. Sometimes there is only 1 point inside the polygon, but sometimes more. In that case, I need to calculate the mean for different measurements inside the polygon, so as a result, I can create a new point layer with only 1 point per polygon (the mean - if there were several points, and keep the same data if there were only 1 point in a polygon).

I tried to use Join attributes by location (summary) tool, but I got all NULL values for the mean values I wanted for different columns. The same result was from v.vect.stats from GRASS.

Also if a point belongs to two polygons at the same time, I want it to be included in both.

How/Where do you want the results? As a new layer, an attribute on the squares? — BERA, May 05 '22 at 08:33
What kind of output do you expect to achieve? Is there a possibllity to share your data with us? What are the types of your fields with values (maybe the refactoring is needed.)? Did you try to join points to polygons (an not polygons to points)? — Taras, May 05 '22 at 08:35
@BERA Preferably as a new point layer. But it's possible to create a new layer if I would get a new attribute to squares, so it doesn't really matter, I just can't figure out how to join them correctly and get the means — Wayfarer, May 05 '22 at 08:51
@Taras I can't really share the data, just because I'm not allowed to do that, but I've extracted several polygons and several points (with only some columns like salinity and temperature). You can get the shp files here: https://drive.google.com/drive/folders/1Wjvs-UFGdrGjE-4TGPA0z3YEAobUtJs0?usp=sharing
Data type for measurements in point layer is Real. As a final result I want a new point layer, were it will be 1 point instead for example 5 points and the measurement values will be means. And if there was 1 point in a square, I want just to keep it. Thanks for trying to help. — Wayfarer, May 05 '22 at 09:02

Taras · Answer 1 · 2022-05-05T10:08:27.113

There is a possibility using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer....

Let's assume there are two layers one with points called 'points_example' (green) and second with polygons called 'square_example' (violet), see image below.

With the following query, it is possible to calculate the mean for different measurements inside the polygon, so as a result, I can create a new point layer with only 1 point per polygon (the mean - if there were several points, and keep the same data if there were only 1 point in a polygon).

SELECT
    poly.begin,
    poly.end,
    poly.distance,
    poly.angle,
    poly.square,
    ROUND(SUM(poi."Sali_psu")/COUNT(1),4) AS "SaliMean",
    ROUND(SUM(poi."TempCels")/COUNT(1),4) AS "TempCMean",
    ROUND(SUM(poi."Temp_Air")/COUNT(1),4) AS "TempAMean",
    ROUND(SUM(poi."DaLeMinu")/COUNT(1),4) AS "DaLeMean",
    ROUND(SUM(poi."Julianday")/COUNT(1),4) AS "JulianMean",
    CASE
        WHEN COUNT(1) < 2 THEN poi.geometry
        ELSE st_centroid(poly.geometry)
    END AS geometry
FROM
    "points_example" AS poi,
    "square_example" AS poly
WHERE
    st_within(poi.geometry, poly.geometry)
GROUP BY
    poly.geometry

The output point layer (pink) with its attribute table will look like:

Note: that for cases when there are more than 1 point per square, the new geometry was appropriated as a centroid of a square by means of the ST_Centroid().

References:

Selecting all columns except geometry using Virtual Layer in QGIS

Thank you so much! It worked, though with a big data table it takes time — Wayfarer, May 05 '22 at 12:29

score 3 · Answer 2 · answered May 05 '22 at 10:47

3

Solution 1

Using the data you provided, I get meaningful results using Join Attributes by Location (Summary), see screenshot below and be sure to make settings accordingly.

Solution 2

Another option is to create a new field on the polygon layer using field calculator with this expression:

array_mean(
    overlay_intersects(
        'points_example',  -- name of the layer
        Temp_Air  -- name of the field
    )
)

Using Join Attributes by Location (Summary) correctly:

answered May 05 '22 at 10:47

Babel

71,072
14
78
208

1

Thank you! My mistake was that I selected "where the features are within" and not "intersect", otherwise everything works just fine. The second option is also nice – Wayfarer May 05 '22 at 12:33

Calculating mean value for points within polygon in QGIS

2 Answers2

Linked