Nearest point to another point with condition in QGIS 3

Question

I have two different statewide layers of points. One set of points are locations of ballot dropboxes and the other points are residences. Both layers have a column for city. I'm trying to calculate the nearest dropbox to each residence and end up with a table of the closest dropbox to the residence that retains the residence unique id and all the attributes of the dropbox layer (geo, address string, note string). The catch is they must be in the same city. For example, in northern Boston, MA, the nearest dropbox point by distance is in southern Cambridge, MA but Boston voters cannot drop off their ballot in Cambridge; it must be a Boston dropbox if the residence is in Boston.

I'm trying to understand QGIS better and it seems like this is maybe something I could iterate over although I'm not sure how I'd go about something like residence.city == dropbox.city for each city. Or is it more of a batched process or something that would require a model?

Answer 1

Try using virtual layers. From Finding nearest neighbors between two tables with point locations in SpatiaLite?.

Both my layers have the field "kom_kod" which is city code. This work on my small sample but gets very slow with large datasets. Do you have postgis?

SELECT *, MIN(ST_Distance(b.GEOMETRY,d.GEOMETRY)) AS DIST, b.geometry
FROM "houses" As b, "dropbox" As d
WHERE b."kom_kod" = d."kom_kod"
GROUP BY b.fid

Answer 2

In QGIS there may be more than one way to carry out your task. I would go to the mmqgis plugin and see if the Hub Lines /Distance tool will indirectly meet your needs. There is a web page where the champion Michael Minn wrote about it, at http://michaelminn.com/linux/mmqgis/ scroll down till you get to Hub *** .

To install this plugin, go to the menu items PLUGINS| search by mmq* as per screen shot below.