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.

Answer 1

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

Answer 2

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: