Extending "Join attributes by nearest" with WHERE clause in QGIS

Question

I am using QGIS v3.24.1-Tisler and the "Join attributes by nearest" tool in Processing Toolbox, which does 90% of what I need. However I would like to be able to add a WHERE clause, so join point in layer 1 to nearest point in layer 2, where common attributes of a field in layer 1 and layer 2 match. Is this possible?

Example:
'Layer 1' has point geometries, with a field "WATERWAY" which contains the river name value.
'Layer 2' has point geometries, with a field "WATERBODY" which also contains the river name value.

'Layer 1' is river bank inspection data (green diamond symbols in map below).
'Layer 2' is a series of points created with QChainage up the centreline of the river that records the distance from the start of the waterway (light blue circles).

The problem with the "Join attributes by nearest" tool arises when we have two rivers meeting at an intersection and the Points in 'Layer 1' are joined to the wrong points in 'Layer 2' (if they are closer to a distance marker of another waterbody). Working as designed I know, but not the result that I need.

How to run "Join attributes by nearest" with additional statement WHERE LAYER1.WATERWAY = LAYER2.WATERBODY?

The black lines show how "Join attributes by nearest" makes the join, the red arrows show how the correct join will be made if the WHERE clause can be added.

Answer 1

Disclaimer: I am the developer of this plugin

I have just released ProcessX Plug-In plugin for QGIS 3.16+ which has an algorithm to do just that.

Install the Plug-In from the official repository, then go to your processing toolbox -> ProcessX -> Vector - Conditionals -> Join Attributes by Nearest with Condition:

Answer 2

I have tested "Join attributes by field value" but it didn't bring me correct result so decided to make my own script that creates a new layer containing only points from original layer that has same values with join layer.

Assume there are two layers: layer_a(green) and layer_b(orange):

They both have a field 'name' that represented in labels which also contain ids in brakets.

We want to join attributes from layer_b to layer_a by:

• same values in field 'name'
• closest distance from layer_a feature to layer_b feature.

So here is a bit bulky but working code to do so.

def join_attributes(layer_one, layer_two, out_layer, eq_field):
    '''Join attributes from one layer to another by 
    closest features with a case field'''
    # get all features from layers
    layer_one_features = list(layer_one.getFeatures())
    layer_two_features = list(layer_two.getFeatures())
    
    # get case field indexes
    index_one_join = layer_one.fields().names().index(eq_field)
    index_two_join = layer_two.fields().names().index(eq_field)
    
    # get unique values from case field of join layer
    layer_two_unique_values_by_field = list(
        set(
            [j.attributes()[index_two_join] 
            for j in layer_two_features]
            )
        )
    
    for f in layer_one_features:
        f_geom = f.geometry()
        attr_join = f.attributes()[index_one_join] # current case value 
        
        # check if init case attr exist in unique join values
        if attr_join in layer_two_unique_values_by_field:
            # collect features from join layer having same value in case fields
            join_features_attr = [
                j for j in layer_two_features 
                if j.attributes()[index_two_join]==attr_join
            ]
            # finding closest feature among sorted features
            closest_feature = min(
                join_features_attr,
                key = lambda x: x.geometry().distance(f_geom) 
            )
            if closest_feature:
                # making new feature 
                new_feature = QgsFeature()
                new_feature.setFields(out_layer.fields())
                
                # joining attributes
                joined_attrs = f.attributes()+closest_feature.attributes()
                for i, field in enumerate(out_layer.fields()):
                    new_feature[field.name()] = joined_attrs[i]
                
                # setting geometry and adding feature to output layer
                new_feature.setGeometry(f_geom)
                out_layer.dataProvider().addFeatures([new_feature])
    return out_layer


def combined_layer(layer_init, layer_join):
    '''Generating new layer with combined fields'''
    
    iface.mainWindow().blockSignals(True)
    new_layer = QgsVectorLayer("Point", "Join_attrs_layer", "memory")
    new_layer.setCrs(layer_init.crs())
    
    # collecting fields into one list, adding them to new layer
    init_fields = [f for f in layer_init.fields()]
    join_fields = [f for f in layer_join.fields()]
    for f in join_fields:
        f.setName('{}_{}'.format(f.name(), layer_join.name()))
    init_fields+=join_fields
    new_layer.dataProvider().addAttributes([f for f in init_fields]) 
    new_layer.updateFields()
    
    iface.mainWindow().blockSignals(False)
    return new_layer
    
layer_a = QgsProject.instance().mapLayersByName('layer_a')[0]
layer_b = QgsProject.instance().mapLayersByName('layer_b')[0]

new_layer=combined_layer(layer_a, layer_b)
join_attributes(layer_a, layer_b, new_layer, 'name')

QgsProject.instance().addMapLayer(new_layer) 

A brief explanation.

Firstly a new layer is created. Note that it has 'Point' type, so testing on other geometry type requires change in first param of QgsVectorLayer(). This layer contains fields from both layer_a and layer_b and the last ones have 'layer_b' prefix.

Secondly the join is performed. Function join_attributes is just looping through each feature from layer_a, sorting layer_b fetures to contain the same 'name' value and then looks for the closest feature from layer_b. As told before, only features from layer_a that have corresponding feature from layer_b are added but it can be changed by modyfiyng code.

Here is the result:

The final layer here is one with hexagon points. You can see labels contain layer_a 'name' value and ids in brakets like a route from layer_a to layer_b.

Note that distance is calculating in units of layer's CRS. When I first got the final result I was surprized by the choice of closest points and then noticed that my layers were in 4326 and project in 3857.

Edit

To run this code you need to set your layer names and field in these lines:

layer_a = QgsProject.instance().mapLayersByName('layer_a')[0] # change layer_a to your_layer
layer_b = QgsProject.instance().mapLayersByName('layer_b')[0] # change layer_a to your_layer
***
join_attributes(layer_a, layer_b, new_layer, 'name') # change name to your_name