Using ArcGIS Desktop 10.0 SP4.

I have a large dataset of sites that needs to be filtered down to a usable size. There is an attribute for the site_ID and another one for the site_type. There are duplicate site_ID’s in the dataset and I only need one record for each site. The preference of what gets deleted depends on the type. If there is a record with a type “A” then all other records for that site can be deleted. If a site does not have a type “A” then it doesn’t matter which records get deleted.

In a nutshell I want to turn this…


Into this…

555--------------doesn’t matter

I’ve been playing around with SQL statements without any luck. Any suggestions?

  • 2
    I'm a bit confused by the "doesn't matter" portion -- does that mean you're leaving all records, deleting all records, or somewhere in the middle?
    – Roy
    Jun 18, 2012 at 19:25
  • @Roy - I think the intent is to simply keep one feature with that ID. It does not matter if the Site_Type is B or C. Jun 18, 2012 at 22:35
  • Get Spatial is correct. If site 555 does not have an "A" type then it doesn't matter which record is retained, as long as only one record is retained.
    – babarsac
    Jun 19, 2012 at 16:50

3 Answers 3


Based on the pseudocode from @MichaelTodd, and the what you listed above, I cobbled together some code that I think will work.

In essence, it deals with the ID's that contain a type of "A" first.
1. Select those ID's
2. Select features matching those ID's.
3. Delete features where Type <> A

Next, deal with the features that don't contain a Type A. This should now only include features that were not in the first set, since those have been removed.
1. Select ID's where Type <>A
2. Step through ID's, selecting features with matching attribute
3. Count features matching each ID, then iterate through deleting features until only 1 is left.

import arcpy
arcpy.env.workspace = r"c:\Temp"
fc = "newshape.shp"

field1 = "Site_ID"
field2 = "Site_Type"
field3 = "OID"

#Setting delimiters should allow you to change source without causing problem in query strings
delimfield1 = arcpy.AddFieldDelimiters(fc,field1)
delimfield2 = arcpy.AddFieldDelimiters(fc,field2)
delimfield3 = arcpy.AddFieldDelimiters(fc,field3)

srchstring = delimfield2 + "= 'A'"

#Returns features with Site_Type = A
rowswitha = arcpy.SearchCursor(fc,srchstring,"",field1, "Site_ID A")

#Makes list of Site_ID's that have one or more feature where Site_Type = A
idswitha = []
for itemwitha in rowswitha:

#Step through list of Features, delete features in ID list where Site_ID <> A
for idwitha in idswitha:
    idstring = delimfield1 + " = " + idwitha
    rowswithid = arcpy.UpdateCursor(fc,idstring)

    for rowwithid in rowswithid:
        if rowwithid.getValue(field2)!='A':

    del rowswithid, rowwithid

#Create list of features where Site_Type not equal to A
srchstring2 = delimfield2 + " <> 'A'"
fields = field3 + "; " + field1
rowsnoa = arcpy.SearchCursor(fc,srchstring2,"",fields, "Site_ID A")

#Create list of ID's where Wite_Type <>A
idsnoa = []
for itemnoa in rowsnoa:

#Create Unique value list of IDs
setidsnoa = set(idsnoa)

#For each ID, return features, count features, then delete until deleted count leaves 1
for idnoa in idsnoa:
    idnoastring = delimfield1 + " = " + idnoa
    rowsnoaid = arcpy.UpdateCursor(fc, idnoastring)

    #Create list to return count of items in cursor
    rowcountlist = []
    for rownoaid in rowsnoaid:
    #Get count of items in list
    rowcount = len(rowcountlist)
    count = 0
    rowdel = rowsnoaid.next()
    while count < rowcount:
        rowdel = rownoaid.next()
        count +=1

    del rowsnoaid

I edited to add list item to return count for second section of the query. It is not the most efficient code, so any improvements are welcome.

I removed the index call from UpdateCursor as this is not supported. Changed to next() method.

  • Get Spatial Thanks a bunch for your help. I've adapted the script to what I need and now doing a little debugging. I seem to be getting some runtime errors "ERROR 999999: Error executing function. the input string is not a geographic or projected coordinate system". It's very strange since I'm not trying to reproject anything at all.
    – babarsac
    Jun 19, 2012 at 16:58
  • @babarsac - Let me take a look at it. Did you perhaps get a row number where it failed? Jun 19, 2012 at 17:12
  • Yep in line 39 37: srchstring2 = delimfield2 + " <> 'L'" 38: fields = field3 + "; " + field1 39: rowsnol = arcpy.SearchCursor(fc,srchstring2,fields, "Site_UID L")
    – babarsac
    Jun 19, 2012 at 17:15
  • @babarsac - I edited the code. It looks like I got sloppy with the syntax on one of the SearchCursor calls, and missed the empty string for the Spatial Reference. rowsnoa = arcpy.SearchCursor(fc,srchstring2,"",fields, "Site_ID A") Jun 19, 2012 at 17:19
  • The end is in sight. In line 53 for "rowcount = len(rowsnolid)" I'm being told that "TypeError: object of type 'Cursor' has no len()". Would this have to do with null values being returned?
    – babarsac
    Jun 19, 2012 at 17:40

This can be done with the Dissolve tool:

  • Site_ID as the Dissolve field
  • Site_type as a Statistics Field with MIN Statistic type

arcpy.Dissolve_management("in.shp","out.shp","Site_ID","Site_type MIN","MULTI_PART","DISSOLVE_LINES")


If this were SQL, I would do something like the following:

select distinct site_id, 'A'
from table 
where site_type='A' 
union all
select distinct site_id, 'B' 
from table 
where site_type!='A' and 
site_id not in (select site_id from table where site_type='A')

The query above selects unique site ids that have an A type and adds that to the set of unique site ids for site ids that do not have an A type.

I'll see how one might do that in Python and update if I can figure it out.

  • Can't figure it out using shapefiles. If this was straight SQL Server, I could make it work, but shapefiles don't seem to like subqueries. Jun 18, 2012 at 20:41
  • Why not use shp2pgsql, run the query, and then go back to a shapefile (or not)? Oct 3, 2015 at 6:29

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.