51

I have a point shapefile with thousands of points. It has an "ID" code field that is supposed to be unique. Every now and then the data entry clerk wrongly type the "ID" creating duplicates. Right now I'm manually scrolling the field to find the duplicate.

Is there another way to do this using the Search Query Builder?

0

9 Answers 9

65

Another graphical, dynamic and most importantly simple way to detect duplicate attributes: use QGIS's expression builder.

Highlight duplicates in attribute table:

Suppose FieldWithDuplicates is the attribute containing duplicates ( TreeID in the screenshot below). Enable Conditional Formatting (see red arrow below) with the following condition:

count(1,"FieldWithDuplicates") > 1

To group all duplicates at the top, right click the column, select Sort
Enter the above expression without the >1, and deselect Sort ascending.

duplicate attributes highlighted in QGIS attribute table

Highlight features with duplicate attributes on canvas:

You could add a new symbol or label with the filter set to the above condition.

And of course you can enable a data derived override based on the the same.

For example, if you want to highlight labels for features with a duplicate attribute, you can set it to draw a label background with the following override:

count(1,"FieldWithDuplicates") > 1

to achieve something like the following

duplicate attribute labels highlighted in QGIS canvas

In both situations, of course, once you either delete or change the duplicate attributes, the formatting/styling updates instantly.

5
  • 2
    This is by far the most legit answer here. I just wanted to add that the expression can be used directly with the standard query tool as well.
    – maxwhere
    Jul 4, 2019 at 9:50
  • @maxwhere , do you mean in the Query Builder used for filtering layers? I can't seem to get it to actually filter the results in Q 3.4 or 3.8, although I'm surprised it didn't throw up an error like it used to in Q 2.x.
    – she_weeds
    Jul 15, 2019 at 8:47
  • As suggested by @caiohamamura, the expression can be slightly simplified to: count(1,"FieldWithDuplicates") > 1
    – onietosi
    Jul 15, 2020 at 15:13
  • What is the use of "1" in the first argument? According to QGIS documentation, it is the expression which is field to aggregate. Why do we put "1"?
    – panoet
    Feb 27, 2022 at 19:13
  • @panoet This is because it doesn't matter what field we aggregate for count() here, the important thing is what attribute it is grouped by (i.e. the attribute that we want to find duplicates in). In this case there is no difference if it is counting TreeID (for the five features above - T1019; T1020; T1021 + T1021; T1021 + T1021; T1023), or the value 1 (1; 1; 1+1; 1+1; 1), the count of values is still 1; 1; 2; 2; 1
    – she_weeds
    Dec 22, 2022 at 9:47
17

Use Group Stats plugin and set the ID as a field classification. You can see how many times each value has been entered in 'count' column.

15

This is a good question onto which I just stumbled myself. I don't like any of the answers which have been given so far. I have a valid dataset with unique IDs that are non sequential and non integer. The problem is that the dataset contains single geometries but some boundaries are multi geometries in nature. My task is to identify and union these geometries.

I recommend to use the DB Manager and SQL for this kind of work. The DB Manager is now part of QGIS. You need to export your data into either PostGIS or a SpatiaLite dataset. SpatiaLite should be the file based data format of choice anyways.

Now you can use count(), group by, and order by as you like and should be able to solve this and other issues rather quickly.

enter image description here

1
  • 1
    Of all the solutions this is the one that worked best for me as my data set was very large.
    – Ian Moffit
    Feb 16, 2023 at 13:23
14

A quick (although inelegant) way to do this is to go into Layer properties, select Style - Categorized using the column that you're interested in. Apply this, then right click on the layer in the layers window and check the Show Feature Count checkbox. Then expand the layer in the layers window and you can immediately see how many times each value has been entered.

12

I'm wondering why didn't anybody recommend to use only the Select By Expression tool with the query:

COUNT(1, "attribute")>1

Then all features with repeated attribute will be selected, you can then filter, save the selection or do whatever you need to.

8

If the IDs are consecutive, I would add a new temporary column with unique values like @Ship.shp suggested and then use the query builder to search for ID != uniqueID.

That would return the duplicates directly. After fixing the original IDs, remove the extra column or repeat the whole process as needed — it is not clear what kind of pattern your IDs must match. If they just need to be unique, note the last value first and you can then edit the bad IDs in one iteration, just bumping the number as you go.

5

Here is my script for removing features with same ID's. It takes first feature with more than one index attribute and writes it to new feature class.

#Definition of inputs and outputs
# Written by: Gregor Skrt 
#==================================
##[Example scripts]=group
##input=vector
##unique_field=field input
##output=output vector

#Algorithm body
#==================================
from qgis.core import *
from PyQt4.QtCore import *
from processing.core.VectorWriter import VectorWriter

# "input" contains the location of the selected layer.
# We get the actual object, so we can get its bounds
layer = processing.getobject(input)
provider = layer.dataProvider()
fields = provider.fields()
writer = VectorWriter(output, None, fields, provider.geometryType(), layer.crs() )

inFeat = QgsFeature()
outFeat = QgsFeature()
inGeom = QgsGeometry()
nElement = 0
values = {}

value_field_index = layer.fieldNameIndex(unique_field)

feats = processing.getfeatures(layer)
nFeat = len(feats)

for inFeat in feats:
    progress.setPercentage(int((100 * nElement)/nFeat))
    nElement += 1
    inGeom = inFeat.geometry()
    attrs = inFeat.attributes()
    value = attrs[value_field_index]
    
    if value not in values:
    #to ne vem ce bo drzalo ???
        values[value]=[]
    outFeat.setGeometry(inGeom)
    outFeat.setAttributes(attrs)
    writer.addFeature(outFeat)
del writer
1
  • 1
    to je dober komentar: "#to ne vem ce bo drzalo ???"
    – Taras
    Nov 29, 2021 at 6:09
3

The answer by caiohamamura solved a related problem: After pasting in features from another layer, the fid field (supposed to be unique) had some duplicates. I was not allowed to save the layer!

In the attribute table, at the bottom left of the page in the filter dropdown menu, I found the Advanced Filter (Expression) and entered:

Count(1, "fid")>1

What I would like to add to the topic is this: when editing the fid values, you have to hit "apply" in the lower right hand corner of the attribute table window for the filter to update. Then your list gets whittled down as you go, showing only remaining duplicates.

3

You can use the split by attribute and end up with a separate table for each value.

I like Rayo's suggestion though, except statist doesn't quite work like I thought it did.
It does give a count of unique values but doesn't help with what those values are.
Another software might add a count field and allow you to export it to CSV or some spreadsheet format.

statst

My suggestion for the split layer by attribute is in the vector management tools

management too

split

Split your data on any 1 field and you will have your counts. Way more inelegant than ship.ship's solution

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.