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 criteria, 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 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\StateBoundaries" 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() # Clean up feature layers arcpy.Delete_management("AllStatesLayer") arcpy.Delete_management("SelectionStateLayer")
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; however, you don't need to explicitly delete the cursor because the "with" statement cleans it up for you automatically. This is one of the benefits of using the cursors from the data access module.
Before you move on, examine the following tool reference pages. You can ignore the Command Line Syntax section, but pay particular attention to the Usage Tips and the Script Examples.