I am using ArcGIS Pro 2.4.0, with Python 3.6.8. I found the following similar posts but could not figure out how to apply them: Using Python Search Cursor results to Select Features by Attributes and Selecting rows in a layer using arcpy.SearchCursor
I wish to make a Feature Layer that is a subset of a Feature Class. The subset should include any records that meet one of nine different scenarios that I have for when a record should be flagged as a problem. If I were to create this layer using a where statement in arcpy.MakeFeatureLayer_management
, I believe it would be illegible to someone else as it would have many compound OR
and AND
statements. (Method 3 below is a reproducible, truncated example.)
Instead, I'd like to first create a list of all unique id's of those features meeting one of the nine criteria as they pass through an arcpy.da.SearchCursor
. Then I was planning on using a where clause within MakeFeatureLayer
, selecting only those features whose unique ID is also within the compiled list. (Methods 1 and 2 below)
My problem is that in method 1, I can't figure out how to use an "in"
SQL statement in my where clause. I receive the error: ERROR 000358: Invalid expression. Failed to execute (MakeFeatureLayer)
. (This is generated with the second to last line of code below).
Here's a reproducible example, where I use the shapefile within cb_2018_us_cd116_20m.zip
(the 116 congressional districts) found on the Census website.
Method 1: (not working)
First run query via SearchCursor
on data and save records matching criteria to list, then create layer based on IDs matching those in list via MakeFeatureLayer
.
import arcpy
arcpy.env.workspace = '~Downloads/Reproducible_Example'
district_shapefile = r'~Downloads\Reproducible_Example\cb_2018_us_cd116_20m.shp'
# Create empty list to which GEOID of records of interest will be added
interest_list = []
# Run a search cursor to iterate through records and add GEOID that match criteria to list of records of interest.
in_table = district_shapefile
input_table_fields = ['GEOID', 'ALAND', 'AWATER']
with arcpy.da.SearchCursor(in_table, input_table_fields) as cursor:
for row in cursor:
# Scenario 1: ALNAD > 66914406427 and AWATER > 11321049519
if row[1] > 66914406427 and row[2] > 11321049519:
interest_list.append(row[0])
# Scenario 2: ALAND < 37598152 but exclude record where ALAND = 26316818 and AWATER = 7203018
if row[1] < 37598152 and not (row [1] == 26316818 and row[2] == 7203018 ):
interest_list.append(row[0])
# Print resultant list showing three records matching criteria: ['2708', '0200', '3610']
print(interest_list)
# Subset feature class so that only contains those records whose GEOID is in interest_list.
expression = '"GEOID" in interest_list'
arcpy.MakeFeatureLayer_management(in_features = district_shapefile, out_layer = 'flagged_records', where_clause = expression)
arcpy.SaveToLayerFile_management(in_layer = 'flagged_records', out_layer = 'flagged_records_layer')
An alternative approach I tried is method 2 (below), however my output layer is not a subset and instead contains all records. (Although I should say, I do not actually want to create a new feature class. I want to create a layer so that any edits I make to correct the flagged records are updated to the original feature class, so this method I believe is not applicable.)
Method 2: (not working)
First run query via SearchCursor
on data and save records matching criteria to list (code above), then iterate though those records in list and add those to a selection via a second SeachCursor
(below):
for_loop_layer = arcpy.MakeFeatureLayer_management(in_features = district_shapefile)
for i in interest_list:
in_table = for_loop_layer
input_table_fields = ['GEOID']
with arcpy.da.SearchCursor(in_table, input_table_fields) as cursor:
for row in cursor:
# For each record, if the field ID matches i, add record to selection
if row[0] == i:
print(i)
arcpy.SelectLayerByAttribute_management(in_layer_or_view = for_loop_layer, selection_type = 'ADD_TO_SELECTION')
arcpy.CopyFeatures_management(for_loop_layer, 'for_loop_selections')
I am able to create a subset output layer of flagged records with method 3 (below). However, my real scenarios are long and I am looking for an alternative method so as to avoid having all criteria combined in one unintelligible where clause.
Method 3: (working but not desired)
Run query directly with one long where clause.
arcpy.MakeFeatureLayer_management(in_features = district_shapefile, out_layer = 'interest_layer', where_clause= "(ALAND > 66914406427 and AWATER > 11321049519) OR (ALAND < 37598152 and not (ALAND = 26316818 and AWATER = 7203018 ))")
arcpy.SaveToLayerFile_management(in_layer = 'interest_layer', out_layer = 'interest_records_layer')
expression
-- tryexpression = '"GEOID" in ({:s})'.format(','.join(interest_list))