I designed a workflow, that although functional, is very slow and prone to errors. I am sure there is a better approach, but I cannot wrap my head around it. The environment is ArcGIS arcpy (Python 3), the data is located in a SDE database.

The task is to fill the initially empty table (FIN TABLE), with data from the feature class (featureIN). I need to group features with the same Field1, 2, 3, and write back the received GlobalID into ProcessID.

My approach is to

  1. Dissolve based on Field1, 2, 3
  2. Use a SearchCursor on the resulted dissolved features
  3. Look up the attributes (Attr1, 2) from featureIN. Keep Last. (SearchCursor)
  4. Write the data to FIN TABLE (InsertCursor)
  5. Catch the GlobalID (SearchCursor)
  6. Write it back to featureIN (UpdateCursor)

Workflow with example data

With that workflow I manage to process 10 rows/s, which takes too long given the large dataset I have.

Is there a better approach? How can this be optimized?

EDIT: Here is the rough workflow in python:

INfc = 'featureIN.shp' 
dissTMPfc = 'DISSOLVE.shp'
dm1 = {'Field1': 1,'Field2': 2,'Field3': 3,'Attr1': 4,'Attr2': 5 } # FieldMap

#### Step 1 Dissolve based on Field1, 2, 3 ####
fieldsDIS = ['Field1', 'Field2', 'Field3']
arcpy.Dissolve_management(INfc, dissTMPfc, ';'.join(fieldsDIS), "", "MULTI_PART", "DISSOLVE_LINES")

#### Step 2 Use a SearchCursor on the resulted dissolved features ####
cursorDIS = arcpy.da.SearchCursor(dissTMPfc, fieldsDIS)
for row in cursorDIS:

    # build where clause for each row
    whereClause = ""
    i = 0
    for field in fieldsDIS:
        if i > 0: whereClause = whereClause + " AND "
        if row[i] is not None:
            whereClause = whereClause + field + " = '" + str(row[i]) + "'"
            whereClause = whereClause + field + " IS NULL"
        i = i + 1

    ### Step 3 Look up the attributes (Attr1, 2) from featureIN. Keep Last.
    cursorIN = arcpy.da.SearchCursor(INfc, fieldsIN, whereClause )
    updRow = []
    for row2 in cursorIN:
        for key in dm1.keys():

    ### Step 4 Write the data to FIN TABLE (InsertCursor)
    with arcpy.da.InsertCursor(OUTtb, fieldsOUT) as cursorTAB:
        objectid = cursorTAB.insertRow(updRow)

    ### Step 5 Catch the GlobalID (SearchCursor)
    searchFields = ['OBJECTID', 'GlobalID']
    whereClause2 = 'OBJECTID = {0}'.format(objectid)
    with arcpy.da.SearchCursor(OUTtb, searchFields, whereClause2) as searchCursor:
        for row3 in searchCursor:
            globalid = row3[searchFields.index('GlobalID')]

    ### Step 6 Write GlobalID back to featureIN (UpdateCursor)
    with arcpy.da.UpdateCursor(INfc, ['ProcessID'], whereClause) as cursorINupd:
        for row in cursorINupd:
            row[0] = globalid
  • 2
    You need to add the code so we can see exactly what you are doing. Are you using cursors inside cursors? Try using dictionaries instead – BERA Aug 28 at 9:05
  • +1 @BERA. you can also check this post on geonet to see this at work! – umbe1987 Aug 28 at 9:10
  • I added the code. I had to quickly adapt it, so it is untested. Yes, I am using cursor inside cursor. Will dictionaries not grow too large for this task? I will read through the post! Thanks! – Marcus Aug 28 at 9:50
  • You're using the variable row at multiple levels of nesting. You don't provide enough details on row counts to provide useful feedback. I once cached 3.6Gb of vertices in a dictionary using the 64-bit Background Geoprocessing ArcPy. – Vince Aug 28 at 11:15
  • @Vince Thanks for the hint with the variable. – Marcus Aug 28 at 11:24

You're making use of cursors within cursors, as well as appling sql queries to those cursor which can slow down the process exponentially. Your dissolve also appears to be unnecessary as it appears to be used to get unique IDs. I suggest making use of dictionaries to store information. This task can be completed with four cursors run one at a time.

  1. Search input table and create dictionary of field values key to attributes
  2. Update out table with dictionary and update cursor
  3. Create dictionary of field values and guids in out table with search cursor
  4. Update input table with guids with dictionary and update cursor

Example code (untested):

INfc = 'featureIN.shp'

#get last attributes in input table, store in dictionary
di = {}
with arcpy.da.SearchCursor (INfc, ['Field1', 'Field2', 'Field3', 'Attr1', 'Attr2']) as curs:
    for fld1, fld2, fld3, attr1, attr2 in curs:
        #update dict with key and attributes
        di [(fld1, fld2, fld3)] = (attr1, attr2)

#insert into out table keys, values
with arcpy.da.InsertCursor (OUTtb, ['Field1', 'Field2', 'Field3', 'Attr1', 'Attr2']) as curs:
    #iterate stored value in dict
    for (fld1, fld2, fld3), (attr1, attr2) in di.items ():
        #create and insert row
        row = [fld1, fld2, fld3, attr1, attr2]
        curs.insertRow (row)

#search cursor to get new IDs generated when inserting row
di = {}
with arcpy.da.SearchCursor (OUTtb, ['Field1', 'Field2', 'Field3', 'GlobalID']) as curs:
    fld1, fld2, fld3, guid in curs:
        #update dict with key and guid
        di [(fld1, fld2, fld3)] = guid

#update pid in table
with arcpy.da.UpdateCursor (INfc, ['Field1', 'Field2', 'Field3', 'ProcessID']) as curs:
    for fld1, fld2, fld3, pid in curs:
        #get id
        pid = di [(fld1, fld2, fld3)]
        #update row
        row = (fld1, fld2, fld3, pid)
        curs.updateRow (row)
  • Thanks. This is the solution I was looking for! I forgot to mention it in the initial task, but I will need to add the dissolve step, as I need the shape@xy. – Marcus Aug 29 at 6:59
  • @Marcus if you want to be real fancy you can average each key’s x’s and y’s for you ‘dissolved’ xy without doing an actual dissolve. – Emil Brundage Aug 29 at 13:40

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.