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 2008 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.
Here's where you need to know a little bit about how ArcGIS works with layers and selections. Suppose you want to select all states whose boundaries touch Wyoming. In most cases you won't need to create an entirely new feature class to hold those particular states; you probably only need to maintain those particular state records in the computer's memory for a short time while you update some attribute. ArcGIS uses the concept of feature layers to represent in-memory sets of records from a feature class.
The Make Feature Layer tool creates a feature layer from some or all of the records in a feature class. You can apply a SQL expression when you run Make Feature Layer to narrow down the records included in the feature layer based on attributes. You can subsequently use Select Layer By Location to narrow down the records in the feature layer based on some spatial criteria.
Opening a search cursor on Wyoming and all states bordering it would take four steps:
- Use Make Feature Layer to make a feature layer of all US States. Let's call this the All States layer.
- Use Make Feature Layer to create a second feature layer of just Wyoming. (To get Wyoming alone, you would apply an SQL expression when making the feature layer.) Let's call this the Selection State layer.
- Use Select Layer By Location to narrow down the All States layer (the layer you created in Step 1) to just those states that touch the Selection State layer.
- Open a search cursor on the All States layer. The cursor will include only Wyoming and the states that touch it because there is a selection applied to the All States layer. Remember that the feature layer is just a set of records held in memory. Even if you called it the All States layer, it no longer includes all states once you apply a selection.
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 usaLayer = "D:\Data\USA\USA.gdb\Boundaries" state = "Wyoming" nameField = "NAME" try: # Make a feature layer with all the US States arcpy.MakeFeatureLayer_management(usaLayer, "AllStatesLayer") # Make a feature layer containing only the state of interest arcpy.MakeFeatureLayer_management(usaLayer, "SelectionStateLayer", '"' + str(nameField) + '" =' + "'" + str(state) + "'") # 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
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 run the Delete tool.
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 called "AllStatesLayer" 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.