Calculating distance between multiple lines of points in QGIS?

Question

I'm looking to calculate a distance from a sensor reading to that of the nearest sensor reading on a different sensor line, all held within the same points file. Ideally using QGIS.

The image below is an attempt to picture the problem: the red dots are our points. Black lines are to show the points are associated with that line. The grey distances are what we want to associate with the points.

I have looked at nearest neighbour and hub distances but struggling to work out a workflow.

Answer 1

With a Virtual Layer (with a PostGIS table it's far away more simple):

• I have only one point table for sensors with 3 fields: "id", "line_id" and "geometry" (masked field in the attribute table under QGIS);

• from the left to the right, I've numbered the sensor lines with 1, 2, 3 (the middle line have id = 2);

• Here the SQL code:

WITH
--sensors in line 1
s1 AS (SELECT * FROM sensors WHERE line_id = 1),
--sensors in line 2
s2 AS (SELECT * FROM sensors WHERE line_id = 2),
--sensors in line 3
s3 AS (SELECT * FROM sensors WHERE line_id = 3),
--distances between middle line sensors (s2) and s1    
s2s1 AS (
SELECT s2.id AS s2id,
s1.id AS s1id
FROM s2, s1
ORDER BY s2.id, ST_DISTANCE(s2.geometry, s1.geometry)),
--sqlite trick to get for each middle sensor the nearest s1 sensor
s2nears1 AS (
SELECT s2s1.s2id,
SUBSTR(GROUP_CONCAT(s2s1.s1id), 1, STRPOS(GROUP_CONCAT(s2s1.s1id), ',') - 1) AS s1id
FROM s2s1
GROUP BY s2s1.s2id),
--distances between middle line sensors (s2) and s3
s2s3 AS (
SELECT s2.id AS s2id,
s3.id AS s3id
FROM s2, s3
ORDER BY s2.id, ST_DISTANCE(s2.geometry, s3.geometry)),
--nearest s3 sensor for each middle sensor
s2nears3 AS (
SELECT s2s3.s2id,
SUBSTR(GROUP_CONCAT(s2s3.s3id), 1, STRPOS(GROUP_CONCAT(s2s3.s3id), ',') - 1) AS s3id
FROM s2s3
GROUP BY s2s3.s2id)
--final select with all middle s2 id and its nearest line 1 and 3 sensors
SELECT s2.id,
s2nears1.s1id,
s2nears3.s3id
FROM s2
LEFT JOIN s2nears1 ON s2.id = s2nears1.s2id
LEFT JOIN s2nears3 ON s2.id = s2nears3.s2id

Note that a line 1 or 3 sensors can be the nearest sensor for many middle line 2 sensors.

Answer 2

I tried to do it using the Graphical modeler and QGIS algorithms only. It works well except for the attributes join. I can't figure out why it fails to join because when I create joins in the layer property, it works fine. Note my points are a regular 5m grid, but it would work with irregularly spaced points too as long as they are roughly in a grid pattern. Here's what the model, test points, and resulting table look like (point IDs labeled):

Here's the settings for each algorithm in order:

Distance matrix:

• The input and target layers are your points layer.
• Output type: Linear
• Use only nearest: 9 target points (it should capture enough neighbors but if your samples are very irregular, you might have to increase this)

Field calculator (Input/Target line calc.):

You need two calculators, adding two fields which contain the line IDs for inputs and targets in the distance matrix

• Result field name: input_line & target_line
• field type: int
• Formula: attribute( get_feature( 'your_orig_points', 'fid', attribute( $currentfeature, 'InputID') ), 'line_id_field')
• Just replace InputID by TargetID for the second formula

Extract by expression (next/previous line extract.):

Here, the idea was to isolate targets in the distance matrix that were on the previous and next lines. To keep expressions simpler, I did it separately.

• Expressions: "target_line" = "input_line" + 1 & "target_line" = "input_line" - 1

Extract by expression (minimum distance calc.):

This step is where the closest point on the previous and next lines are isolated. The expression is the same for both instances.

• Expression: "Distance" = minimum( "Distance",group_by:="InputID")

Join attributes by field value:

From this point, all the attributes are written somewhere so it's just a matter of joining them back to the original layer. I tried doing it within the model and export a Final layer, but it wouldn't work, neither directly from the Processing toolbox. The table screenshotted above is after a join in the layer properties from the outputs of last two Extract by expression.