Getting nearest point but with a filter conditional in QGIS 3.4?

Question

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.

Answer 1

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

The 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 min or max be added to the query.