Sort UpdateCursor with Multiple SQL ORDER BY Statements?

[VinceE]

I am trying to loop through an existing point feature class with an UpdateCursor (DA version) and assign consecutive labels (A, B, C, D...) to a field for features that fall within the same "group," which is a numbered attribute stored in another field. Lets call them the GROUP field and the LABEL field.

I have accomplished this as follows, and it seems to work fine. It uses SQL's "ORDER BY GROUP," then increments through an alphabet string to assign the LABEL, then starts over at 'A' when the GROUP changes.

from string import ascii_uppercase
from arcpy.da import UpdateCursor

fc = r'C:\Users\vce50\Desktop\Cursors\Cursors\Cursors.gdb\Random_Points'
fields = ['GRP', 'LBL']
where = f"""GRP IS NOT NULL AND TRIM(BOTH ' ' FROM GRP) <> ''"""
sql_clause = (None, 'ORDER BY GRP ASC')

with UpdateCursor(fc, fields, where, sql_clause=sql_clause) as ucurs:
    for group, _ in ucurs:
        current_group = group
        try:
            if current_group != previous_group:
                letter_index = 0
        except NameError:
            letter_index = 0

        new_lbl = ascii_uppercase[letter_index]
        ucurs.updateRow([group, new_lbl])

        letter_index += 1
        previous_group = current_group

The problem is that I would also like to order the labels spatially (north to south, for example), as a secondary sort after the GROUP. I am using a File GDB feature class, so I think that means no spatial SQL queries. I have also tried adding a Y_COOR field (DOUBLE type), and using that as a secondary SQL ORDER BY:

sql_clause = (None, f"""ORDER BY {grp_fld} ASC; ORDER BY {y_coor} DESC""")

However, I cannot seem to get this to work. It seems like only one ORDER BY query can be passed. Is that the case?

I would prefer to do this in place with an UpdateCursor, but could also read all rows using a SearchCursor, sort with Python's sorted() and lambda function to order by multiple fields, and then write rows to a new blank feature class if the UpdateCursor won't work. Any recommendations would be appreciated!

[VinceE · Accepted solution]

Looks like this was a very clear issue of bad syntax on my part... here is sorting by three fields (all ascending, by default), by priority, in case someone else stumbles across this issue.

sql_clause = (None, f"""ORDER BY {grp_fld}, {agency_fld}, {xcoor_fld}""")

[DominicRoberge2]

Hi Vince

would this work for you. I tested it and seems like the sorted() reverse=true on the SHAPE@Y field will sort from North to South

fields = ['OBJECTID','GROUPT', 'LABELT','SHAPE@X','SHAPE@Y']
where = f"""GROUPT IS NOT NULL AND TRIM(BOTH ' ' FROM GROUPT) <> ''"""
sql_clause = (None, 'ORDER BY GROUPT DESC')

with arcpy.da.UpdateCursor(fc, fields, where, sql_clause=sql_clause) as ucurs:
    ucursSort = sorted(ucurs,key=lambda group : group[4], reverse=True)
    for group in ucursSort:
        print(group)
        current_group = group
        try:
            if current_group != previous_group:
                letter_index = 0
        except NameError:
            letter_index = 0

        new_lbl = ascii_uppercase[letter_index]
        #ucurs.updateRow([group, new_lbl])

        letter_index += 1
        previous_group = current_group

[JoshuaBixby]

Commenting out the updateRow line effectively turns the UpdateCursor into a SearchCursor.  That said, uncommenting the updateRow line won't work because the update cursor has been fully iterated by sorted so there is no current row to update.

[VinceE]

Thanks for the input! This is close to the approach I would have used had I not gotten my SQL syntax sorted out, but @JoshuaBixby's comments would be issues with the Cursor.

I think I would have had to use a SearchCursor, use sorted() with a lambda function (as you have done), and then use a separate UpdateCursor InsertCursor to write the sorted rows to a blank feature class with the same schema. Probably workable, but involves iterating over the same data more times (three total?) than necessary.

[DominicRoberge2]

how about this: Adding the UpdateCursor based on the OBJECTID of your first sorted()

fields = ['OBJECTID','GROUPT', 'LABELT','SHAPE@X','SHAPE@Y']
where = f"""GROUPT IS NOT NULL AND TRIM(BOTH ' ' FROM GROUPT) <> ''"""
print(where)
sql_clause = (None, 'ORDER BY GROUPT DESC')
i=0
with arcpy.da.UpdateCursor(fc, fields, where, sql_clause=sql_clause) as ucurs:
    ucursSort = sorted(ucurs,key=lambda group : group[4], reverse=True)
    for group in ucursSort:
        print(group)
        current_group = group
        try:
            if current_group != previous_group:
                letter_index = 0
        except NameError:
            letter_index = 0

        new_lbl = ascii_uppercase[letter_index]
        print(new_lbl)
        
        #ucursSort.updateRow(group)
        where2 = f"""OBJECTID = """+str(group[0])
        print(where2)
        with arcpy.da.UpdateCursor(fc, fields, where2) as ucursU:
            for r in ucursU:
                print(r)
                r[2] = new_lbl
                ucursU.updateRow(r)
            
        letter_index += 1
        previous_group = current_group
        print("------------------")

[JoshuaBixby]

I am guessing nesting update cursors on the same feature class will create lock issues that prevent the nested cursor from actually updating.  Have you tested this and it worked?

[DominicRoberge2]

I did tested it on a point feature class in a file geodatabase and it worked for me

[JoshuaBixby]

Interesting, I don't nest update cursors on the same dataset, but I will have to test it out sometime.

[DominicRoberge2]

we could replace the first updateCursor with SearchCursor, it's still working for me

fields = ['OBJECTID','GROUPT', 'LABELT','SHAPE@X','SHAPE@Y']
where = f"""GROUPT IS NOT NULL AND TRIM(BOTH ' ' FROM GROUPT) <> ''"""
print(where)
sql_clause = (None, 'ORDER BY GROUPT DESC')
i=0
with arcpy.da.SearchCursor(fc, fields, where, sql_clause=sql_clause) as ucurs:
    ucursSort = sorted(ucurs,key=lambda group : group[4], reverse=True)
    for group in ucursSort:
        print(group)
        current_group = group
        try:
            if current_group != previous_group:
                letter_index = 0
        except NameError:
            letter_index = 0

        new_lbl = ascii_uppercase[letter_index]
        print(new_lbl)
        
        #ucursSort.updateRow(group)
        where2 = f"""OBJECTID = """+str(group[0])
        print(where2)
        with arcpy.da.UpdateCursor(fc, fields, where2) as ucursU:
            for r in ucursU:
                print(r)
                r[2] = new_lbl
                ucursU.updateRow(r)
            
        letter_index += 1
        previous_group = current_group
        print("------------------")