Automating Select by location using ArcPy and where_clause?

Question

I have used the code in Coding python loop to select features of polygon layer than are completely contained in group of 11 layers using ArcPy? to automate select by location analysis using ArcPy.

I have a polygon layer "Provinces.shp" which specify number of cities, and a point layer "Points.shp". The purpose is to find the intersected points in each cities and export these points as separate layers. Thus, I need to first select city names based on "NAME_1" field in the polygon layer and then intersect it with point layer literately.

The code is as below:

import sys, string, os, arcgisscripting
import arcpy

# Script arguments...
A = arcpy.GetParameterAsText(0)
B = arcpy.GetParameterAsText(1)
outfolder = arcpy.GetParameterAsText(2)

# Create a Search Cursor that references the features in layer B
rows = arcpy.da.SearchCursor(B, "*")
row = rows.next()

# Create a numbering system for new output files
filenum = 0

while row:

    # Report a message back to the user about progress
    arcpy.AddMessage("Creating selected set #" + str(filenum) + " from " + A)
    #Create a feature layer to cycle through your selection features
    fc = r'C:/Users/Zarei.f/Desktop/data-module 6/IRN_adm1.shp'
    layerFld = arcpy.AddFieldDelimiters(fc,"NAME_1")

    sql =  """{0} = {1}""".format(layerFld , str(row[6])

    #fidvalue = row.getValue("NAME_1")

    arcpy.MakeFeatureLayer_management(B,"cursor_lyr5"+str(filenum),sql)

    # Process: Select Layer By Location...
    arcpy.SelectLayerByLocation_management(A, "INTERSECT", "cursor_lyr5"+str(filenum), "", "NEW_SELECTION")

    outname = outfolder + "\\" + 'test' + str(filenum)
    # Process: Copy Features...
    arcpy.CopyFeatures_management(A, outname, "", "0", "0", "0")

    #iterate the output file number
    filenum = filenum + 1

    #go to the next row
    row = rows.next()

I got the below error:

ExecuteError: ERROR 

    000230: Failed selecting with NAME_1 = Alborz
    Failed to execute (MakeFeatureLayer).

Regarding to this error, the MakeFeatureLayer analysis can't understand the defined sql clause.

I used several ways to define the sql clause but they all raise a same error.

Answer 1

The other answer appears to provide a solution, but if that works, then I think an easier syntax for ArcPy newcomers to learn uses just Python String Formatting instead:

sql = "{0} = '{1}'".format("NAME_1", row[6])

I write lots of where clauses for use with ArcPy, and never use AddFieldDelimiters. ArcPy itself seems to now be able to cater for figuring out how to present field names to its various layer and table data sources.

Answer 2

Make two changes to your code. First use AddFieldDelimiters, which has the format arcpy.AddFieldDelimiters("featureclass", "Fieldname"). You are allowing arcpy to figure out what syntax is required to access that field. Field delimiters (such as needing to have quotes around the field name) vary between data types. (While you may have had the correct format for this particular feature class, it's usually better to take the guesswork out.) You can find other questions on this site that have additional explanations of AddFieldDelimiters.

Next, since it looks like your data in the field "NAME_1" is string, add single quotes within the SQL expression, around the placeholder for variable {1}.

sql = """{0} = '{1}'""".format(arcpy.AddFieldDelimiters(layerFld, "NAME_1"), str(row[6]))

In the first case you are getting formatting for the FIELD. In the second case you are adding formatting for the DATA.