topic Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements? in Python Questions

Sort UpdateCursor with Multiple SQL ORDER BY Statements?

VinceE — Thu, 04 Nov 2021 00:40:55 GMT

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!

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

VinceE — Thu, 04 Nov 2021 13:50:57 GMT

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

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

DominicRobergeIADOT — Thu, 04 Nov 2021 14:05:17 GMT

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

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

JoshuaBixby — Thu, 04 Nov 2021 14:16:51 GMT

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.

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

VinceE — Thu, 04 Nov 2021 14:44:30 GMT

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.

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

DominicRobergeIADOT — Thu, 04 Nov 2021 14:43:18 GMT

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

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

JoshuaBixby — Thu, 04 Nov 2021 14:59:31 GMT

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?

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

DominicRobergeIADOT — Thu, 04 Nov 2021 15:00:49 GMT

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

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

JoshuaBixby — Thu, 04 Nov 2021 15:03:18 GMT

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

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

DominicRobergeIADOT — Thu, 04 Nov 2021 15:09:27 GMT

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

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

VinceE — Thu, 04 Nov 2021 19:29:46 GMT

This is great, thanks for sharing this. For the fun of it, I rewrote to avoid using the nested cursors. I think a lot of this is a matter of style/opinion, but this separates the reading and editing from the writing, and also only sorts using the lambda function, rather than once with the SQL query and once with the lambda.

It's basically the same as yours, it just creates a dictionary of updated values with the OID as the key, which enables writing in the correct order. This only uses two cursors (I believe you are recreating an UpdateCursor with every SearchCursor loop--I don't know how expensive that is). However, this version does have to create the intermediate dictionary.

While I still think using the SQL query could be cleaner/easier to read, I assume it is slower because it forces the user to add and calculate a separate coordinate field (unless they have a spatially enabled geodatabase). Because the spirit of the question was specifically about using the SQL query, I am going to leave that as marked correct for posterity, but thanks all the input!

from string import ascii_uppercase from arcpy.da import SearchCursor, UpdateCursor fc = r'PATH' # Ensure OID and SHAPE are index 0 and 1. fld = [f.name for f in ap.ListFields(fc) if f.type not in ['Geometry', 'OID']] all_fld = [ap.da.Describe(fc)['OIDFieldName'], 'SHAPE@'] + fld # ['OBJECTID', 'SHAPE@', 'LBL', 'XCOOR', 'YCOOR', 'GRP'] # Indices of fields, used to call specific values in cursor rows. grp_idx = all_fld.index('GRP') lbl_idx = all_fld.index('LBL') # SQL WHERE: where = f"""GRP IS NOT NULL AND TRIM(BOTH ' ' FROM GRP) <> ''""" # Use Python sorted w/ lambda funct; update the given row; store in dict w/ OID key. # Sort by GROUP, then DESC x-coor. row_dict = {} with SearchCursor(fc, all_fld, where) as scurs: scurs_sort = sorted(scurs, key=lambda x: (x[grp_idx], -x[1].centroid.X)) for row in scurs_sort: row = list(row) # Can't update SCurs tuple, unlike UCurs list current_group = row[grp_idx] try: if current_group != previous_group: letter_index = 0 except NameError: letter_index = 0 row[lbl_idx] = ascii_uppercase[letter_index] row_dict[row[0]] = row[1:] letter_index += 1 previous_group = current_group # Write the new row back to the same FC, using the OID as key again. with UpdateCursor(fc, all_fld, where) as ucurs: for oid, *_ in ucurs: new_data = row_dict[oid] ucurs.updateRow([oid, *new_data])

Re: Sort UpdateCursor with Multiple SQL ORDER BY Statements?

VinceE — Thu, 04 Nov 2021 19:38:28 GMT

To follow up with this, I have had luck simultaneously using an UpdateCursor and an InsertCursor on the same feature class. I had a field with a list of values in it, and I needed to explode that list out so each item was in a single row with it's own point. This meant updating the current row to include only the first value in the list, and then creating new rows in the same feature class for each subsequent value in the list.

Using the Editor class enabled me to do this. I don't know exactly what makes this necessary, but I was not able to do it without the Editor.

with ap.da.Editor(outgdb): with UpdateCursor(infc, '*') as ucurs, InsertCursor(infc, '*') as icurs: