da.SearchCursor: Nested cursor will not 'reset'

369
7
Jump to solution
01-13-2022 11:52 AM
ZacharyHart
Regular Contributor

To be fair, I don't know if this actually qualifies as a 'nested cursor' but it is a cursor calling up a function which has a cursor in it.

For starters, here is my function: it simply assigns a new identifier based on the points spatial location:

 

 

def SpatialRenumberPoints(InLayer, StartNumber):
    arcpy.management.AddXY(InLayer)##Could verify if exists first##
    arcpy.AddField_management(InLayer, "NewID", 'LONG')##Could verify if exists first##
    fields = ['OID@','POINT_X','POINT_Y','NewID']
    sqlOrder = "ORDER BY {0} DESC, {1} ASC".format(fields[2], fields[1])
    with arcpy.da.UpdateCursor(InLayer, fields,sql_clause = (None, sqlOrder)) as cursor:
        for row in cursor:
            row[3]= StartNumber
            StartNumber = StartNumber + 1
            cursor.updateRow(row)
    del cursor, row   

 

 

When calling this function up in the Python Window, the next time it was run, the function would continue where it's last highest value left off. This was fixed by deleting out the cursor at the end of the function. Life is/was good.

Now, I realized I had a use case where I wanted to run the same tool against a dataset which has groups of points (identified by an attribute) and iterate through each group. No problem I thought, I'll just use use 'GROUP BY' for the SQL Clause in a search cursor. Like so:

 

 

with arcpy.da.SearchCursor('PlotCopy',['CLUSTER_ID'],sql_clause=(None, "GROUP BY CLUSTER_ID")) as cur:
    for row in cur:
        SpatialRenumberPoints('PlotCopy',1)
        print(row)

 

 

The only problem is that here again, the cursor isn't resetting or starting from '1' for each iteration.

ZacharyHart_0-1642103519681.png

Perhaps I'm using GROUP BY inappropriately and should use my search cursor to setup a selection instead?

Thanks to anyone who can help!!

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

In your second code snippet, the overall search cursor and the update cursor in SpatialRenumberPoints are independent of each other.  The reason the numbering isn't resetting is that the numbering is starting at 1 and going until the end for all records each time you call SpatialRenumberPoints.

What might work is if you pass the CLUSTER_ID value through to SpatialRenumberPoints as a new argument, and then add a SQL WHERE clause to the update cursor in SpatialRenumberPoints to filter records to only that CLUSTER_ID.

View solution in original post

7 Replies
DanPatterson
MVP Esteemed Contributor

I like selections since you can use them then use the same code to set the selection to none, before carrying on with the next selection, I just don't skip the middle step of setting the selection to none after the initial selection and before the next (theoretically it shouldn't matter, but I like to make sure I am starting with a clean slate each time and not relying on underlying magic)


... sort of retired...
CMV_Erik
Occasional Contributor

In this case, you could get away without a parameter; just set StartNumber  = 1 as the first line of the function. If you want to retain the ability to change with a parameter, you could declare a new local variable inside the function and use it instead, like below. That way, every new function call should automatically start with 1 because the value is never shared outside the function.  


def
SpatialRenumberPoints(InLayer, paramStartNumber):
StartNumber = paramStartNumber
arcpy.management.AddXY(InLayer)##Could verify if exists first##
arcpy.AddField_management(InLayer, "NewID", 'LONG')##Could verify if exists first##
fields = ['OID@','POINT_X','POINT_Y','NewID']
sqlOrder = "ORDER BY {0} DESC, {1} ASC".format(fields[2], fields[1])
with arcpy.da.UpdateCursor(InLayer, fields,sql_clause = (None, sqlOrder)) as cursor:
for row in cursor:
row[3]= StartNumber
StartNumber = StartNumber + 1
cursor.updateRow(row)
del cursor, row

 

ZacharyHart
Regular Contributor

I clicked too soon. Declaring the variable within the function did not fix the issue. I think I need to consider something like what @JoshuaBixby  suggested.

 

0 Kudos
CMV_Erik
Occasional Contributor

I didn't look closely enough at the code the first time, but I agree. @JoshuaBixby nailed it. 

CMV_Erik
Occasional Contributor

To try to make up for my oversight, an alternate approach: 

If there's only one dataset involved, this could be also done with a single cursor & no functions if you sort the update cursor by CLUSTERID. More work per record, but fewer geodatabase requests. Something like: 

 

InLayer = 'PlotCopy'
arcpy.management.AddXY(InLayer)##Could verify if exists first##
arcpy.AddField_management(InLayer, "NewID", 'LONG')##Could verify if exists first##
fields = ['OID@','POINT_X','POINT_Y','NewID', 'CLUSTERID']
sqlOrder = "ORDER BY {0} ASC, {1} DESC, {2] ASC".format(fields[4], fields[2], fields[1])

last_cluster_id = 0
StartNumber = 1
with arcpy.da.UpdateCursor(InLayer, fields, sql_clause = (None, sqlOrder)) as cursor_sorted_by_cluster_id:
for row in cursor_sorted_by_cluster_id:
# test if the clusterid was the same as the last one
if row[4] == last_cluster_id: # still the same CLUSTERID group
StartNumber += 1 # increment the counter
else: # starting a new CLUSTERID group - reset
last_cluster_id = row[4] # change the value
StartNumber = 1 # reset the counter
row[3] = StartNumber
cursor_sorted_by_cluster_id.updateRow(row) 

 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

In your second code snippet, the overall search cursor and the update cursor in SpatialRenumberPoints are independent of each other.  The reason the numbering isn't resetting is that the numbering is starting at 1 and going until the end for all records each time you call SpatialRenumberPoints.

What might work is if you pass the CLUSTER_ID value through to SpatialRenumberPoints as a new argument, and then add a SQL WHERE clause to the update cursor in SpatialRenumberPoints to filter records to only that CLUSTER_ID.

ZacharyHart
Regular Contributor

@JoshuaBixby @CMV_Erik Sorry for the lag in response here, we've been swamped with project work so dev efforts get shelved. 


@JoshuaBixby wrote:

 The reason the numbering isn't resetting is that the numbering is starting at 1 and going until the end for all records each time you call SpatialRenumberPoints.


That is the exact insight I needed. To be real honest, I'm pretty sure my lack of this comprehension has confounded other things in the past too. I tried Erik's solution but it didn't work for, I presume, the exact reason you point out.

My challenge is that I want to keep this function stand alone to be used in other scripts, so I'll try to make an option argument like 'GroupField=None' or something. Nope that's a dead end...doh!

UPDATE:

Here are Josh's suggestions integrated and the code is now working!

with arcpy.da.SearchCursor('PlotCopy',['CLUSTER_ID'],sql_clause=(None, "GROUP BY CLUSTER_ID")) as cur:
    for row in cur:
        SpatialRenumberPoints('PlotCopy',1,'CLUSTER_ID',row[0])
        print("Updating {0}...".format(row[0]))

which calls up the function:

def SpatialRenumberPoints(InLayer, StartNumber, GroupField=None, GroupID=None):
    arcpy.management.AddXY(InLayer)##Could verify if exists first##
    arcpy.AddField_management(InLayer, "NewID", 'LONG')##Could verify if exists first##
    fields = ['OID@','POINT_X','POINT_Y','NewID']
    if GroupField is None:
        WhereClause = None
    else:
        WhereClause =""" "{0}" = '{1}' """.format(GroupField, GroupID)
        print(WhereClause)
    sqlOrder = "ORDER BY {0} DESC, {1} ASC".format(fields[2], fields[1])   
    with arcpy.da.UpdateCursor(InLayer, fields, where_clause = WhereClause, sql_clause = (None, sqlOrder)) as cursor:
        for row in cursor:
            row[3]= StartNumber
            StartNumber = StartNumber + 1
            cursor.updateRow(row)
    del cursor, row  

@JoshuaBixby  I'm guessing this looks pretty crude to you, any suggestions for improvements? Also, is the Group By in the Search Cursor even needed now? Thanks for any additional insight!

0 Kudos