3

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.

example

  • 2
    Hi. Welcome to GIS SE ! Sometimes you have just one nearest sensor, sometimes two (first and last sensor on the image). Sometimes you have nearest sensor on each line, sometimes not. How do you treat these cases ? – J. Monticolo Jul 10 at 13:47
  • 1
    Hi! Ideally there will only be one closest point. call it an error in my drawing. – Dave Jul 10 at 14:00
  • 1
    Ok. Do sensors have belonging line attributes ? – J. Monticolo Jul 10 at 14:13
  • Yes each point has a Line id Unique to the line. – Dave Jul 10 at 14:53
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.

  • GROUP_CONCAT( ) Mysql? – Cyril Jul 10 at 17:46
  • 1
    Sqlite (doc) function and present in QGIS Virtual Layers. – J. Monticolo Jul 10 at 19:16
1

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):

enter image description here enter image description here enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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