Averaging points within a boundary

Question

What I am trying to do is average a set of points with a separate boundary.

Each of the larger circles is a fixed distance around a sample point (typically the area sampled) with each of the smaller points being a yield point. What I have been doing in the past is creating an averaged raster grid of yield and then using the point sampling tool to give me a yield per sample point (with attributes of the sample included).

While this is a fairly good process, it has hard edges and the points do not always end up in the center of the grid.

What I would like to do is use a similar process as the point sampling tool, but with a noticeable difference. I would like to return the aspects of the sampling attributes along with the average and standard deviation of the yield points within the boundary. I am only needing them in a csv/txt file to run them through some statistics to find relationships.

Is there a process out there someone has used to solve a similar problem?

Answer 1

I prepared a simplified example here:

• sample_points: your small points which has id and yield fields (yield as seen on the picture as label: 366, 294, ...)
• large_circles: your big orange circles. I understand it was originally points which you have displayed as buffer. (I used Geometry generator).

Now we can create a Virtual Layer Layer | Add Layer | Add/Edit Virtual Layer. Import these two layers (sample_points and large_circles) then,

(1) To create a table of Yields

SELECT sample_points.*, large_circles.ID AS circle
FROM sample_points
CROSS JOIN large_circles
ON st_within(sample_points.geometry, st_buffer(large_circles.geometry, 1000))=1

Usually this table is my goal.

(2) According your post, you need average and sd.

SELECT Count(*) AS samples, 
       Avg(sample_points.yield) AS avg_yield, 
       StdDev_Samp(sample_points.yield) AS sd_yield
FROM sample_points
CROSS JOIN large_circles
ON st_within(sample_points.geometry, st_buffer(large_circles.geometry, 1000))=1
GROUP BY large_circles.id