arcgis10 filter dataset

Question

Using ArcGIS 10 SP-4

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…

Site_ID-----Site_Type
123--------------A
123--------------B
123--------------C
555--------------B
555--------------B
555--------------C

Into this…

Site_ID-----Site_Type
123--------------A
555--------------doesn’t matter

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

Answer 1

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:
    idswitha.append(itemwitha.getValue(field1))

#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':
            rowswithid.deleteRow(rowwithid)

    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:
    idsnoa.append(itemnoa.getValue(field1))

#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:
        rowcountlist.append(rownoaid.getValue(field3))
    #Get count of items in list
    rowcount = len(rowcountlist)
    count = 0
    rowdel = rowsnoaid.next()
    while count < rowcount:
        rowsnoaid.deleteRow(rowdel)
        rowdel = rownoaid.next()
        count +=1

    del rowsnoaid

---- 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. ---- Edit 2 - Remove index call from UpdateCursor as this is not supported. Change to next() method.

Answer 2

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")

Answer 3

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.