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.

  • the CSV can't contain a point (i.e. a geometry). You can create geometries from a csv file, and then use the spatial layer to find the nearest neighbors.
    – JGH
    Sep 14, 2019 at 16:07

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.