I have a point (road accidents) and a line (roads) layer. The line layer has two attributes: road_code and segment_code. Each line is composed by multiple minor segments. The point layer contains a road_code, but not a segment_code. So by using a field join I can link an accident to a road, but not to a specific segment of this road. I managed in SQL to link an accident to a specific segment of the correct road with the following code:
SELECT accident_id, segment_code
FROM ( SELECT acc.id AS accident_id,
rd.segment_code,
st_distance(rd.geom, acc.geom) AS distance,
rank() OVER (PARTITION BY acc.id ORDER BY (st_distance(rd.geom, acc.geom), rd.segment_code) AS closeness
FROM accidents acc
LEFT JOIN roads rd ON acc.road_code = rd.road_code
ORDER BY acc.id, (rank() OVER (PARTITION BY acc.id ORDER BY (st_distance(rd.geom, acc.geom)), rd.segment_code))) tab
WHERE tab.closeness = 1
ORDER BY tab.accident_id
Basically the code applies at the same time what in QGIS are the JOIN BY ATTRIBUTE FIELD (to make sure the accident is linked to the correct road) and the JOIN BY NEAREST (to make sure the attribute is linked to the nearest segment of that correct road).
However, I want to achieve the same in a QGIS Graphical Model. How can I do?
