I have two point layers in CSV files, each with about 500 points/rows. Each point in both layers has a year column (attribute). I know how to use Distance Matrix to find the nearest point. BUT: For each point in Layer 1, I want to find the nearest point from Layer 2 that has one of the three previous years to the point in Layer 1. For example, if point x in Layer 1 has 1989 in the Year column, then it should search for nearest point only out of all points in Layer 2 that have 1986, 1987, 1988 in the Year column, and return the nearest one.
You can create a virtual layer that will join the two layers and apply the desired filtering.
go to the menu
Layer > Add Layer > Add/Edit Virtual Layer... and enter the query
SELECT pt1.id, pt2.id, ROUND(MIN(ST_Distance(pt1.geometry, pt2.geometry)),2) AS distance FROM point_layer_1 AS pt1, point_layer_2 AS pt2 WHERE pt2.year_column BETWEEN pt1.year_column - 3 AND pt1.year_column - 1 GROUP BY pt1.id ORDER BY distance DESC
group by will ensure we get a single row for each row in layer1. Because we
order by distance, the group by will retain the 1st entry, i.e. the nearest one.
edit: according to this doc, since there is a single
min() in use, we could drop the
order by clause and still get
pt2.id referring to the closest point. This would however fail should a secondary
max be added to the query.