I am trying to write a python script and create a script tool in ArcGIS that will let a user choose the value or values that will be used to extract a shapefile. These selected value(s) will then need to pass through the select tool. I'm sure this involves writing a WHERE clause but I'm new to python and am a little lost on this one.
What's your specific question? Do you need some general guidance on python scripting, or are you not sure how to construct the where clause from user input?– bluefootOct 5, 2012 at 23:44
1I'm not exactly sure how to construct the where clause and the how to pass this clause through a SQL statement for the select by attribute tool. This post: gis.stackexchange.com/questions/21760/… is almost exactly what I need except I need it to work as a python script– rralbrittonOct 5, 2012 at 23:46
Ok, I took another swing at it now that I think I have more of an idea of what you're looking for. It isn't perfect, as you can't select specific existing values, but you CAN select a field and enter as many values as you want into the query. Here's the code:
import arcpy from arcpy.analysis import * #Define input/output input = arcpy.GetParameterAsText(0) fieldName = arcpy.GetParameterAsText(1) values = arcpy.GetParameterAsText(2) output = arcpy.GetParameterAsText(3) #Build the where clause queryJoin = "' OR " + fieldName + " = '" whereClause = fieldName + " = '" + queryJoin.join(values) + "'" Select(input, output, whereClause)
When you set up your parameters, make sure that the field parameter is 'obtained from' "input". Also make sure you select "yes" for multi value for the values parameter.
This will give you something very similar to the example you linked to. I'm sure the query building could be done a better way but my brain is fried. Let me know if this is more along the lines of what you were looking for.
thank you for your input. Maybe I'm not asking this question correctly so please bare with me. I have a countys shapefile that contains a "CO_NAME" field. From this field I want a user to select one or more counties. These selected counties need to become part of the SQL expression. Since the name of the counties will be user derived how is the SQL expression constructed? Oct 6, 2012 at 0:20
My edit above gets you closer to what you want, there doesn't seem to be a data type that can be assigned to pull specific column values out of the feature, unfortunately, so the user will have to manually type in county names.– bluefootOct 6, 2012 at 3:01
The last script you provided runs but returns an empty shapefile. I'm not sure what this means. I posted an answer below that also runs an returns the first county in a list. Oct 6, 2012 at 16:05
I figured out that the reason its returning an empty shapefile is the query statement is splitting county names into individual letters. For example if I enter "ABC" as a county the where clause reads it as 'A' 'B' 'C', and it only does this for the first county in the string. I'm still working on how to fix this Oct 8, 2012 at 2:53
The following arcpy-based script which uses select layer by attributes is a good starting point. I retrofitted the script to work as a script tool in ArcGIS. You can easily substitute polygon FCs for point FCs in the Select Layers section to tailor the script to your specific needs.
# Import system modules import arcpy from arcpy import env # Set the workspace env.workspace = arcpy.GetParameterAsText(0) Dir = env.workspace # Local variables input = arcpy.GetParameterAsText(1) polygon = arcpy.GetParameterAsText(2) expression = arcpy.GetParameterAsText(3) name = arcpy.GetParameterAsText(4) # Make a layer from the input feature class arcpy.MakeFeatureLayer_management(input, "lyr") # Select all points or polygons which overlap the polygon/s of interest arcpy.SelectLayerByLocation_management("lyr", "intersect", polygon, 0, "new_selection") # Within selected features, further select based on a SQL query within the script tool arcpy.SelectLayerByAttribute_management("lyr", "SUBSET_SELECTION", expression) # Write the selected features to a new featureclass arcpy.CopyFeatures_management("lyr", Dir + "\\" + str(name))
Using the following parameters:
In this test example,the output includes only the points 1) within the polygons and 2) with "OBJECTID" > 50.
OK - so this is what I've come up with so far. It kind of works. Its going through the whole process but only giving me back the first county that I list as an argument. For example, when I type in county names in the arguments dialogue box I've tried typing it "ASHE";"AVERY" as well as "ASHE" "AVERY" and the script only takes in ASHE. I'm assuming this might be a syntax issue? Here is my current code:
def whereClause(table, field, values): """Takes a semicolon-delimited list of values and constructs a SQL WHERE clause to select those values within a given field and table."""
# Add field delimiters fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(table).path, field) # Split multivalue at semicolons and strip quotes valueList = [value[1:-1] if (value.startswith("'") and value.endswith("'")) else value for value in values.split(';')] # Determine field type fieldType = arcpy.ListFields(table, field).type # Add single-quotes for string field values if str(fieldType) == 'String': valueList = ["'%s'" % value for value in valueList] # Format WHERE clause in the form of an IN statement whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(valueList))a return whereClause
def outName(input,post="Out",fileExtension="shp"): """Returns output name.""" outName=os.path.basename(input).split(".")+post+"."+fileExtension return outName
InputFC = "C:/Project/ToolData/CountyBoundary.shp" Field = "CO_NAME" Counties = arcpy.GetParameterAsText(0) #user selects counties from choice list SQL=whereClause(InputFC,Field,Counties) OutputFC=outName(InputFC,"_Select") arcpy.Select_analysis(InputFC,OutputFC,SQL) count=int(arcpy.GetCount_management(OutputFC).getOutput(0))
print OutputFC, " was created, and contains the following", count,"counties:"
sc = arcpy.SearchCursor(OutputFC)
for line in sc: print line.CO_NAME