4

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.

enter image description here

4
  • How/Where do you want the results? As a new layer, an attribute on the squares?
    – BERA
    May 5 at 8: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 5 at 8: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 5 at 8: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: drive.google.com/drive/folders/… 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 5 at 9:02

2 Answers 2

4

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.

input

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:

result

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:

1
  • Thank you so much! It worked, though with a big data table it takes time
    – Wayfarer
    May 5 at 12:29
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: enter image description here

1
  • 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 5 at 12:33

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.