Removing duplicate features in QGIS based on two conditions

Question

I have duplicates (maybe triplicates) in an attribute table and would like to remove the duplicate locations based on comparison to the other duplicate. For example:

Scenario 1:

I would like to select the first "InputID" feature because the "p_pipe_azi" = "rdcross_no" (also denoted by 'dwn_cross1' value in the "status" field.

Scenario 2:

I would also like to choose the first "InputID" feature because the "Distance" value is minimum.

Answer 1

The solution

The basic idea is to create two fields with values 1/0 for true/false for the two conditions (let's call them cond1 and cond2). Concatenate the 1/0 values and convert them to a number. Then get the highest of these, representing the hierarchy of the two conditions: which one to evaluate first/which one is more important.

Let's also create another field called dupl that checks if the feature has a duplicate (or triplicate etc.).

You can then connect these two steps with an expression to get those features you want to keep.

Implementation

You can use QGIS expressions for that. The (array_min()) and (array_max())expressions work with QGIS versions starting from 3.16.

1. Value 1 is true if "p_pipe_azi" = "rdcross_no". Create field cond1 with field calculator and this expression: p_pipe_azi=rdcross_no

2. Value 2 is true for the feature with the minimum distance for each group of duplicates (based an InputID). Create field cond2 with field calculator and this expression: Distance=array_min(array_agg( Distance, group_by:=InputID))

3. Create field dupl with field calculator. To get a 1/0 (true/false) value based on the test if the feature has a duplicate, use this expression: array_length (array_agg( InputID,group_by:=InputID))>1

Now you can use these values to define a rule in which order the conditions should be evaluated with what result (that was not very clear from your question).

For example, to get the result described in your question, use select by expression (see documentation "Selecting features") with this expression:

dupl=1 and 
to_int(cond1 || cond2) = 
array_max( 
    array_agg( 
        to_int(cond1 || cond2), 
        group_by:=InputID
    )
)

You could just do all in one step, avoiding the intermediary steps describe above of creating new fields. To do so, use the respective expressions that create the fields and insert them in the expression above (instead of cond1, cond2). However, the step by step procedure is a bit more intuitive.

Screenshot: features 5 to 8 are duplicates and contain the values provided by you. The last three fields were calculated with the expressions from above. Selected are those two of the duplicate features you want, based on the expression from above: