{"word_count": 606, "character_count": 5259}
On Georezo, a French forum, someone asked how to retrieve the streets closest to pipelines.The QGIS expressionSo, 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 repoHere, you'll find a complete reproducible project with layers and expressionsTutorialOne 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 expressionget_feature_by_id('streets', 1)Here it is the first objectTo get the distance to this street from the current object, we calculatedistance(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 usenodes_to_points(geometry(@parent) -- @nodes variableTo get the first node of the target line, we usepoint_n(@nodes, 1)To get the distance to this nodedistance(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 nodearray_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 functionaggregate(  '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 usearray_min(@mean_distances)We find the index of the min mean distance, in the arrayarray_find(@mean_distances, array_min(@mean_distances))We add one because the first element of the array is indexed 0array_find(@mean_distances, array_min(@mean_distances)) + 1Here 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 nameLet'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')ConclusionInterestingly, 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 toolTo 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.BonusWhat if you put many points on each target line ?We can do this with interpolate_line_pointHere 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))