nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Select by Attributes Using Python List

BenYoungs

All,

Is there an efficient way to select multiple features in a feature class using a Python list containing OIDs of the features?

What I have set up right now is the following:

for OID in OIDlist:
query = 'OID = ' + OID
gp.SelectByAttribute(layername, "ADD_TO_SELECTION", query)

That works, however it is painfully slow. Is there another approach that I can use (possibly avoiding the GP SelectByAttributes tool?) that would be more efficient?

My desired end result is a layer file that only contains the features with OIDs specified in the list.

Thanks in advance.

Find more posts tagged with

Accepted answers

All comments

LeanneSulewski

Thank you! You guys solved a python issue I was having!! Thanks a bunch! You guys save me a ton of time!

BenYoungs

Chris,

You're definitely right on the merits of using local data for geoprocessing. However, I was trying to bypass that if possible because this tool will be running on potentially hundreds of feature classes.

I think this definitely still gives me something to work with, and I will take a look at the work-around you provided and see if that works for me.

Again, thanks to you both for the help.

ChrisSnyder

Ha!

+2

MathewCoyle

Step1 to any ESRI geoprocessing analysis: Copy all the data to a local hard drive (perferably in .shp or FGDB format), then proceed with the analysis (i.e. Don't use data directly from a network connection!).

+1
no upvote button...

ChrisSnyder

Did I mention that Oracle SDE sucks? Among other things I hate about it is that it has character limits on the SQL expresions you can pass into it. FGDB is much better in this regard...

This probably doesn't help you as I bet you are developing some sort of interactive tool/toolbox or something, but I learned a long while back... Step1 to any ESRI geoprocessing analysis: Copy all the data to a local hard drive (perferably in .shp or FGDB format), then proceed with the analysis (i.e. Don't use data directly from a network connection!).

Here's a work around that builds a selection table in SDE

#blah blah
    else:
        #Process: Try to select the arcs
        message = "Selecting features. Please wait..."; showPyMessage()
        selectionOidList = list(currentSearchOidList)
        selectionOidList.sort()
        gp.SelectLayerByAttribute_management(inLayer, "NEW_SELECTION", oidFieldName + " in (" + str(selectionOidList)[1:-1] + ")")

        #blah blah
        #blah blah

        #Process: Proceed with getting the selected feature count... if data source is in SDE this can take a while!               
        selectedFeatureCount = int(gp.GetCount_management(inLayer).getoutput(0))
        if selectedFeatureCount > 0:
            message = "Selected " + str(selectedFeatureCount) + " features..."; showPyMessage()
        elif sdeDataSourceFlag == True and selectedFeatureCount == 0 and len(selectionOidList) > 0:
            #Process: Build an SDE look up table to handle the large selection!
            message = "Building ArcSDE selection table. Please wait..."; showPyMessage()
            lookupTblName = "SEL_OID_" + string.lower(os.environ.get("USERNAME")) + "_" + time.strftime('%Y%m%d%H%M%S')
            gp.CreateTable_management("in_memory", lookupTblName, "", "")
            gp.AddField_management("in_memory\\" + lookupTblName, "SEL_OID", "LONG")
            insertRows = gp.insertcursor("in_memory\\" + lookupTblName)
            for currentSearchOid in currentSearchOidList:
                insertRow = insertRows.newrow()
                insertRow.SEL_OID = currentSearchOid
                insertRows.insertrow(insertRow)
            del insertRow
            del insertRows
            parentWorkspace = parentFC[0:-len(parentFC.split("\\")[-1]) - 1] #if stream dataset (aka  "the parent" ) is in SDE put the lut there too
            lookupTblPath = parentWorkspace + "\\" + lookupTblName
            gp.CopyRows_management("in_memory\\" + lookupTblName, lookupTblPath, "")
            gp.Delete_management("in_memory\\" + lookupTblName, "")
            gp.ChangePrivileges_management(lookupTblPath, "public", "GRANT", "GRANT")
            gp.SelectLayerByAttribute_management(inLayer, "NEW_SELECTION", oidFieldName + " in (SELECT SEL_OID FROM " + lookupTblName + ")")
            gp.Delete_management(lookupTblPath, "")         
            message = "Selected " + str(count2) + " features..."; showPyWarning()
        else:
             message = "ERROR: No features were selected! Exiting script..."; showPyError(); sys.exit()
        del currentSearchOidList 
    message = "REFRESH THE ARCMAP VIEW TO DISPLAY NEWLY SELECTED FEATURES!"; showPyWarning()  
    #*****************GEOPROCESSING STUFF ENDS HERE******************************
  
    #Indicates that the script is complete
    message = sys.argv[0] + " is all done!"; showPyMessage()

except:
    message = "Error in script! Consult log file " + logFile + " for details..."; showPyError()
    message = "\n*** LAST GEOPROCESSOR MESSAGE (may not be source of the error)***";  print >> open(logFile, 'a'), str(time.ctime()) + " - " + message
    print >> open(logFile, 'a'), gp.GetMessages()
    message = "PYTHON TRACEBACK INFO: " + traceback.format_tb(sys.exc_info()[2])[0];  print >> open(logFile, 'a'), str(time.ctime()) + " - " + message
    message = "PYTHON ERROR INFO: " +  str(sys.exc_type)+ ": " + str(sys.exc_value) + "\n";  print >> open(logFile, 'a'), str(time.ctime()) + " - " + message
    message = "\n*** PYTHON LOCAL VARIABLE LIST ***"; print >> open(logFile, 'a'), str(time.ctime()) + " - " + message #don't print this mess to ArcToolbox (just the logFile)!
    variableCounter = 0                      
    while variableCounter < len(locals()):
        message =  str(list(locals())[variableCounter]) + " = " + str(locals()[list(locals())[variableCounter]]); print >> open(logFile, 'a'), str(time.ctime()) + " - " + message #don't print this mess to ArcToolbox (just the logFile)!
        variableCounter = variableCounter + 1]

BenYoungs

I ran into a problem while implenting this....I am running this query on an SDE/Oracle database, and I get the following error:

ORA-01795 Maximum number of expressions in a list is 1000.

I am going to do some research on this and see how I can get around this, but it doesnt seem like an Oracle setting that you can change.

ChrisSnyder

...tuple list instead of a list list...

Okay I get it. So yes a tuple could be even better - although you can't sort a tuple 😞 , and the OID field is (always?) sorted in ascending oder, so I think having the OID-based sql expresion in a sorted order would probably make it execute a bit faster. So something like:

oidList = [6,4,2,6,8,1]
oidList.sort()
gp.MakeFeatureLayer_managment(fc, "fl", "OBJECTID in (" + str(oidList)[1:-1] + ")")

or using a tuple:

oidList = [6,4,2,6,8,1]
oidList.sort()
gp.MakeFeatureLayer_managment(fc, "fl", "OBJECTID in " + str(tuple(oidList)))

BenYoungs

I am using 9.3.1 in this instance. I was hoping that I would be able to use the IN statement somewhere in there and get rid of the SelectByAttribute loop, and from both your examples that certainly looks like it will work.

I will try to add the IN query directly to the MakeFeatureLayer process and see how much that improves performance.

Thank you both for your suggestions.

MathewCoyle

Yes Chris' solutions would probably be better, I was passing the list as a tuple list instead of a list list.

ChrisSnyder

I think it needs to be formatted like this:

oidList = [1,2,3,4,5,6]
gp.SelectLayerByAttribute(layername, "ADD_TO_SELECTION", "OBJECTID in (" + str(oidList)[1:-1] + ")")

Instead of using the SelectLayerByAttribute_management() tool, use the MakeFeatureLayer_managment() tool

gp.MakeFeatureLayer_managment(fc, "fl", "OBJECTID in (" + str(oidList)[1:-1] + ")")
gp.Clip_analysis("fl", cookieCutterFC, blah, blah)

MakeFeatureLayer_managment() is a much faster way to make "selections", the drawback is that is can oly be used to make a "NEW_SELECTION", and can't be used to"ADD_TO_SELECTION", "SWITCH_SELECTION", etc.

Also, FYI: You can execute SQL statments like this (composed of lists of OIDs) that are millions of characters long using FGDB format! So cool!!!

MathewCoyle

Something like this should work. (won't)

query = 'OID in '+OIDlist
gp.SelectByAttribute(layername, "ADD_TO_SELECTION", query)

This would work better.

query = 'OID in '+str(OIDlist)
gp.SelectByAttribute(layername, "NEW_SELECTION", query)

By the way, what version of Arc are you running?

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.