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:
        # 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 ):

# Print resultant list showing three records matching criteria: ['2708', '0200', '3610']        

# 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:
                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')
  • 1
    Use str(tuple(list)) in your query. – FelixIP Mar 13 '20 at 23:12
  • Your Method1 doesn't actually place the list in expression -- try expression = '"GEOID" in ({:s})'.format(','.join(interest_list)) – Vince Mar 13 '20 at 23:27
  • @Vince, thank you. I still get the same error though ERROR 000358: Invalid expression Failed to execute (MakeFeatureLayer). – ENIAC-6 Mar 13 '20 at 23:35
  • @FelixIP, thank you. I tried '"GEOID" in str(tuple(interest_list))' but still get the same error. – ENIAC-6 Mar 13 '20 at 23:38
  • @FelixIP this did work! field = "GEOID" expression = '"{}" in {}'.format(field, str(tuple(interest_list))) Though I'm not exactly certain that I understand why. It looks like the where clause can only search within a string and not a list? – ENIAC-6 Mar 13 '20 at 23:57

@Vince's comment is pretty close, but the GEOID field is actually a string, so you need to explicitly include single quotes around each value in the list in the sql query

expression = '"GEOID" in ({:s})'.format(','.join(["'" + str(geoid) +"'" for geoid in interest_list]))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.