Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have written some generic user ipnut code for a toolbox script (ArcGIS 10.0) but because everything is a variable I don't know how to write the SQL whereclause.

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.

Any help would be greatly appreciated.

Thanks

# 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 = '"' + crimeField + '" =' + workforceindex"
#whereClause3 = '"' + workforceField + '" >= ' + "'" + workforceindex + "'"
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
#whereClause4 = "'"' +farmField + '" >=' + farmfieldindex"
#whereClause4 = '"' + farmField + '" >= ' + "'" + farmfieldindex + "'"
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

#altwhereclause1 = "CRIME_INDE = 25000"
#altwhereclause4 = "NO_FARMS87 = 500"


# 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.CopyFeatures_management(citiesL, cityListFC)

# 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
# arcpy.MakeFeatureLayer_management(cityListFC,cityListL)

#arcpy.AddField_management(citiesL, "CITYNAME", "TEXT", "", "", "25")
#arcpy.AddField_management(citiesL, "CRIMEINDX", "DOUBLE", "", "", "")
#arcpy.AddField_management(citiesL, "HAS_UNI", "TEXT", "", "", "3")
#arcpy.CalculateField_management(citiesL, "CITYNAME", "!NAME!", "PYTHON")
#arcpy.CalculateField_management(citiesL, "CRIMEINDX", "!CRIME_INDE!", "PYTHON")
#arcpy.CalculateField_management(citiesL, "HAS_UNI", "!UNIVERSITY!", "PYTHON")
#arcpy.DeleteField_management(citiesL,["NAME", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["CRIME_INDE", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["UNIVERSITY", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["ID", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["POPULATION", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["TOTAL_CRIM", "LABEL", "CLASS"])

# 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, "#", "#", "#")

# arcpy.FeatureClassToShapefile_conversion(citiesL, path)

#arcpy.CopyFeatures_management(cityListL, ouputLayer) # cityListFC)
#fieldmappings = arcpy.FieldMappings()
#fieldmappings.addTable(targetFeatures)
#fieldmappings.addTable(joinFeatures)
#NameFieldIndex = fieldmappings.findFieldMapIndex("NAME")
#fieldmap = fieldmappings.getFieldMap(NameFieldIndex)

# 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
#field = fieldmap.outputField

# Rename the field and pass the updated field object back into the field map
#field.name = "COUNTYNM"
#field.aliasName = "COUNTYNM"
#fieldmap.outputField = field
# replace old field map in mappings object with new one
#fieldmappings.replaceFieldMap(NameFieldIndex, fieldmap)

# delete fields no longer applicable
#v = fieldmappings.findFieldMapIndex("AREA")
#fieldmappings.removeFieldMap(v)
#w = fieldmappings.findFieldMapIndex("POP1990")
#fieldmappings.removeFieldMap(w)
#x = fieldmappings.findFieldMapIndex("POP_SQMILE")
#fieldmappings.removeFieldMap(x)
#y = fieldmappings.findFieldMapIndex("SQ_MILES")
#fieldmappings.removeFieldMap(y)
#z = fieldmappings.findFieldMapIndex("X")
#fieldmappings.removeFieldMap(z)
#a = fieldmappings.findFieldMapIndex("Y")
#fieldmappings.removeFieldMap(a)
#b = fieldmappings.findFieldMapIndex("X_1")
#fieldmappings.removeFieldMap(b)
#c = fieldmappings.findFieldMapIndex("Y_1")
#fieldmappings.removeFieldMap(c)
#d = fieldmappings.findFieldMapIndex("PERIMETER")
#fieldmappings.removeFieldMap(d)
#e = fieldmappings.findFieldMapIndex("GAVPRIMARY")
#fieldmappings.removeFieldMap(e)
#f = fieldmappings.findFieldMapIndex("NearRoad_OBJECTID")
#fieldmappings.removeFieldMap(f)
#g = fieldmappings.findFieldMapIndex("NearRoad_IN_FID")
#fieldmappings.removeFieldMap(g)
#h = fieldmappings.findFieldMapIndex("NearRoad_NEAR_FID")
#fieldmappings.removeFieldMap(h)

#Run the Spatial Join tool, using the defaults for the join operation and join type
# arcpy.SpatialJoin_analysis(targetFeatures, joinFeatures, outputLayer, "#", "#", fieldmappings)

# order and rename final output fields where appropriate
#arcpy.AddField_management(outfc, "INTERSTATEkm", "SHORT", "3", "1", "")
#arcpy.AddField_management(outfc, "COUNTY", "TEXT", "", "", "14")
#arcpy.AddField_management(outfc, "WORKFORCE", "LONG", "", "", "")
#arcpy.AddField_management(outfc, "FARMS", "LONG", "", "", "")
#arcpy.CalculateField_management(outfc, "INTERSTATEkm", "!NearRoad_NEAR_DIST!", "PYTHON")
#arcpy.CalculateField_management(outfc, "COUNTY", "!COUNTYNM!", "PYTHON")
#arcpy.CalculateField_management(outfc, "WORKFORCE", "!AGE_18_64!", "PYTHON")
#arcpy.CalculateField_management(outfc, "FARMS", "!NO_FARMS87!", "PYTHON")
#arcpy.DeleteField_management(outfc,["NearRoad_NEAR_DIST", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["COUNTYNM", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["AGE_18_64", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["NO_FARMS87", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["OBJECTID_1", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["Shape_Leng", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["Join_Count", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["TARGET_FID", "LABEL", "CLASS"])



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()[2])[0]
print "Python Error Info: " +  str(sys.exc_type)+ ": " + str(sys.exc_value)

# save selected features FINAL OOTPUT here
# arcpy.CopyFeatures_management(citiesL, outputLayer)

print arcpy.GetMessages()
share|improve this question
    
Adding the error would help to locate problems. At a glance, the variables in whereClause3 are not defined (workforceField, workforceindex). After that, I would add that lists and loops are your friends (exs: the sections with DeleteField and removeFieldMap). –  gm70560 Apr 11 '13 at 17:43
add comment

1 Answer 1

You combine whereClause3 and whereClause4 with the AND operator.

As gm70560 wrote, the variables workforceField, workforceindex are not defined. That's why, first you have to define them. I guess workforceindex is a double or at least a number datatype like farmfieldindex. So you have to Change:

whereClause3 = "\"%s\" = '%s'" % (workforceField, workforceindex)

to:

whereClause3 = "\"%s\" = %s" % (workforceField, workforceindex)

The whereClause4 is not defined (commented out). You have to change

whereClause = "\"%s\" = '%s'" % (farmField, farmfieldindex)

to:

whereClause4 = "\"%s\" = %s" % (farmField, farmfieldindex)

Is that right? (I removed the single quotes for the farmfieldindex)

Another reason for getting an error should be the following:

whereClause3 + "AND" + whereClause4

Is the same like: "\"%s\" = %s" + "AND" + "\"%s\" = %s"

Is the same like: "\"%s\" = %sAND\"%s\" = %s"

Thus, you have no blank between the value (workforceindex) of the first part of the where clause and the AND Operator. Change the Select Layer by Attribute Function to the following and it should work:

arcpy.SelectLayerByAttribute_management(countiesL, "NEW_SELECTION", whereClause3 + " AND " + whereClause4)

If it still not working check the delimiters in the where clause (have a look at the AddFieldDelimiters function)

share|improve this answer
    
Thanks for replying Saleika. It pointed me in the right direction and I got there using a field delimiter. –  scottaidh Apr 17 '13 at 18:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.