0

I'm attempting to create a more flexible approach to calculating fields used in the Where clauses in Select by Attribute SQL queries.

Is there a way to keep my current approach (below) while using variable field names for what's included in the Where clauses?

My goal is to set the field names of a related shapefile as parameters in an ArcMap custom tool in a new toolbox(.tbx). The parameters would be utilized in the SQL query.

# Import arcpy module

import arcpy

arcpy.env.workspace = 'C:/Users/Kurt/Documents/ArcGIS/Projects/ChattanoogaLTS'


#reads shapefile into arcpy layer object

arcpy.MakeFeatureLayer_management ('Export_Output_4.shp', 'AddingLTS')


#Local Variable

Streets_layer = "AddingLTS"

#Add Field

arcpy.AddField_management(Streets_layer, "LTS", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")


#Select LTS 1 conditions

arcpy.SelectLayerByAttribute_management(Streets_layer, "NEW_SELECTION", """(LanesPerDi = 1 AND sidepath =1 AND speed <= 20)
                                        OR (LanesPerDi = 1 AND sidepath =1 AND speed = 25) 
                                        OR (LanesPerDi = 1 AND sidepath =1 AND speed = 30) 
                                        OR (LanesPerDi = 1 AND sidepath =0 AND BL_Width >= 6 AND speed <= 20) 
                                        OR (LanesPerDi = 1 AND sidepath =0 AND BL_Width >= 6 AND speed = 25) 
                                        OR (LanesPerDi = 1 AND sidepath =0 AND BL_Width >= 6 AND speed = 30) 
                                        OR (LanesPerDi = 1 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed <= 20) 
                                        OR (LanesPerDi = 1 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 25) 
                                        OR (LanesPerDi = 1 And sidepath = 0 And (FC_2 = 'Local_Private' Or FC_2 = 'Local') And BL_Width = 0 And speed <= 20) 
                                        OR (LanesPerDi = 1 And sidepath = 0 And (FC_2 = 'Local_Private' Or FC_2 = 'Local') And BL_Width = 0 And speed = 25) 
                                        OR (LanesPerDi = 2 AND sidepath =1 AND speed <= 20) 
                                        OR (LanesPerDi = 2 AND sidepath =1 AND speed = 25) 
                                        OR (LanesPerDi = 2 AND sidepath =0 AND BL_Width >= 6 AND speed <= 20) 
                                        OR (LanesPerDi = 2 AND (FC_2 = 'Local_Private' Or FC_2 = 'Local') AND sidepath =0 AND BL_Width >= 6 AND speed = 25) 
                                        OR (LanesPerDi = 2 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed <= 20) 
                                        OR (LanesPerDi = 2 AND  (FC_2 = 'Local_Private' Or FC_2 = 'Local') AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 25) 
                                        OR (LanesPerDi = 3 AND sidepath =1 AND speed <= 20)""")


#Calculate LTS 1

arcpy.CalculateField_management(Streets_layer, "LTS", "1", "VB", "")


#Select LTS 2 conditions

arcpy.SelectLayerByAttribute_management(Streets_layer, "NEW_SELECTION", """(LanesPerDi = 1 AND sidepath =1 AND speed = 35) 
                                        OR (LanesPerDi = 1 AND sidepath =1 AND speed >= 40) 
                                        OR (LanesPerDi = 1 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 30) 
                                        OR (LanesPerDi = 1 And sidepath = 0 And (FC_2 = 'Collector' Or FC_2 = 'Arterial') And BL_Width = 0 And speed <= 20) 
                                        OR (LanesPerDi = 1 And sidepath = 0 And (FC_2 = 'Collector' Or FC_2 = 'Arterial') And BL_Width = 0 And speed = 25) 
                                        OR (LanesPerDi = 2 AND sidepath =1 AND speed = 30) OR (LanesPerDi = 2 AND sidepath =1 AND speed = 35) 
                                        OR (LanesPerDi = 2 AND sidepath =1 AND speed >= 40) 
                                        OR (LanesPerDi = 2 AND (FC_2 = 'Collector' Or FC_2 = 'Arterial') AND sidepath =0 AND BL_Width >= 6 AND speed = 25) 
                                        OR (LanesPerDi = 2 AND sidepath =0 AND BL_Width >= 6 AND speed = 30) 
                                        OR (LanesPerDi = 2 AND  (FC_2 = 'Collector' Or FC_2 = 'Arterial') AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 25)
                                        OR (LanesPerDi = 2 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 30)
                                        OR (LanesPerDi = 2 And sidepath = 0 And BL_Width = 0 And speed <= 20)
                                        OR (LanesPerDi = 2 And sidepath = 0 And (FC_2 = 'Local_Private' Or FC_2 = 'Local') And BL_Width = 0 And speed = 25)
                                        OR (LanesPerDi = 3 AND sidepath =1 AND speed = 25) 
                                        OR (LanesPerDi = 3 AND sidepath =1 AND speed = 30) 
                                        OR (LanesPerDi = 3 AND sidepath =1 AND speed = 35) 
                                        OR (LanesPerDi = 3 AND sidepath =1 AND speed >= 40) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND BL_Width >= 6 AND speed <= 20) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND BL_Width >= 6 AND speed = 25)""")


#Calculate LTS 2

arcpy.CalculateField_management(Streets_layer, "LTS", "2", "VB", "")


#Select LTS 3 conditions

arcpy.SelectLayerByAttribute_management(Streets_layer, "NEW_SELECTION", """(LanesPerDi = 1 AND sidepath =0 AND BL_Width >= 6 AND speed = 35)
                                        OR (LanesPerDi = 1 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 35) 
                                        OR (LanesPerDi = 1 And sidepath = 0 And BL_Width = 0 And speed = 30) 
                                        OR (LanesPerDi = 1 And sidepath = 0 And BL_Width = 0 And speed = 35) 
                                        OR (LanesPerDi = 2 AND sidepath =0 AND BL_Width >= 6 AND speed = 35) 
                                        OR (LanesPerDi = 2 And sidepath = 0 And (FC_2 = 'Collector' Or FC_2 = 'Arterial') And BL_Width = 0 And speed = 25) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND BL_Width >= 6 AND speed = 30) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND BL_Width >= 6 AND speed = 35) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed <= 20) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 25) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 30) 
                                        OR (LanesPerDi = 3 And sidepath = 0 And BL_Width = 0 And speed <= 20)""")


#Calculate LTS 3

arcpy.CalculateField_management(Streets_layer, "LTS", "3", "VB", "")


#Select LTS 4 conditions

arcpy.SelectLayerByAttribute_management(Streets_layer, "NEW_SELECTION", """(LanesPerDi = 1 AND sidepath =0 AND BL_Width >= 6 AND speed >= 40) 
                                        OR (LanesPerDi = 1 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 40) 
                                        OR (LanesPerDi = 1 And sidepath = 0 And BL_Width = 0 And speed >= 40) 
                                        OR (LanesPerDi = 2 AND sidepath =0 AND BL_Width >= 6 AND speed >= 40) 
                                        OR (LanesPerDi = 2 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 35) 
                                        OR (LanesPerDi = 2 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed >= 40) 
                                        OR (LanesPerDi = 2 And sidepath = 0 And BL_Width = 0 And speed = 30) 
                                        OR (LanesPerDi = 2 And sidepath = 0 And BL_Width = 0 And speed = 35) 
                                        OR (LanesPerDi = 2 And sidepath = 0 And BL_Width = 0 And speed >= 40) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND BL_Width >= 6 AND speed >= 40) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed = 35) 
                                        OR (LanesPerDi = 3 AND sidepath =0 AND (BL_Width > 0 AND BL_Width <6) AND speed >= 40) 
                                        OR (LanesPerDi = 3 And sidepath = 0 And BL_Width = 0 And speed = 25) 
                                        OR (LanesPerDi = 3 And sidepath = 0 And BL_Width = 0 And speed = 30) 
                                        OR (LanesPerDi = 3 And sidepath = 0 And BL_Width = 0 And speed = 35) 
                                        OR (LanesPerDi = 3 And sidepath = 0 And BL_Width = 0 And speed >= 40)""")


#Calculate LTS 4

arcpy.CalculateField_management(Streets_layer, "LTS", "4", "VB", "")
4
  • Can you be a little more specific on which field names you're wanting to use as variables? Are they the fields you're adding (with AddField), or are they the field names in the Where clause? Dec 15, 2021 at 15:51
  • the fields names within the Where clause (LanesPerDi, sidepath, speed, etc.)
    – KurtH
    Dec 15, 2021 at 16:53
  • By "ArcMap custom tool", do you mean a toolbox tool or a Python Add-In?
    – Dan
    Dec 15, 2021 at 23:06
  • I meant to say a tool residing within a toolbox I create
    – KurtH
    Dec 15, 2021 at 23:18

1 Answer 1

0

You will need to setup parameters in the Tool dialog window and then use GetParameterAsText to call them in your code.

For a very basic case, this will allow a user to manually type in the field names to use in the where statement.

add script dialog

and your code would look something like:

import arcpy

fieldName1 = arcpy.GetParameterAsText(0)
fieldName2 = arcpy.GetParameterAsText(1)

where = "{} = 'foo' and {} = 'bar'".format(fieldName1, fieldName2)

arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", where)
0

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.