2

I have some duplicates in my data attribute table like you can see below:

enter image description here

I used the following formula

count(1, "Site ID") > 1

based on this question: Identifying duplicate attributes in field using QGIS

But as you can see I have just the duplicate values selected. When I click on any of them in order to delete it, the rest of selected stuff disappears (is deselected).

I am wondering about the option, which would allow me to remove these duplicates by some expression.

A similar problem was considered in these threads:

where the Python approach was suggested.

I personally think, the delete duplicate features Removing overlapping/duplicate polygons in the same layer using QGIS is not the best option here, because I have to base my selection on just one column in the data attribute table. How can I sort it out?

7
  • Sorry, the question is not clear: what is your problem? You have a selection and don't know how to delete? Or you don't know how to generate an appropriate selection? Based on which fields exactly duplicates should be identiefied?
    – Babel
    Apr 4 at 9:31
  • I have the selection and I don't know how to delete just 1 record from 2. As you can see I have some of them doubled and I need one of them deleted for each case. I have them 27.
    – MKR
    Apr 4 at 9:38
  • 3
    Did you try the "Delete duplicates by attribute"?
    – Taras
    Apr 4 at 9:51
  • @Taras I am not the best at reading the references and here is the problem. If you could explain to me how to read these, I would be really grateful.
    – MKR
    Apr 4 at 9:55
  • If there are two with the same Pole ID, which one should be deleted?
    – BERA
    Apr 4 at 10:04

2 Answers 2

1

Let's say you have duplicate values in field value and want to keep just one of the duplicate: the one with the smallest id value: use Select by expression with this expression:

array_contains (
    with_variable (
        'array',
        array_agg( 
            $id, 
            group_by:="Site ID", 
            order_by:=$id
        ),
        array_remove_all( 
            @array, 
            array_first( @array)
        )
    ),
    $id
)

enter image description here

4
  • I tried: array_contains ( with_variable ( 'array', array_agg( 'Site ID', group_by:='Site ID', order_by:='Site ID' ), array_remove_all( @array, array_first( @array) ) ), 'Site ID' ) and it selects nothing, as have the "false" in the result
    – MKR
    Apr 4 at 9:58
  • That's why I asked based on what you want to identify duplicates - it seems only based on Site Id. Try: array_contains ( with_variable ( 'array', array_agg( 'Site ID', group_by:='Site ID', order_by:=$id ), array_remove_all( @array, array_first( @array) ) ), $id)
    – Babel
    Apr 4 at 10:45
  • Still no reaction, I have even created the id column not existing before, but the result is the same.
    – MKR
    Apr 4 at 11:02
  • Sorry, did not have access to QGIS when I wrote my last comment. I mean: take my initial expression and replace all id by $id and value by Site Id, thus the expression should look like: array_contains ( with_variable ( 'array', array_agg( $id, group_by:=Site ID, order_by:=$id ), array_remove_all( @array, array_first( @array) ) ), $id )
    – Babel
    Apr 4 at 11:39
0

It is not an expression, but there is a tool in QGIS for deleting duplicates called "Delete duplicates by attribute".

Deletes duplicate rows by only considering the specified field / fields. The first matching row will be retained, and duplicates will be discarded.

Optionally, these duplicate records can be saved to a separate output for analysis.

Let's assume there is a polygon layer 'poly_test' with several duplicates, see image below.

input1

After applying the algorithm with this settings, where "id" field was used as Fields to match duplicates by

window1

The following output will appear

output1


If there is a need to delete duplicates based on its data "poorness", proceed with the following workflow.

Let's assume there is a polygon layer 'poly_test' with several duplicates, see image below.

input2

Step 1. Create a field "Quality" using the following expression:

array_count(array("Data1", "Data2", "Data3"), '')

step_1

Step 2. Apply the "Extract by expression" with the following expression:

"Quality" = minimum("Quality", group_by:="id")

window2

step_2

Step 3. And finally apply the "Delete duplicates by attribute" algorithm (with "id" field as Fields to match duplicates by) and get the output like this:

step_3

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.