Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
1  
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 '12 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. –  Get Spatial Jun 18 '12 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 '12 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:
    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.

share|improve this answer
    
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 '12 at 16:58
    
@babarsac - Let me take a look at it. Did you perhaps get a row number where it failed? –  Get Spatial Jun 19 '12 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 '12 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") –  Get Spatial Jun 19 '12 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 '12 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")

share|improve this answer
    
+1 - For a far simpler solution!! –  Get Spatial Jun 27 '12 at 9:22

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.

share|improve this answer
    
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. –  Michael Todd Jun 18 '12 at 20:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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