topic Re: Select by Attributes Using Python List in Python Questions

Select by Attributes Using Python List

BenYoungs — Thu, 08 Sep 2011 12:59:04 GMT

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.

Re: Select by Attributes Using Python List

MathewCoyle — Fri, 10 Dec 2021 21:27:53 GMT

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?

Re: Select by Attributes Using Python List

ChrisSnyder — Fri, 10 Dec 2021 21:27:55 GMT

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!!!

Re: Select by Attributes Using Python List

MathewCoyle — Thu, 08 Sep 2011 16:11:01 GMT

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

Re: Select by Attributes Using Python List

BenYoungs — Thu, 08 Sep 2011 16:13:12 GMT

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.

Re: Select by Attributes Using Python List

ChrisSnyder — Fri, 10 Dec 2021 21:27:58 GMT

...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)))

Re: Select by Attributes Using Python List

BenYoungs — Thu, 08 Sep 2011 17:32:43 GMT

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.

Re: Select by Attributes Using Python List

ChrisSnyder — Fri, 10 Dec 2021 21:28:00 GMT

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]

Re: Select by Attributes Using Python List

MathewCoyle — Thu, 08 Sep 2011 18:01:23 GMT

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...

Re: Select by Attributes Using Python List

ChrisSnyder — Thu, 08 Sep 2011 18:04:23 GMT

Ha!

+2

Re: Select by Attributes Using Python List

BenYoungs — Thu, 08 Sep 2011 18:43:06 GMT

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.

Re: Select by Attributes Using Python List

LeanneSulewski — Tue, 10 Apr 2012 14:36:03 GMT

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