Applying a SQL expression to the search cursor is only useful for attribute queries, not spatial queries. For example, you can easily open a search cursor on all counties named "Lincoln" using a SQL expression, but finding all counties that touch or include the Mississippi River requires a different approach. To get a subset of records based on a spatial criterion, you need to use the geoprocessing tool Select Layer By Location.
A few relational databases such as SQL Server expose spatial data types that can be spatially queried with SQL. Support for these spatial types in ArcGIS is still maturing, and in this course, we will assume that the way to make a spatial query is through Select Layer By Location. Since we are not using ArcSDE, this is actually true.
Suppose you want to generate a list of all states whose boundaries touch Wyoming. As we saw in the previous section with the Select Layer By Attribute tool, the Select Layer By Location tool will return a Feature Layer containing the features that meet the query criteria. One thing we didn't mention in the previous section is that a search cursor can be opened not only on feature classes, but also on feature layers. With that in mind, here is a set of steps one might take to produce a list of Wyoming's neighbors:
- Use Select Layer By Attribute on the states feature class to create a feature layer of just Wyoming. Let's call this the Selection State layer.
- Use Select Layer By Location on the states feature class to create a feature layer of just those states that touch the Selection State layer. Let's call this the Neighbors layer.
- Open a search cursor on the Neighbors layer. The cursor will include only Wyoming and the states that touch it, because the Neighbors layer is the selection applied in step 2 above. Remember that the feature layer is just a set of records held in memory.
Below is some code that applies the above steps.
# Selects all states whose boundaries touch # a user-supplied state import arcpy # Get the US States layer, state, and state name field usaFC = r"C:\Data\USA\USA.gdb\Boundaries" state = "Wyoming" nameField = "NAME" try: whereClause = nameField + " = '" + state + "'" selectionStateLayer = arcpy.SelectLayerByAttribute_management(usaFC, 'NEW_SELECTION', whereClause) # Apply a selection to the US States layer neighborsLayer = arcpy.SelectLayerByLocation_management(usaFC, 'BOUNDARY_TOUCHES', selectionStateLayer) # Open a search cursor on the US States layer with arcpy.da.SearchCursor(neighborsLayer, (nameField)) as cursor: for row in cursor: # Print the name of all the states in the selection print (row) except: print (arcpy.GetMessages()) finally: # Clean up feature layers and cursor arcpy.Delete_management(neighborsLayer) arcpy.Delete_management(selectionStateLayer) del cursor
You can choose from many spatial operators when running SelectLayerByLocation. The code above uses "BOUNDARY_TOUCHES". Other available relationships are "INTERSECT", "WITHIN A DISTANCE" (may save you a buffering step), "CONTAINS", "CONTAINED_BY", and others.
Note that the Row object "row" returns only one field ("NAME"), which is accessed using its index position in the list of fields. Since there's only one field, that index is 0, and the syntax looks like this: row. Once you open the search cursor on your selected records, you can perform whatever action you want on them. The code above just prints the state name, but more likely you'll want to summarize or update attribute values. You'll learn how to write attribute values later in this lesson.
Cleaning up feature layers and cursors
Notice that the feature layers are deleted using the Delete tool. This is because feature layers can maintain locks on your data, preventing other applications from using the data until your script is done. arcpy is supposed to clean up feature layers at the end of the script, but it's a good idea to delete them yourself in case this doesn't happen or in case there is a crash. In the examples above, the except block will catch a crash, then the script will continue and Delete the two feature layers.
Cursors can also maintain locks on data. As mentioned earlier, the "with" statement should clean up the cursor for you automatically. However, we've found that it doesn't always, an observation that appears to be backed up by this blurb from Esri's documentation of the arcpy.da.SearchCursor class:
Search cursors also support with statements to reset iteration and aid in removal of locks. However, using a del statement to delete the object or wrapping the cursor in a function to have the cursor object go out of scope should be considered to guard against all locking cases.
One last point to note about this code that cleans up the feature layers and cursor is that it is embedded within a finally block. This is a construct that is used occasionally with try and except to define code that should be executed regardless of whether the statements in the try block run successfully. To understand the usefulness of finally, imagine if you had instead placed these cleanup statements at the end of the try block. If an error were to occur somewhere above that point in the try block -- not hard to imagine, right? -- the remainder of the try block would not be executed, leaving the feature layers and cursor in memory. A subsequent run of the script, after fixing the error, would encounter a new problem: the script would be unable to create the feature layer stored in selectionStatesLayer because it already exists. In other words, the cleanup statements would only run if the rest of the script ran successfully.
This situation is where the finally statement is especially helpful. Code in a finally block will be run regardless of whether something in the try block triggers a crash. (In the event that an error is encountered, the finally code will be executed after the except code.) Thus, as you develop your own code utilizing feature layers and/or cursors, it's a good idea to include these cleanup statements in a finally block.
Implementation of the Select Layer By Attribute/Location tools required a different syntax in earlier versions of ArcGIS. The tools didn't return a Feature Layer and instead they required you to first create the Feature Layer yourself using the Make Feature Layer tool. Let's have a look at this same example completed using that syntax:
# Selects all states whose boundaries touch # a user-supplied state import arcpy # Get the US States layer, state, and state name field usaFC = r"C:\Data\USA\USA.gdb\Boundaries" state = "Wyoming" nameField = "NAME" try: # Make a feature layer with all the US States arcpy.MakeFeatureLayer_management(usaFC, "AllStatesLayer") whereClause = nameField + " = '" + state + "'" # Make a feature layer containing only the state of interest arcpy.MakeFeatureLayer_management(usaFC, "SelectionStateLayer", whereClause) # Apply a selection to the US States layer arcpy.SelectLayerByLocation_management("AllStatesLayer","BOUNDARY_TOUCHES","SelectionStateLayer") # Open a search cursor on the US States layer with arcpy.da.SearchCursor("AllStatesLayer", (nameField)) as cursor: for row in cursor: # Print the name of all the states in the selection print (row) except: print (arcpy.GetMessages()) finally: # Clean up feature layers and cursor arcpy.Delete_management("AllStatesLayer") arcpy.Delete_management("SelectionStateLayer") del cursor
Note that the first MakeFeatureLayer statement takes the Boundaries feature class as an input and produces as an output a feature layer that throughout the rest of the script can be referred to using the name 'AllStatesLayer.' Similarly, the next statement creates another feature layer from the Boundaries feature class, this one applying a where clause to limit the included features to just Wyoming. This feature layer will go by the name 'SelectionStateLayer.' Creation of these feature layers was necessary in this older syntax because the Select Layer By Attribute/Location tools would only recognize feature layers, not feature classes, as valid inputs.
While we find this syntax to be a bit odd and less intuitive than the one shown at the beginning of this section, it still works and you may encounter it when viewing others' scripts.