Iterate through rows, select row, select by location, update cursor

[Peter_Green]

Hello. 

Python 3.6.8, ArcGIS Pro, file geodatabase.

I have a feature class Applications which is the result of a previous script. Fields I am interested in are Status (string), Rank (integer), Area.Shape.  One of the features in Applications already has a Status = "Confirmed". It's the row with the highest Rank. Other Status values are null, "Failed", "Pending".

Below is a mixture of aspiration of the script and a proposed method although I have to admit this is stretching the limits of my current python. I am not sure this process is possible after looking a other scripts that use da.updatecursor?

I would like to -

Create Pending_Applications layer from Applications feature class where Status = "Pending"

Create Confirmed_Applications layer from Applications feature class where Status = "Confirmed". 

Iterate in order of Rank field the Pending_Applications- select the record of the current row

If the selected feature intersects the Confirmed_Applications layer update the Status field to "Defeated"

else if it does not intersect Confirmed_Applications update the Status field to "Confirmed" (if sum of Area.Shape of the Confirmed_Applications + the Area.Shape of the current row =< 8,000)

else update the Status field to "Capped Out" (if sum of Area.Shape of the Confirmed_Applications + the Area.Shape of the current row > 8,000) update the Status field to "Capped Out".

Once a row is updated would the next iteration re-read the Confirmed_Applications layer again with additional features where the Status had been updated to "Confirmed" in previous iterations?

Many tanks.

Marc

[Peter_Green]

I am updating this question, adding some python. I have what I think is a searchcursor iterating through each row of a layer, selecting the current feature, performing a select by location against another layer (which happens to be from the same feature class but with a different query).  Count the selected features in each pass. If feature count is zero then reclass a field to "Confirmed" else reclass the field to "Defeated".

I have added a print to see the feature count in the python shell at each pass. It is returning the corrent count however where featurecount == '0' the Status field is not updating to "Confirmed". It is correctly updating the Status field to "Defeated" where the else statement is true:

print ('Creating Confirmed and Pending subsets...')
##Create a Layer of all Confirmed Applications and Layer of all Pending Applications##
arcpy.MakeFeatureLayer_management (ScotWind_Applications_Scoring, "ScotWind_Applications_Pending")
arcpy.management.SelectLayerByAttribute ("ScotWind_Applications_Pending", "NEW_SELECTION", "Status = 'Pending'", None)

print ('Comparing Pending against Confirmed...')
##Select ScotWind_Applications_Confirmed by location within 5km of the selected row of ScotWind_Applications_Pending##
with arcpy.da.SearchCursor("ScotWind_Applications_Pending", ["OBJECTID"], sql_clause=(None, "ORDER BY Rank")) as cursor:
    for row in cursor:
    
        arcpy.SelectLayerByAttribute_management ("ScotWind_Applications_Pending","NEW_SELECTION", "OBJECTID = {}".format(row[0])) 

        arcpy.MakeFeatureLayer_management (ScotWind_Applications_Scoring, "ScotWind_Applications_Confirmed")
        arcpy.management.SelectLayerByAttribute ("ScotWind_Applications_Confirmed", "NEW_SELECTION", "Status = 'Confirmed'", None)
        
        arcpy.management.SelectLayerByLocation("ScotWind_Applications_Pending", "INTERSECT", "ScotWind_Applications_Confirmed", "5 Kilometers", "SUBSET_SELECTION", "NOT_INVERT")

        featurecount = int(arcpy.GetCount_management("ScotWind_Applications_Pending").getOutput(0))

        print (featurecount)

        codeblock5 = """def Reclass(Status):
            if featurecount ==  '0':
                return 'Confirmed'
            else:
                return 'Defeated'"""
                
        arcpy.management.CalculateField("ScotWind_Applications_Pending", "Status", "Reclass(!Status!)", "PYTHON3", codeblock5)                   


endtime = time.clock()
print ("Processing Complete in " + str(endtime-starttime) + "seconds.")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Peter_Green]

The results of the print (featurecount) are correct. Could there be in issue where featurecount = 0 impacting on the codeblock here as those row's Status field are not update. Only those row's Status field in the else statement.

codeblock5 = """def Reclass(Status):
            if featurecount ==  0:
                return 'Confirmed'
            else:
                return 'Defeated'"""‍‍‍‍‍

   I also changed the query

codeblock5 = """def Reclass(Status):
            if featurecount >  0:
                return 'Defeated'
            else:
                return 'Confirmed'"""‍‍‍‍‍‍‍‍‍‍

   and still get same result. Only where featurecount > 0 do the row's Status field update. Here is the Python Shell providing what I think is the correct getcount:

Creating Confirmed and Pending subsets...
Comparing Pending against Confirmed...
0
0
0
0
0
1
0
0
0
0
Processing Complete in 315.59900619999996seconds.
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Peter_Green]

Some progress. The reclass of the Status field now works if the getcount = 0. I swapped the input and target realising (I think) that carrying out a selection on the currently selected row was not the intention. I am now selecting by location the ScotWind_Applications_Confirmed within 5km of the selected feature in the searchcursor. However the results suggest that I need to re-think how I get the searchcursor to re-read the ScotWind_Applications_Confirmed layer each time a feature's Status field is update to 'Confirmed'. Update code:

print ('Creating Confirmed and Pending subsets...')
##Create a Layer of all Pending Applications ##
arcpy.MakeFeatureLayer_management (ScotWind_Applications_Scoring, "ScotWind_Applications_Pending")
arcpy.management.SelectLayerByAttribute ("ScotWind_Applications_Pending", "NEW_SELECTION", "Status = 'Pending'", None)

print ('Comparing Pending against Confirmed...')
##Select ScotWind_Applications_Confirmed by location within 5km of the selected row of ScotWind_Applications_Pending##
with arcpy.da.SearchCursor("ScotWind_Applications_Pending", ["OBJECTID"], sql_clause=(None, "ORDER BY Rank")) as cursor:
    for row in cursor:
        ##Select the current row in ScotWind_Applications_Pending##
        arcpy.SelectLayerByAttribute_management ("ScotWind_Applications_Pending","NEW_SELECTION", "OBJECTID = {}".format(row[0])) 
        ##Create a Layer of all Confirmed Applications which is updated on each iteration##
        arcpy.MakeFeatureLayer_management (ScotWind_Applications_Scoring, "ScotWind_Applications_Confirmed")
        arcpy.management.SelectLayerByAttribute ("ScotWind_Applications_Confirmed", "NEW_SELECTION", "Status = 'Confirmed'", None)

        ##Select all Confirmed Applications on each iteration that are within 5km of the 
        arcpy.management.SelectLayerByLocation("ScotWind_Applications_Confirmed", "INTERSECT", "ScotWind_Applications_Pending", "5 Kilometers", "SUBSET_SELECTION", "NOT_INVERT")

        featurecount = int(arcpy.GetCount_management("ScotWind_Applications_Confirmed").getOutput(0))

        print (featurecount)

        codeblock5 = """def Reclass(Status):
            if featurecount== 0:
                return 'Confirmed'
            else:
                return 'Defeated'"""
                
        arcpy.management.CalculateField("ScotWind_Applications_Pending", "Status", "Reclass(!Status!)", "PYTHON3", codeblock5) ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Peter_Green]

Some success. I am now iterating through rows of a layer with a selection in the correct descending Rank and my other layer selection is correctly nested meaning a refresh of the select by attribute of that layer prior to the SelectLayerByLocation:

print ('Creating Confirmed and Pending subsets...')
##Create a Layer of all Pending Applications ##
arcpy.MakeFeatureLayer_management (ScotWind_Applications_Scoring, "ScotWind_Applications_Pending")
arcpy.management.SelectLayerByAttribute ("ScotWind_Applications_Pending", "NEW_SELECTION", "Status = 'Pending'", None)

print ('Comparing Pending against Confirmed...')
##Select ScotWind_Applications_Confirmed by location within 5km of the selected row of ScotWind_Applications_Pending##


with arcpy.da.SearchCursor("ScotWind_Applications_Pending", ["OBJECTID", "ScotWind_Applications_SMPJoin_AppID", "Rank", "SHAPE@AREA"], sql_clause=(None, "ORDER BY Rank DESC")) as cursor:
    for row in cursor:
        ##Select the current row in ScotWind_Applications_Pending##
        arcpy.SelectLayerByAttribute_management ("ScotWind_Applications_Pending","NEW_SELECTION", "OBJECTID = {}".format(row[0])) 
        ##Create a Layer of all Confirmed Applications which is updated on each iteration##
        arcpy.MakeFeatureLayer_management (ScotWind_Applications_Scoring, "ScotWind_Applications_Confirmed")
        arcpy.management.SelectLayerByAttribute ("ScotWind_Applications_Confirmed", "NEW_SELECTION", "Status = 'Confirmed'", None)

        ##Select all Confirmed Applications on each iteration that are within 5km of the 
        arcpy.management.SelectLayerByLocation("ScotWind_Applications_Confirmed", "INTERSECT", "ScotWind_Applications_Pending", "5 Kilometers", "SUBSET_SELECTION", "NOT_INVERT")

        featurecount = int(arcpy.GetCount_management("ScotWind_Applications_Confirmed").getOutput(0))
        
        print (u'{0}, {1}, {2}, {3}'.format(row[0], row[1], row[2], row[3]))

        print (featurecount)
        
        codeblock5 = """def Reclass(Status):
            if featurecount == 0:
                return 'Confirmed'
            else:
                return 'Defeated'"""
                
        arcpy.management.CalculateField("ScotWind_Applications_Pending", "Status", "Reclass(!Status!)", "PYTHON3", codeblock5)

        arcpy.Delete_management("ScotWind_Applications_Confirmed")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

   I would now like to get the sum of the SHAPE@AREA of ScotWind_Applications_Confirmed and the current  SHAPE@AREA of the current row of ScotWind_Applications_Pending in each pass of searchcursor in order to write a pre-condition. If the sum of both is > a numerical maximum, say 8,900,  update Status to "Capped Out" else carry out the codeblock 5 and it's calculatefield.

[Peter_Green · Accepted solution]

I now have a completed script. The following runs successfully from Python Shell. Although I do get errors when running the Script from a toolbox in ArcGIS Pro. I will post a separate question if I can't fix it.

It's been a real challenge to my python skills but really enjoyable. The only other thing I'd say is it has been void of comments and are there better ways of tagging python questions? I realise my initial post was a "how do I pythonise this logic and set of  processes?" then started to post a python solution. Is it better to come in with some code from the off?....

print ('Comparing Pending against Confirmed...')
##Select ScotWind_Applications_Confirmed by location within 5km of the selected row of ScotWind_Applications_Pending##
with arcpy.da.SearchCursor("ScotWind_Applications_Pending", ["OBJECTID", "ScotWind_Applications_SMPJoin_AppID", "Rank", "SHAPE@AREA"], sql_clause=(None, "ORDER BY Rank DESC")) as cursor:
    for row in cursor:
        ##Select the current row in ScotWind_Applications_Pending##
        arcpy.SelectLayerByAttribute_management ("ScotWind_Applications_Pending","NEW_SELECTION", "OBJECTID = {}".format(row[0]))
        
        ##Create a Layer of all Confirmed Applications which is updated on each iteration##
        arcpy.MakeFeatureLayer_management (ScotWind_Applications_Scoring, "ScotWind_Applications_Confirmed")
        arcpy.management.SelectLayerByAttribute ("ScotWind_Applications_Confirmed", "NEW_SELECTION", "Status = 'Confirmed'", None)

        area_sum = 0
        with arcpy.da.SearchCursor("ScotWind_Applications_Confirmed", ["SHAPE@AREA"]) as cursor2:
           for row2 in cursor2:
                area_sum = area_sum + row2[0]
                                                     
        print (area_sum)

        universal_sum = area_sum + row[3]
        
        ##Select all Confirmed Applications on each iteration that are within 5km of the 
        arcpy.management.SelectLayerByLocation("ScotWind_Applications_Confirmed", "INTERSECT", "ScotWind_Applications_Pending", "5 Kilometers", "SUBSET_SELECTION", "NOT_INVERT")

        featurecount = int(arcpy.GetCount_management("ScotWind_Applications_Confirmed").getOutput(0))

        print (u'{0}, {1}, {2}, {3}'.format(row[0], row[1], row[2], row[3]))

        print (featurecount)

        print (universal_sum)
        
        codeblock5 = """def Reclass(Status):
            if universal_sum > 4500000000:
                return 'Capped Out'
            elif featurecount== 0:
                return 'Confirmed'
            else:
                return 'Defeated'"""
                
        arcpy.management.CalculateField("ScotWind_Applications_Pending", "Status", "Reclass(!Status!)", "PYTHON3", codeblock5)

        arcpy.Delete_management("ScotWind_Applications_Confirmed")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍