33

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?

10 Answers 10

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.

| improve this answer | |
29

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("FieldWithDuplicates","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 (=1) with the following override:

CASE WHEN
count("FieldWithDuplicates","FieldWithDuplicates") > 1
THEN 1
ELSE 0
END

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.

| improve this answer | |
  • 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 '19 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 '19 at 8:47
  • As suggested by @caiohamamura, the expression can be slightly simplified to: count(1,"FieldWithDuplicates") > 1 – onietosi Jul 15 at 15:13
15

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.

| improve this answer | |
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.

| improve this answer | |
11

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

| improve this answer | |
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
| improve this answer | |
3

Also you can use the split by attribute and end up with a seperate 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 other 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 an dyou will have your counts.
way more inelegant than ship.ship's solution

| improve this answer | |
2

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.

| improve this answer | |
2

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.

| improve this answer | |
0

You can use pyqgis and collections.Counter:

def findduplicates(layername, fieldname):
    from collections import Counter
    lyr = QgsProject.instance().mapLayersByName(layername)[0]
    c = Counter([f[fieldname] for f in lyr.getFeatures()]) #Count elements
    for i in c.copy(): #Delete if count <=1
        if c[i] <= 1:
            del c[i]
    print('There are {0} repeated {1}s, selecting...'.format(len(c), fieldname))
    processing.run("qgis:selectbyexpression", 
    {'INPUT':lyr.source(),
    'EXPRESSION':'"{0}" in {1}'.format(fieldname,tuple(c)),'METHOD':0}) #Select

enter image description here

| improve this answer | |

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.