Updating field based on location

[Emilbrundage]

I have the following information:

A table with a list of cities
A cities layer
A grid layer

I am trying to write a code that goes through the list of cities and searches for each grid that intersects each city in the list. So if I have a city of 'somewhereville' python will find all the grids that intersect the 'somewhereville' feature and then updates a field in the grid's attribute with the city name ('somewhereville'). I have tried a few ways but I can't quite figure it out. Any direction would be great.

[DouglasSands]

I have the following information:

A table with a list of cities
A cities layer
A grid layer

I am trying to write a code that goes through the list of cities and searches for each grid that intersects each city in the list. So if I have a city of 'somewhereville' python will find all the grids that intersect the 'somewhereville' feature and then updates a field in the grid's attribute with the city name ('somewhereville'). I have tried a few ways but I can't quite figure it out. Any direction would be great.

You can definitely accomplish this with a combination of search cursors, careful use of make feature layer, selecting by location, and the field calculator.

Here is a suggested workflow:

1. Start with a Search Cursor and loop over your table of cities.
2. For every row in the table, make a feature layer out of your cities layer. For the where clause, set it so that your City Name field is equal to the current city in the table.
3. Use select by location to select all of the grids that intersect your city.
4. Use the field calculator to update the city name field in your grid to be equal to the current city (again this is for the current row in your table).
5. Clear the selection - This is very important. You do this using Select by Attribute with the option 'CLEAR_SELECTION'
6. Delete the feature layer using arcpy.Delete_management('name of layer')

Essentially, steps 2-6 will take place inside of the for loop as you iterate over every row in your cities table.

Hope this helps!
- Doug

[Emilbrundage]

Thanks Doug! Your advice has been very helpful.

Now I'm stuck on one line that doesn't want to work on me. My code breaks when I try to select layer by location. Here is the code:

import arcpy
from arcpy import env


#arcpy.AddMessage ("1")
arcpy.env.workspace = "C:\E1B8\Grid_Create_Update_11-2013"

#arcpy.AddMessage ("2")

# Create the search cursor
#

tablecur = arcpy.SearchCursor("SampleCityTable")
for row in tablecur:
    cityname = row.City
    sql = "\"'NAME10' = " + cityname + '"'
#    arcpy.AddMessage (sql)
#    arcpy.AddMessage (cityname)

# Make new feature for current city
    arcpy.MakeFeatureLayer_management("state_places.shp","thecity",sql)
#    arcpy.AddMessage ("3")

# Select grid that intersect with new feature
    arcpy.SelectLayerByLocation_management("CityGrids.shp", "INTERSECT", "thecity", "", "NEW_SELECTION")
#    arcpy.AddMessage ("4")

# Update field in grid layer with city name 
    arcpy.CalculateField_management("CityGrids.shp", "CITY_GRID", cityname, "PYTHON", "")

# Remove selection from grid
    arcpy.SelectLayerByAttribute_management("CityGrids.shp", "CLEAR_SELECTION", "")

# Delete newly made city layer
    arcpy.Delete_management("thecity", "")
#    arcpy.AddMessage ("5")

Again, any help is very much appreciated!

Edit: To be more specific, the error I get is:

ERROR 000368: Invalid input data.

from the line:

arcpy.SelectLayerByLocation_management("CityGrids.shp", "INTERSECT", "thecity", "", "NEW_SELECTION")

[JakeSkinner]

Hi Emil,

When using the 'Select Layer by Attribute' or 'Select Layer by Location' functions, the input needs to a be a layer.  Use the MakeFeatureLayer_management function to do this.  Ex:

arcpy.MakeFeatureLayer_management("CityGrids.shp", "cityGridsLyr")
arcpy.SelectLayerByLocation_management("cityGridsLyr", "INTERSECT", "thecity", "", "NEW_SELECTION")

[ChrisSnyder]

What if one of your grid cells has multiple cities within it?

[Emilbrundage]

What if one of your grid cells has multiple cities within it?

Then the first city in the list takes precedent. I was going to incorperate this aspect into my code (with an if statement) once I got everything else working.

[ChrisSnyder]

How about something like this:

import arcpy
cityFC = r"C:\temp\city_polygons.shp"
indexFC = r"C:\temp\index_polygons.shp"
arcpy.MakeFeatureLayer_management(indexFC, "index_fl")
searchRows = arcpy.da.SearchCursor(cityFC, ["SHAPE@","CITY_NAME"])
for searchRow in searchRows:
   geomObj, cityName = searchRow
   arcpy.SelectLayerByLocation_management("index_fl", "INTERSECT", geomObj, "", "NEW_SELECTION")
   arcpy.CalculateField_management("index_fl", "CITY_GRID", "'" + cityName + "'", "PYTHON", "")

Note that if you want the "first" city to trump subsequent ones (in the case of overlap), you would want to sort the cityFC search cursor by OID in decending order (by default the sort order is by OID in ascending order).

[Emilbrundage]

How about something like this:

Note that if you want the "first" city to trump subsequent ones (in the case of overlap), you would want to sort the cityFC search cursor by OID in decending order (by default the sort order is by OID in ascending order).

Thanks CS, I shall study this code to see if I get it, if I can't get my current code working.

I am getting the current error:

ExecuteError: ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used.

Apparently for this line:

    arcpy.SelectLayerByLocation_management("cityGridsLyr", "INTERSECT", "thecity", "", "NEW_SELECTION")

Which doesn't make much sense to me since that line does not contain SQL. Am I missing something?

import arcpy
from arcpy import env

arcpy.env.workspace = "C:\E1B8\Grid_Create_Update_11-2013"

# Create the search cursor
#

tablecur = arcpy.SearchCursor("SampleCityTable.dbf")
for row in tablecur:
    cityname = row.City
    clause = "\"'NAME10' = " + cityname + '"'
    arcpy.AddMessage (clause)
    arcpy.AddMessage (cityname)

# Make new feature for current city
    arcpy.MakeFeatureLayer_management("state_places.shp","thecity",clause)

# Create layer for selection
    arcpy.MakeFeatureLayer_management("CityGrids.shp", "cityGridsLyr")

# Select grid that intersect with new feature
    arcpy.SelectLayerByLocation_management("cityGridsLyr", "INTERSECT", "thecity", "", "NEW_SELECTION")

# Update field in grid layer with city name 
    arcpy.CalculateField_management("cityGridsLyr", "CITY_GRID", cityname, "PYTHON", "")

# Remove selection from grid
    arcpy.SelectLayerByAttribute_management("cityGridsLyr", "CLEAR_SELECTION", "")

# Delete newly made city layer
    arcpy.Delete_management("thecity", "")
    arcpy.Delete_management("cityGridsLyr", "")

[ChrisSnyder]

An invalid SQL statement was used.

Probably not for the SelectByLocation, probably for the MakeFeatureLayer... Instead of:

clause = "\"'NAME10' = " + cityname + '"'

try:

clause = "NAME10 = " + "'" + cityname + "'"

[DouglasSands]

Note that if you want the "first" city to trump subsequent ones (in the case of overlap), you would want to sort the cityFC search cursor by OID in decending order (by default the sort order is by OID in ascending order).

OID is largely arbitrary, though. If there is a way you want to define 'first', for example alphabetically first, you'd need to sort on the name field in descending order.