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; } } }

Use Python to Select by Attributes in attribute table

Bud

We have scenarios where we want to select rows in a FC in the attribute table, but there isn't a way to run the query using the Select by Attributes window — due to limitations in SQL / SDE.ST_GEOEMTRY functions.

For example: Select features that have true curves. While it's not possible to make that selection via SQL, it's relatively easy to find the records via ArcPy using the hasCurves Geometry property.

Could ESRI consider giving us a way to Select by Attributes using Python — via functionality right in the Attribute Table window?

Something kind of like this?

Encuentra más publicaciones etiquetadas con

Geoprocessing

Under Consideration

Status: Under Consideration

Comentarios

RandyCasey

@Bud maybe I am not understanding your scenario and ask: but have you tried this?

Select Layer By Attribute (Data Management)—ArcGIS Pro | Documentation

I use it all the time in my automation scripts as well as using it in the built in Python console in Pro. And when I do use it in Pro, if I have a map open with the layer added, it selects my features in the attribute table for me automatically, then I can use that selection set in further geoprocessing tools. It works even better when using it in stand-alone scripts.

Bud

@RandyCasey Thanks for the details.

In hindsight, my original post wasn't clear. I've updated it.

I'm thinking it might be handy if we could make Python selections right in the Select by Attributes window...using really short snippets of Python code, instead of full Python scripts. Kind of like what we do in the field calculator in ArcMap: have the option of using VB Script or Python. But instead, in the Select by Attributes window, have the option of using a SQL WHERE clause or a Python snippet.

RandyCasey

@Bud Ah! I completely understand now. I have usually had to create a view table to perform that exact workflow. Your suggestion would make that simpler. Thanks for clarifying it though and sorry for the confusion.

DrewFlater

Just want to mention this can currently be done as a two step process. It is likely Select By Attributes will remain a SQL query implementation only. But you can easily run Calculate Field using a Python expression to flag or create new values which can easily be queried. From the example of selecting records that have curves, do this:

arcpy.management.CalculateField("YourLayer", "HasCurvesField", "!Shape.hasCurves!", "PYTHON3", '', "SHORT")
arcpy.management.SelectLayerByAttribute("YourLayer", "NEW_SELECTION", "HasCurvesField = 1")

DavidSolari

Felt like solving a challenge so here's a working tool. I can't attach the full tool so here's the components:

Parameters:

Execution:

import arcpy
import ast


def parseSelectionType(s):
    return s.strip().upper().replace(" ", "_")
    
    
def checkFunc(funcText):
    try:
        ast.parse(funcText)
        return True, None
    except SyntaxError as e: 
        return False, "Invalid syntax in Python function, line {}".format(e.lineno - 1)
    
    
def buildFuncText(funcBody, fieldNames):
    FUNC_NAME = "_select_func"
    signature = "def {}(".format(FUNC_NAME)
    if len(fieldNames):
        signature += ", ".join(f.lower() for f in fieldNames)
    signature += "):\n\t"
    indentFuncBody = funcBody.replace("\r\n", "\n").replace("\n","\n\t")
    return signature + indentFuncBody, FUNC_NAME


def main(layer, fieldNames, funcBody, selectionType="NEW_SELECTION", isInvertSelection=False):
    selectFuncText, selectFuncName = buildFuncText(funcBody, fieldNames)
    isValid, error = checkFunc(selectFuncText)
    if not isValid:
        arcpy.AddError(error)
        raise SystemExit(1)
    namespace = {}
    exec(selectFuncText, namespace)
    selectFunc = namespace[selectFuncName]
    
    desc = arcpy.da.Describe(layer)
    oidFieldName = desc["OIDFieldName"]
    isOIDInFunc = True
    if oidFieldName not in fieldNames:
        fieldNames = ["OID@"] + fieldNames
        isOIDInFunc = False
    shapeFieldName = desc["shapeFieldName"]
    if shapeFieldName in fieldNames:
        fieldNames = [f if f != shapeFieldName else "SHAPE@" for f in fieldNames]
    selectOIDs = []
    with arcpy.da.SearchCursor(layer, fieldNames) as search:
        for row in search:
            oid = row[0]
            params = row if isOIDInFunc else row[1:]
            if selectFunc(*params):
                selectOIDs.append(oid)
    sql = "{} IN ({})".format(oidFieldName, ",".join(str(o) for o in selectOIDs))
    arcpy.management.SelectLayerByAttribute(layer, selectionType, sql, isInvertSelection)


if __name__ == "__main__":
    layer = arcpy.GetParameterAsText(0)
    selectionType = parseSelectionType(arcpy.GetParameterAsText(1)) if arcpy.GetParameterAsText(1) else "NEW_SELECTION"
    fields = [f.value for f in arcpy.GetParameter(2)]
    funcBody = "\n".join(s for s in arcpy.GetParameter(3))
    isInvertSelection = arcpy.GetParameter(4)
    main(layer, fields, funcBody, selectionType="NEW_SELECTION", isInvertSelection=False)

Validation:

import ast


def checkFunc(funcText):
    try:
        ast.parse(funcText)
        return True, None
    except SyntaxError as e: 
        return False, "Invalid syntax in Python function, line {}".format(e.lineno - 1)


def buildFuncText(funcBody, fieldNames):
    FUNC_NAME = "_select_func"
    signature = "def {}(".format(FUNC_NAME)
    if len(fieldNames):
        signature += ", ".join(f.lower() for f in fieldNames)
    signature += "):\n\t"
    indentFuncBody = funcBody.replace("\r\n", "\n").replace("\n","\n\t")
    return signature + indentFuncBody, FUNC_NAME


class ToolValidator:
  # Class to add custom behavior and properties to the tool and tool parameters.

    def __init__(self):
        # set self.params for use in other function
        self.params = arcpy.GetParameterInfo()

    def initializeParameters(self):
        # Customize parameter properties. 
        # This gets called when the tool is opened.
        return

    def updateParameters(self):
        # Modify parameter values and properties.
        # This gets called each time a parameter is modified, before 
        # standard validation.
        return

    def updateMessages(self):
        # Customize messages for the parameters.
        # This gets called after standard validation.
        fields = self.params[2]
        funcBody = self.params[3]
        if fields.value and funcBody.value:
            fieldNames = [f.value for f in fields.values]
            funcBodyText = "\n".join(funcBody.values)
            funcText = buildFuncText(funcBodyText, fieldNames)[0]
            isValid, error = checkFunc(funcText)
            if not isValid:
                funcBody.setErrorMessage(error)
        return

    # def isLicensed(self):
    #     # set tool isLicensed.
    # return True

The Python Function Body parameter is a bit of a hack, it uses multiple string params in lieu of a proper text box (ESRI pls). I also didn't implement parameter parsing like the Calculate Field tool does so your function parameters are just the field names in lower case (e.g. the field "ParcelID" is accessed as "parcelid"). It'll also convert the shape field to python geometry objects just like the "SHAPE@" cursor token so you can do shape.firstPoint.X and such. There's probably some bugs lurking in here and using eval to run arbitrary input can cause issues but overall this'll get you what you need.

DavidSolari

Also, forgot to mention this, but the way you use the tool is to write a Python function that takes in 1 or more fields for each record and returns a boolean value to determine if it's selected or not. For example, if you have a text field called "CustomerName" and you want to select every record whose customer has a first name over 10 characters, your python function would be "return len(customername.split()[0] if customername else 0) > 10".

Bud

Thanks @DavidSolari!

Side note regarding, “The Python Function Body parameter is a bit of a hack, it uses multiple string params in lieu of a proper text box (ESRI pls).”

If I were to submit an Idea for that limitation, what should the idea be called? “Need a proper textbox parameter for Python geoprocessing tools”? Or something better?

Únete a la Conversación

Inicia sesión para publicar, seguir contenido y más. ¿Nuevo aquí? Regístrate gratis.