Deleting geometries in feature layer based on unique field values

Question

I have a building feature layer for a county. Some parcels have more than one building, so the buildings in these parcels all have the same TMS number in the "TMS" field. I only want my feature layer to have the largest building in each parcel and delete out the smaller ones, but I am a bit stuck on how to do this. I currently have:

def unique_values(table , field):
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        return sorted({row[0] for row in cursor})

myValues = unique_values(buildings, 'TMS')

len = len(myValues)

updateRows = arcpy.UpdateCursor(buildings)

for i in range(len):
    for row in updateRows:
        if (row.GetValue("TMS") == myValues[i]):

This loops through all of the rows, but I am not sure how to accomplish my goal from here. There is a field that has the area of each building we'll call "Area".

Answer 1

This is how I might tackle it as a first go using a dictionary to keep track of the parcels and building sizes.

parcels = {}
with arcpy.da.SearchCursor(buildings, ["PARCELID","BUILDINGID","BUILDINGAREA"]) as sc:
    for row in sc:
        if row[0] in parcels:
             if parcels[row[0]][0] < row[2]:
                 parcels[row[0]] = [row[1],row[0]]
        else:
             parcels[row[0]] = [row[1],row[0]]

This creates a dictionary with parcel id as the unique key and the building id and area as a list object. Should give you the largest building by area.

Answer 2

Vince makes several good points. First, stop using the legacy/original Cursor - ArcGIS Pro and start using the newer (not really new as they have been around since 10.1 but still newer than original) Data Access module - ArcGIS Pro cursors. The Data Access module cursors are both more performant and also more idiomatic in syntax.

Another solid point Vince makes is that modifying datasets in place carries the risk of dataset corruption, if not data corruption itself, when the operation is interrupted for some reason before completion. There is a reason the vast majority of ArcGIS geoprocessing tools create new datasets instead of modifying a dataset in place. The new dataset can either be created as the existing dataset is processed, or the existing dataset can be copied into memory or temp space and then the copied dataset can be processed in-place.

---

Moving on to actual code, there are numerous ways to address this type of situation ranging from sorting data to indexing data. In all of the examples below, a new in-memory table is created instead of modifying an existing table in-place. You can export the in-memory table if you want to keep it.

When working with geodatabases (enterprise, file, or mobile), I tend to rely on using SQL to sort the data before processing it, but one can use a Python dictionary approach as well.

table = # path to feature class or table, or name of layer or tableview
group_field = "TMS"
sort_field = "Area"

Approach 1: Create new dataset and populate it while processing existing dataset

# Create new feature class or table in memory
field_names = [field.name for field in arcpy.Describe(table).fields]
if arcpy.Describe(table).dataType in ("Table", "TableView"):
    tmp_table = arcpy.management.CreateTable("memory", "tmpTable", template=table)
elif arcpy.Describe(table).dataType in ("FeatureLayer", "Featureclass"):
    tmp_table = arcpy.management.CreateFeatureclass("memory", "tmpFeatureclass", template=table)
    shape_field = arcpy.Describe(table).shapeFieldName
    field_names[field_names.index(shape_field)] = "Shape@"

Example 1a: Using SQL to sort data and populate new dataset while processing existing dataset

# Create the SQL statement to order the dataset
sql_orderby = f"ORDER BY {group_field}, {sort_field} DESC"

# Set up cursors to loop over sorted records and insert into new in-memory table 
with arcpy.da.SearchCursor(table, field_names, sql_clause=(None,sql_orderby)) as scur:
    grp_idx = scur.fields.index(group_field)
    with arcpy.da.InsertCursor(tmp_table, field_names) as icur:
        prev_row = next(scur)
        icur.insertRow(prev_row)
        for row in scur:
            if row[grp_idx] != prev_row[grp_idx]:
                icur.insertRow(row)
                prev_row = row

Example 1b: Using dictionary to index data and populate new datatset while processing existing dataset

from collections import defaultdict
max_value = defaultdict(float)
with arcpy.da.SearchCursor(table, field_names, sql_clause=(None,sql_orderby)) as scur:
    grp_idx = scur.fields.index(group_field)
    srt_idx = scur.fields.index(sort_field)
    for row in scur:
        max_value[row[grp_idx]] = max(row[srt_idx], max_value[row[grp_idx]])
    scur.reset()
    with arcpy.da.InsertCursor(tmp_table, field_names) as icur:
        for row in scur:
            if max_value[row[grp_idx]] == row[srt_idx]:
                icur.insertRow(row)

Appraoch 2: Copy dataset to memory or temp and process copy in-place

# Copy feature class or table into memory for processing
if arcpy.Describe(table).dataType in ("Table", "TableView"):
    tmp_table = arcpy.management.CopyRows(table, "tmpTable")
elif arcpy.Describe(table).dataType in ("FeatureLayer", "Featureclass"):
    tmp_table = arcpy.management.CopyFeatures(table, "memory/tmpFeatureclass")

Example 2a: Copying new table to memory and use SQL to sort and process copy in-place

# Create the SQL statement to order the dataset
sql_orderby = f"ORDER BY {group_field}, {sort_field} DESC"

# Set up cursors to identify records to delete from in-memory table 
with arcpy.da.UpdateCursor(tmp_table, "*", sql_clause=(None,sql_orderby)) as ucur:
    grp_idx = ucur.fields.index(group_field)
    prev_row = next(ucur)
    for row in ucur:
        if row[grp_idx] == prev_row[grp_idx]:
            ucur.deleteRow()
        else:
            prev_row = row

Example 2b: Using dictionary to index data and process copy in-place

from collections import defaultdict
max_value = defaultdict(float)
with arcpy.da.UpdateCursor(tmp_table, "*", sql_clause=(None,sql_orderby)) as ucur:
    grp_idx = ucur.fields.index(group_field)
    srt_idx = scur.fields.index(sort_field)
    for row in ucur:
        max_value[row[grp_idx]] = max(row[srt_idx], max_value[row[grp_idx]])
    ucur.reset()
    for row in ucur:
        if max_value[row[grp_idx]] != row[srt_idx]:
            ucur.deleteRow()

In the code samples above, the SQL and dictionary approaches handle multiple records with identical maximum values differently. For the SQL approach, only one record ends up in the results where the dictionary approach has all the records with the same maximum value in the results.