Seeking QGIS equivalent tool/plugin of ArcMap Spatial Join within Distance

Question

I would like to do a join by location between two point layers in QGIS 3 (3.4.10, Coruña). I would like to get the mean (or median) value of an attribute all the points of point layer 2 within a 5 meter radius of every point of point layer 1.

In ArcMap my approach would be:

• Point layer 1 & 2: spatial join, fill in the field map as "Mean" for the attribute, set join as "within a distance" and fill in 5 meters.

My approch in QGIS3 would be:

• point layer 1: Add autoincremental field so i have a unique id for every feature
• point layer 1: Buffer of 5 meter
• buffered layer & point layer 2: Join attributes by location (Summary), fields to summarise -> the attribute field en summaries to calculate-> mean
• Point layer 1 and summarised layer: Join attributes by field value on the base of the id field made in step one.

I walk through this process all the time, so I was wondering if there is a stand alone tool/plugin in QGIS3 for this?

Answer 1

You can do the join and statistics computation using a virtual layer. It would help to have an ID on the point1 layer.

The trick is to use the true distance between the point (in the CRS unit) instead of using a buffer.

If you want to keep points from layer1 with no nearby points in layer2, replace the JOIN by a LEFT JOIN

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query.

select a.*, AVG(b.field1) as avg_field1, AVG(b.field2) as avg_field2
FROM myPointLayer1 a
  JOIN myPointLayer2 b
    ON ST_Distance(a.geometry, b.geometry) <= 5
GROUP BY a.id