I have written some generic user input code for a toolbox script (ArcGIS 10.0) but because everything is a variable I don't know how to write the SQL where clause.
Please can someone help and suggest how to do this.
I have posted the code below but please ignore the other lines, they're working. The only part that does not work is the Select Layer By Attribute sections. All paths etc. are input by the user and citiesL is just "citiesL".
There are also a few lines of code with the # before them because I've tried them and they don't work.
# import system modules import arcpy, os, arcpy from arcpy import env arcpy.env.overwriteOutput = True # get user supplied path, layers and fields path = arcpy.GetParameterAsText(0) # path is H:\working\Findsites.gdb cities = arcpy.GetParameterAsText(1) # cities Layer is cities Feature Layer cities.shp citiesL = "citiesL" counties = arcpy.GetParameterAsText(2) # counties Layer is counties Feature Layer counties.shp countiesL ="countiesL" interstates = arcpy.GetParameterAsText(3) # interstates Layer is Feature Layer interstates.shp crimeField = arcpy.GetParameterAsText(4) # crimeField is fieldname 'CRIME_INDE' SQL expression crimefieldindex = arcpy.GetParameterAsText(5) # crime index is CRIME_INDE and is a string 0.02 whereClause = "\"%s\" = '%s'" % (crimeField, crimefieldindex) universityField = arcpy.GetParameterAsText(6) # universityField is fieldname 'UNIVERSITY' SQL expression universityfieldindex = arcpy.GetParameterAsText(7) # universityfieldindex is the UNIVERSITY field and is string integer 1 whereClause2 = "\"%s\" = '%s'" % (universityField, universityfieldindex) workforceField = arcpy.GetParameterAsText(8) # workforceField is fieldname 'AGE_18_64' SQL expression workforceindex = arcpy.GetParameterAsText(9) # workforce index is attribute of AGE_18_64 field and is a Double and is 25000 whereClause3 = "\"%s\" = '%s'" % (workforceField, workforceindex) farmField = arcpy.GetParameterAsText(10) # farmField is fieldname 'NO_FARMS87' SQL expression farmfieldindex = arcpy.GetParameterAsText(11) # farmfieldindex is the NO_FARMS87 field and is Double integer is 500 whereClause = "\"%s\" = '%s'" % (farmField, farmfieldindex) maxKmInterstate = arcpy.GetParameterAsText(12) # interstate WITHIN_A_DISTANCE linear unit nearestInterstate = arcpy.GetParameterAsText(13) # Near Table Analysis table generated targetFeatures = "cityListL" # cities spatial join layer joinFeatures = "countiesL" # counties spatial join layer cityListFC = arcpy.GetParameterAsText(14) # narrowed down cities list temporary cityListL = "CityListL" # the feature layer of the generated cityList feature class outputLayer = arcpy.GetParameterAsText(15) # ootput # make a layer from the cities feature class arcpy.MakeFeatureLayer_management(cities, citiesL) # select layer by location to interstates arcpy.SelectLayerByLocation_management(citiesL, "WITHIN_A_DISTANCE", interstates, maxKmInterstate, "NEW_SELECTION") # Here is the ESRi suggested code for select layer by attribute where clause - doesn't work though # whereClause = '"' + crimeField + '" = 25000 AND "NO_FARMS87" >= 500") arcpy.SelectLayerByAttribute_management(countiesL, "NEW_SELECTION", whereClause3 + "AND" + whereClause4) # from selection above select cities intersecting counties arcpy.SelectLayerByLocation_management(citiesL, "INTERSECT", countiesL, "", "SUBSET_SELECTION") # save selected features # arcpy.Select_analysis(citiesL, cityListFC, "") arcpy.FeatureClassToShapefile_conversion(citiesL, path) # make temp cities list feature layer so that the output can be spatially joined to counties # Generate NearTable_analysis to find closest interstate distance arcpy.GenerateNearTable_analysis(citiesL, interstates, nearestInterstate, maxKmInterstate) # join new city list layer to generated Near Table arcpy.AddJoin_management(citiesL, "NEAR_FID", nearestInterstate, "IN_FID") # Run the Spatial Join tool, using the defaults for the join operation and join type arcpy.SpatialJoin_analysis(targetFeatures, joinFeatures, outputLayer, "#", "#", "#") # spatially join the narrowed down city/road and counties layers remove unwanted fields and rename others # arcpy.SpatialJoin_analysis("cityListL", "countiesL", "H:/working/Findsites.gdb/FoundCities", "#", "#", "") # create new fieldMappings # Get the output field's properties as a field object #Run the Spatial Join tool, using the defaults for the join operation and join type print arcpy.GetMessages() print "\n*** LAST GEOPROCESSOR MESSAGE (may not be source of the error)***"; print arcpy.GetMessages() print "Python Traceback Info: " + traceback.format_tb(sys.exc_info()) print "Python Error Info: " + str(sys.exc_type)+ ": " + str(sys.exc_value) # save selected features FINAL OOTPUT here print arcpy.GetMessages()