Calculating distance by common ID (one to multiple) in two point layers?

Question

In point layer A, the locations of farmers' houses are recorded. Each farmer has a unique ID.

In point layer B, the centroids of farmers' plots are included. Each farmer has more than one plot, thus multiple points in the layer B correspond to layer A by farmer ID.

Based on the common farmer ID, I want to calculate the exact distances (not the nearest) from points of each farmer's house in layer A to his or her multiple plots (points) in layer B using QGIS.

I have tried "Distance matrix" as suggested in most of the posts but it doesn't work well.

Any pointers?

The graph above shows a fraction of my dataset. Green points as layer A for farmer's house; Brown points as layer B for plots. One cluster (visually) can be considered as a village. Usually, plots are clustered around the farmers' house (but not always the case).

Answer 1

Using a virtual layer is probably the easiest way to do this. Add a virtual layer with the following definition:

SELECT 
    a.farmer_id as farmer_id,
    b.field_id as field_id,
    st_distance(a.geometry, b.geometry) AS distance,
    make_line(a.geometry, b.geometry) AS geometry
FROM 
    farmers AS a, 
    fields AS b
WHERE a.farmer_id = b.farmer_id

This adds a layer of lines connecting each farmer's point with the field centroid points. You can then right click on the layer and select save layer as to save it in the format of your choice. The line:

make_line(a.geometry, b.geometry) AS geometry

draws the lines, and is optional. You can remove it if you just want a table of distances. Remember to remove the comma after 'distance' if you do this.

The farmers and fields in the FROM section should be replaced with the appropriate names of your layers. farmer_id and field_id in the SELECT and WHERE should be replaced with the names of your fields.

Note in the WHERE section is is assumed that the 'Farmer ID' field is called the same in both layers - this is not required.

As an aside you could do this without SQL, which would would involve the following steps:

1. Make a linear Distance Matrix (DM) of Farmers and Fields layers
2. Join Field layer to DM layer
3. Select records from DM layer by attribute using FarmerID (from DM) equals FarmerID (from joined Fields layer)
4. Save selected features as.

Not too bad, but you have to remember them if you want to reproduce the work, whereas simply saving the query used to produce the virtual layer gives you a record of what you've done.