On Georezo, a French forum, someone asked how to retrieve the streets closest to pipelines.

The QGIS expression

So, let’s suppose we have one data for pipelines (source) and one for streets (target), here is a QGIS expression that retrieves the street data object index closest to each pipeline.

with_variable(
  'mean_distances',
  aggregate(
    'streets', 
    'array_agg', 
    array_avg(
      with_variable(
        'nodes',
        nodes_to_points(geometry(@parent)),
        array_foreach(
          generate_series(1, num_points(@nodes)),
          distance(point_n(@nodes, @element), $geometry)
        )
      )
    )
  ),
  array_find(@mean_distances, array_min(@mean_distances)) + 1
)

Github repo

Here, you’ll find a complete reproducible project with layers and expressions

Tutorial

One way to master QGIS field calculator expressions is to conceive them progressively, iteratively. Let’s decompose it.

Add a virtual field named nearest_with_nodes as integer which will store the index of the perfect target candidate, that is the index of the closest street line.

Open the Field Calculator window and follow this tutorial.

One street can be retrieved with the expression

get_feature_by_id('streets', 1)

Here it is the first object

To get the distance to this street from the current object, we calculate

distance(get_feature_by_id('streets', 1), $geometry)

But this distance is not ideal, because it is the closest space from one line to the other. If nodes are spread out, this does not reflect the “true” distance.

The “true” distance can be approached by calculating the distance from each of target object nodes to the current source line.

To get the nodes from the target line, we use

nodes_to_points(geometry(@parent) -- @nodes variable

To get the first node of the target line, we use

point_n(@nodes, 1)

To get the distance to this node

distance(point_n(@nodes, 1), $geometry)

But we have to compute it to each node.

We get the number of point with num_points(@nodes)

So we use generate_series to create the indexes of the nodes, going from 1 to the number of nodes. If 3 nodes, we’ll have [1,2,3]

generate_series(1, num_points(@nodes))

And we use array_foreach to calculate the distance to each node

array_foreach(
generate_series(1, num_points(@nodes)),
distance(point_n(@nodes, @element), $geometry)
)

Now we have an array of distances, let’s say [1, 3, 2], we want to get the average of the distance. For this, we use array_avg available after installing the ArrayPlus plugin.

array_avg(
  with_variable(
    'nodes',
    nodes_to_points(geometry(@parent)),
    array_foreach(
      generate_series(1, num_points(@nodes)),
      distance(point_n(@nodes, @element), $geometry)
    )
  )
)

And we calculate this mean distance from source line (pipeline) to each of the target lines (streets).

For this, we use aggregate function

aggregate(
  'streets',
  'array_agg',
  array_avg(
    with_variable(
      'nodes',
      nodes_to_points(geometry(@parent)),
      array_foreach(
        generate_series(1, num_points(@nodes)),
        distance(point_n(@nodes, @element), $geometry)
      )
    )
  )
)

Now, we have an array of mean distances, one mean distance per target line.

But we want to retrieve the min mean distance, and its index. Its index will be the one of the closest line.

To get the min distance, we use

array_min(@mean_distances)

We find the index of the min mean distance, in the array

array_find(@mean_distances, array_min(@mean_distances))

We add one because the first element of the array is indexed 0

array_find(@mean_distances, array_min(@mean_distances)) + 1

Here is the complete expression :

with_variable(
  'mean_distances',
  aggregate(
    'streets',
    'array_agg',
    array_avg(
      with_variable(
        'nodes',
        nodes_to_points(geometry(@parent)),
        array_foreach(
          generate_series(1, num_points(@nodes)),
          distance(point_n(@nodes, @element), $geometry)
        )
      )
    )
  ),
array_find(@mean_distances, array_min(@mean_distances)) + 1
)

So I hope you understood how to find the closest line to another one and the logic behind creating sophisticated expressions.

Display the street name

Let’s say the street layer has “name” attribute.

You can display as a label informations for the target line like this:

attribute(get_feature_by_id('streets', nearest_with_nodes), 'name')

Conclusion

Interestingly, you can see you can execute sophisticated tasks internally with expressions in the field calculator without making any SQLish virtual layers or applying magic python code.

That makes the Field Calculator a powerful and fun-to-use tool

To me, ArrayPlus should benefit from higher visibility as adding really interesting operators in QGIS. A user had to remain me of its existence. It would be nice if it joined the panoply of Field Calculator functions.

Bonus

What if you put many points on each target line ?

We can do this with interpolate_line_point

Here is the expression :

with_variable(
	'mult',
	10, 
	with_variable(
		'mean_distances',
		aggregate(
			'streets',
			'array_agg',
			with_variable(
				'feat',
				geometry(@parent),
				with_variable(
					'distances',
					array_foreach(
					generate_series(0, length(@feat), length(@feat)/((num_points(@feat)-1) * @mult)),
					distance($geometry, line_interpolate_point(@feat, @element))),
					array_avg(@distances)
				)
			)
		),
	array_find(@mean_distances, array_min(@mean_distances)) + 1
	)
)

---

---