Latest Contributions by ScottMacDonald

Hi JSkinn.  The line of code you suggested below for the if .. else.. condition didn't work after all.  It only worked when I changed the and to an or but it didn't solve the problem if a user types in 0 or anything else.  It goes into a loop.  Do you perhaps have any other suggestions? if isinstance(maxKmInterstate, (float, int)) and maxKmInterstate > 0:     <code> else:     <code> # import system modules import arcpy, os, arcpy, string 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 cities2 = "cities2" citiesL = "citiesL" counties = arcpy.GetParameterAsText(2) # counties Layer is counties Feature Layer counties.shp counties2 = "counties2" countiesL ="countiesL" interstates = arcpy.GetParameterAsText(3) # interstates Layer is Feature Layer interstates.shp interstates2 = "interstates2" interstatesL = "interstatesL" crimeField = arcpy.GetParameterAsText(4) # crimeField is fieldname 'CRIME_INDE' SQL expression crimeval = arcpy.GetParameterAsText(5) # Double 0.02 whereClause = "{0} <= {1}".format(arcpy.AddFieldDelimiters(citiesL, crimeField), crimeval) universityField = arcpy.GetParameterAsText(6) # universityField is fieldname 'UNIVERSITY' SQL expression universityval = arcpy.GetParameterAsText(7) # universityfieldindex is the UNIVERSITY field and is string integer 1 whereClause2 = "{0} = {1}".format(arcpy.AddFieldDelimiters(citiesL, universityField), universityval) workforceField = arcpy.GetParameterAsText(8) # workforceField is fieldname 'AGE_18_64' SQL expression workforceval = arcpy.GetParameterAsText(9) # Double and is 25000 whereClause3 = "{0} >= {1}".format(arcpy.AddFieldDelimiters(countiesL, workforceField), workforceval) farmField = arcpy.GetParameterAsText(10) # farmField is fieldname 'NO_FARMS87' SQL expression farmval = arcpy.GetParameterAsText(11) # Double integer is 500 whereClause4 = "{0} >= {1}".format(arcpy.AddFieldDelimiters(countiesL, farmField), farmval) maxKmInterstate = arcpy.GetParameterAsText(12) # interstate WITHIN_A_DISTANCE linear unit nearestInterstate = arcpy.GetParameterAsText(13) # Near Table Analysis table generated newFieldName = "DISTANCE" citylist = arcpy.GetParameterAsText(14) # narrowed down cities list temporary and set to derived to hide citylistL = "citylistL" # the temporary feature layer of the generated cityList feature class outputLayer = arcpy.GetParameterAsText(15) # oot layer set to derived outputTable = arcpy.GetParameterAsText(16) # output table # make cities copy and cities feature layer and remove unnecessary fields arcpy.CopyFeatures_management(cities, cities2) arcpy.MakeFeatureLayer_management(cities2, citiesL) arcpy.DeleteField_management(citiesL, ["POPULATION", "TOTAL_CRIM"]) # select only user chosen fields for cities whereClause if crimeField == "CRIME_INDE" and universityField == "UNIVERSITY": arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause + " AND " + whereClause2) arcpy.AddMessage("Maximum crime index threshold and cities with universities selected") elif crimeField == "CRIME_INDE" and universityField <> "UNIVERSITY": arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause) arcpy.AddMessage("Maximum crime index threshold selected") arcpy.DeleteField_management(citiesL, "UNIVERSITY") arcpy.CopyFeatures_management(citiesL, citylist) elif crimeField <> "CRIME_INDE" and universityField == "UNIVERSITY": arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause2) arcpy.AddMessage("Cities with a university selected") arcpy.DeleteField_management(citiesL, "CRIME_INDE") arcpy.CopyFeatures_management(citiesL, citylist) elif crimeField <> "CRIME_INDE" and universityField <> "UNIVERSITY": arcpy.AddMessage("No crime threshold or cities with universities selected") arcpy.DeleteField_management(citiesL, ["CRIME_INDE", "UNIVERSITY"]) arcpy.CopyFeatures_management(citiesL, citylist) # make counties feature layer and remove unnecessary fields arcpy.CopyFeatures_management(counties, counties2) arcpy.MakeFeatureLayer_management(counties2, countiesL) arcpy.DeleteField_management(countiesL, ["AREA", "PERIMETER", "POP1990", "POP_SQMILE", "SQ_MILES", "GAVPRIMARY", "X", "Y", "X_1", "Y_1"]) # select only user chosen fields for counties whereClause if workforceField == "AGE_18_64" and farmField == "NO_FARMS87": arcpy.SelectLayerByAttribute_management(countiesL, "NEW_SELECTION", whereClause3 + " AND " + whereClause4) arcpy.AddMessage("Minimum workforce and proximity to farms selected") arcpy.CopyFeatures_management(countiesL, "countylist") elif workforceField == "AGE_18_64" and farmField <> "NO_FARMS87": arcpy.SelectLayerByAttribute_management(countiesL, "NEW_SELECTION", whereClause3) arcpy.AddMessage("Minimum workforce selected") arcpy.DeleteField_management(countiesL, "NO_FARMS87") arcpy.CopyFeatures_management(countiesL, "countylist") elif workforceField <> "AGE_18_64" and farmField == "NO_FARMS87": arcpy.SelectLayerByAttribute_management(countiesL, "NEW_SELECTION", whereClause4) arcpy.AddMessage("Proximity to farms selected") arcpy.DeleteField_management(countiesL, "AGE_18_64") arcpy.CopyFeatures_management(countiesL, "countylist") elif workforceField <> "AGE_18_64" and farmField <> "NO_FARMS87": arcpy.AddMessage("No minimun workforce or proximity to farms selected") arcpy.DeleteField_management(countiesL, ["AGE_18_64", "NO_FARMS87"]) arcpy.CopyFeatures_management(countiesL, "countylist") # from selection above select cities intersecting counties arcpy.SelectLayerByLocation_management(citiesL, "INTERSECT", "countylist", "", "SUBSET_SELECTION") # create a city list if maximum distance to interstates selected if isinstance(maxKmInterstate, (float,int)) or maxKmInterstate > 0: arcpy.AddMessage("Maximum distance to interstates selected") # copy interstates feature class so original is unaffected arcpy.CopyFeatures_management(interstates, interstates2) # make interstates feature layer arcpy.MakeFeatureLayer_management(interstates2, interstatesL) # further select cities within maximum interstate distance arcpy.SelectLayerByLocation_management(citiesL, "WITHIN_A_DISTANCE", interstatesL, maxKmInterstate, "SUBSET_SELECTION") # copy to citylist arcpy.CopyFeatures_management(citiesL, citylist) # create a feature layer from new citylist arcpy.MakeFeatureLayer_management(citylist, citylistL) # Generate NearTable_analysis to find closest interstate distance arcpy.GenerateNearTable_analysis(citylistL, interstates, nearestInterstate, maxKmInterstate) # add new DISTANCE field as short integer arcpy.AddField_management(nearestInterstate, newFieldName, "SHORT") #Calculates the new DISTANCE field by dividing metres by 1000 arcpy.CalculateField_management(nearestInterstate, newFieldName, "!NEAR_DIST! / 1000", "PYTHON", "") # join city list to nearest interstate table arcpy.JoinField_management(citylistL, "FID", nearestInterstate, "IN_FID", "DISTANCE") # spatially join the narrowed down city/road and counties layers remove unwanted fields and rename others arcpy.SpatialJoin_analysis(citylistL, countiesL, outputLayer, "#", "#", "") else: arcpy.AddMessage("No maximum distance to interstates selected") arcpy.CopyFeatures_management(citiesL, citylist) arcpy.MakeFeatureLayer_management(citylist, citylistL) arcpy.SpatialJoin_analysis(citylistL, countiesL, outputLayer, "#", "#", "") arcpy.AddMessage("Creating City table based on selections.......") # Set data path intable = outputLayer # Get the fields from the input fields= arcpy.ListFields(intable) # Create a fieldinfo object fieldinfo = arcpy.FieldInfo() # Iterate through the fields and set them to fieldinfo for field in fields:     if field.name == "NAME":         fieldinfo.addField(field.name, "CITYNM", "VISIBLE", "")     elif field.name == "CRIME_INDE":         fieldinfo.addField(field.name, "CRIMEINDX", "VISIBLE", "")     elif field.name == "UNIVERSITY":         fieldinfo.addField(field.name, field.name, "VISIBLE", "")     elif field.name == "DISTANCE":         fieldinfo.addField(field.name, "INTRSTkm", "VISIBLE", "")     elif field.name == "NAME_1":         fieldinfo.addField(field.name, "COUNTYNM", "VISIBLE", "")     elif field.name == "AGE_18_64":         fieldinfo.addField(field.name, "WORKFRCE", "VISIBLE", "")     elif field.name == "NO_FARMS87":         fieldinfo.addField(field.name, "FARMS", "VISIBLE", "")  #elif field.name == "Join_Count":     #    fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "TARGET_FID":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "OBJECTID":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "Shape_Leng":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "Shape_Le_1":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "Shape_Area":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "ID":         fieldinfo.addField(field.name, field.name, "HIDDEN", "") # The created city view layer will have fields as set in fieldinfo object arcpy.MakeTableView_management(intable, "CityView", "", "", fieldinfo) # To persist the layer on disk make a copy of the view arcpy.CopyRows_management("CityView", outputTable) arcpy.AddMessage("Your City Table file has been created")   print arcpy.GetMessages() ... View more

Hi JSKinn - The code you sent to solve the if else problem  (isinstance) doesn't work when the user inputs 0 Kilometers - it goes into a loop and doesn't stop.  Do you know a way round this?  code below.  # import system modules import arcpy, os, arcpy, string 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 cities2 = "cities2" citiesL = "citiesL" counties = arcpy.GetParameterAsText(2) # counties Layer is counties Feature Layer counties.shp counties2 = "counties2" countiesL ="countiesL" interstates = arcpy.GetParameterAsText(3) # interstates Layer is Feature Layer interstates.shp interstates2 = "interstates2" interstatesL = "interstatesL" crimeField = arcpy.GetParameterAsText(4) # crimeField is fieldname 'CRIME_INDE' SQL expression crimeval = arcpy.GetParameterAsText(5) # crime index is CRIME_INDE and is a Double 0.02 whereClause = "{0} <= {1}".format(arcpy.AddFieldDelimiters(citiesL, crimeField), crimeval) universityField = arcpy.GetParameterAsText(6) # universityField is fieldname 'UNIVERSITY' SQL expression universityval = arcpy.GetParameterAsText(7) # universityfieldindex is the UNIVERSITY field and is string integer 1 whereClause2 = "{0} = {1}".format(arcpy.AddFieldDelimiters(citiesL, universityField), universityval) workforceField = arcpy.GetParameterAsText(8) # workforceField is fieldname 'AGE_18_64' SQL expression workforceval = arcpy.GetParameterAsText(9) # workforce index is attribute of AGE_18_64 field and is a Double and is 25000 whereClause3 = "{0} >= {1}".format(arcpy.AddFieldDelimiters(countiesL, workforceField), workforceval) farmField = arcpy.GetParameterAsText(10) # farmField is fieldname 'NO_FARMS87' SQL expression farmval = arcpy.GetParameterAsText(11) # farmfieldindex is the NO_FARMS87 field and is Double integer is 500 whereClause4 = "{0} >= {1}".format(arcpy.AddFieldDelimiters(countiesL, farmField), farmval) maxKmInterstate = arcpy.GetParameterAsText(12) # interstate WITHIN_A_DISTANCE linear unit nearestInterstate = arcpy.GetParameterAsText(13) # Near Table Analysis table generated newFieldName = "DISTANCE" citylist = arcpy.GetParameterAsText(14) # narrowed down cities list temporary and set to derived to hide citylistL = "citylistL" # the temporary feature layer of the generated cityList feature class outputLayer = arcpy.GetParameterAsText(15) # oot layer set to derived outputTable = arcpy.GetParameterAsText(16) # output table # make cities feature layer and remove unnecessary fields arcpy.CopyFeatures_management(cities, cities2) arcpy.MakeFeatureLayer_management(cities2, citiesL) arcpy.DeleteField_management(citiesL, ["POPULATION", "TOTAL_CRIM"]) if crimeField == "CRIME_INDE" and universityField == "UNIVERSITY": arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause + " AND " + whereClause2) arcpy.AddMessage("Maximum crime index threshold and cities with universities selected") elif crimeField == "CRIME_INDE" and universityField <> "UNIVERSITY": arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause) arcpy.AddMessage("Maximum crime index threshold selected") arcpy.DeleteField_management(citiesL, "UNIVERSITY") arcpy.CopyFeatures_management(citiesL, citylist) elif crimeField <> "CRIME_INDE" and universityField == "UNIVERSITY": arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause2) arcpy.AddMessage("Cities with a university selected") arcpy.DeleteField_management(citiesL, "CRIME_INDE") arcpy.CopyFeatures_management(citiesL, citylist) elif crimeField <> "CRIME_INDE" and universityField <> "UNIVERSITY": arcpy.AddMessage("No crime threshold or cities with universities selected") arcpy.DeleteField_management(citiesL, ["CRIME_INDE", "UNIVERSITY"]) arcpy.CopyFeatures_management(citiesL, citylist) # make counties feature layer and remove unnecessary fields arcpy.CopyFeatures_management(counties, counties2) arcpy.MakeFeatureLayer_management(counties2, countiesL) arcpy.DeleteField_management(countiesL, ["AREA", "PERIMETER", "POP1990", "POP_SQMILE", "SQ_MILES", "GAVPRIMARY", "X", "Y", "X_1", "Y_1"]) if workforceField == "AGE_18_64" and farmField == "NO_FARMS87": arcpy.SelectLayerByAttribute_management(countiesL, "NEW_SELECTION", whereClause3 + " AND " + whereClause4) arcpy.AddMessage("Minimum workforce and proximity to farms selected") arcpy.CopyFeatures_management(countiesL, "countylist") elif workforceField == "AGE_18_64" and farmField <> "NO_FARMS87": arcpy.SelectLayerByAttribute_management(countiesL, "NEW_SELECTION", whereClause3) arcpy.AddMessage("Minimum workforce selected") arcpy.DeleteField_management(countiesL, "NO_FARMS87") arcpy.CopyFeatures_management(countiesL, "countylist") elif workforceField <> "AGE_18_64" and farmField == "NO_FARMS87": arcpy.SelectLayerByAttribute_management(countiesL, "NEW_SELECTION", whereClause4) arcpy.AddMessage("Proximity to farms selected") arcpy.DeleteField_management(countiesL, "AGE_18_64") arcpy.CopyFeatures_management(countiesL, "countylist") elif workforceField <> "AGE_18_64" and farmField <> "NO_FARMS87": arcpy.AddMessage("No minimun workforce or proximity to farms selected") arcpy.DeleteField_management(countiesL, ["AGE_18_64", "NO_FARMS87"]) arcpy.CopyFeatures_management(countiesL, "countylist") # from selection above select cities intersecting counties arcpy.SelectLayerByLocation_management(citiesL, "INTERSECT", "countylist", "", "SUBSET_SELECTION") # create a city list if maximum distance to interstates selected if isinstance(maxKmInterstate, (float, int)) or maxKmInterstate > 0: arcpy.AddMessage("Maximum distance to interstates selected") # copy interstates feature class so original is unaffected arcpy.CopyFeatures_management(interstates, interstates2) # make interstates feature layer arcpy.MakeFeatureLayer_management(interstates2, interstatesL) # further select cities within maximum interstate distance arcpy.SelectLayerByLocation_management(citiesL, "WITHIN_A_DISTANCE", interstatesL, maxKmInterstate, "SUBSET_SELECTION") # copy to citylist arcpy.CopyFeatures_management(citiesL, citylist) # create a feature layer from new citylist arcpy.MakeFeatureLayer_management(citylist, citylistL) # Generate NearTable_analysis to find closest interstate distance arcpy.GenerateNearTable_analysis(citylistL, interstates, nearestInterstate, maxKmInterstate) # add new DISTANCE field as short integer arcpy.AddField_management(nearestInterstate, newFieldName, "SHORT") #Calculates the new DISTANCE field by dividing metres by 1000 arcpy.CalculateField_management(nearestInterstate, newFieldName, "!NEAR_DIST! / 1000", "PYTHON", "") # join city list to nearest interstate table arcpy.JoinField_management(citylistL, "FID", nearestInterstate, "IN_FID", "DISTANCE") # spatially join the narrowed down city/road and counties layers remove unwanted fields and rename others arcpy.SpatialJoin_analysis(citylistL, countiesL, outputLayer, "#", "#", "") else: arcpy.AddMessage("No maximum distance to interstates selected") # create city list where no maximum interstate distance selected arcpy.CopyFeatures_management(citiesL, citylist) # create a feature layer from new citylist arcpy.MakeFeatureLayer_management(citylist, citylistL) # spatially join the narrowed down city/road and counties layers remove unwanted fields and rename others arcpy.SpatialJoin_analysis(citylistL, countiesL, outputLayer, "#", "#", "") # update citylist with relevant fields from counties join arcpy.AddMessage("Creating City table based on selections.......") # Set data path intable = outputLayer # Get the fields from the input fields= arcpy.ListFields(intable) # Create a fieldinfo object fieldinfo = arcpy.FieldInfo() # Iterate through the fields and set them to fieldinfo for field in fields:     if field.name == "NAME":         fieldinfo.addField(field.name, "CITYNM", "VISIBLE", "")     elif field.name == "CRIME_INDE":         fieldinfo.addField(field.name, "CRIMEINDX", "VISIBLE", "")     elif field.name == "UNIVERSITY":         fieldinfo.addField(field.name, field.name, "VISIBLE", "")     elif field.name == "DISTANCE":         fieldinfo.addField(field.name, "INTRSTkm", "VISIBLE", "")     elif field.name == "NAME_1":         fieldinfo.addField(field.name, "COUNTYNM", "VISIBLE", "")     elif field.name == "AGE_18_64":         fieldinfo.addField(field.name, "WORKFRCE", "VISIBLE", "")     elif field.name == "NO_FARMS87":         fieldinfo.addField(field.name, "FARMS", "VISIBLE", "")  #elif field.name == "Join_Count":     #    fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "TARGET_FID":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "OBJECTID":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "Shape_Leng":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "Shape_Le_1":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "Shape_Area":         fieldinfo.addField(field.name, field.name, "HIDDEN", "")     elif field.name == "ID":         fieldinfo.addField(field.name, field.name, "HIDDEN", "") # The created city view layer will have fields as set in fieldinfo object arcpy.MakeTableView_management(intable, "CityView", "", "", fieldinfo) # To persist the layer on disk make a copy of the view arcpy.CopyRows_management("CityView", outputTable) arcpy.AddMessage("Your City Table file has been created")   print arcpy.GetMessages() ... View more

Thanks JSkinn3, That line of code worked perfectly, thanks for the answer and for replying so quickly - much appreciated. I'll make sure I use the correct    tags next time. Regards Scottaidh ... View more

Thank you all very much for taking the time to reply. The line that worked in the end was a Field Delimiter where clause crimeField = arcpy.GetParameterAsText(4) # crimeField is fieldname 'CRIME_INDE' SQL expression crimeval = arcpy.GetParameterAsText(5) # crime index is CRIME_INDE and is a Double 0.02 whereClause = "{0} <= {1}".format(arcpy.AddFieldDelimiters(citiesL, crimeField), crimeval) [\CODE] Previously, I had two extra ' single abbreviations around the where clause 0 and 1 which made the expression something like ""CRIME_INDE"" = "0.02" so watch out for them!  Thanks again for the help. Scottaidh ... View more

Thank you all very much for taking the time to reply. The line that worked in the end was a Field Delimiter where clause crimeField = arcpy.GetParameterAsText(4) # crimeField is fieldname 'CRIME_INDE' SQL expression crimeval = arcpy.GetParameterAsText(5) # crime index is CRIME_INDE and is a Double 0.02 whereClause = "{0} <= {1}".format(arcpy.AddFieldDelimiters(citiesL, crimeField), crimeval) [\CODE] Previously, I had two extra ' single abbreviations around the where clause 0 and 1 which made the expression something like ""CRIME_INDE"" = "0.02" so watch out for them!  Thanks again for the help. Scottaidh ... View more

Thanks for the reply.  Can you tell me how to do that?  Would they speak to a student before the deadline tomorrow? I don't think I should get my hopes up but wish I had done it a few days ago. Would you mind asking any of your colleagues if they know how to do it? Anyway, I've pasted my cleaned up code in the hope that some one has the answer.  Again the ONLY part that doesn't work is the where clause/SelectLayerByAttrribute parts.  Thanks again m8 # import system modules import arcpy, os, arcpy, traceback 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 + '" <= " + crimefieldindex' #whereClause = '"' + crimeField + '" <= ' + "'" + crimefieldindex + "'" 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 = "'"' + universityField + '" <=' + universityindex" #whereClause2 = '"' + universityField + '" <= ' + "'" + universityfieldindex + "'" 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 = "'"' + workforceField + '" >=' + 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 = "citiesL" # 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 <= 0.02" #altwhereclause2 = "UNIVERSITY = 1" #altwhereclause3 = "AGE_18_64 >= 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 one of  ESRI forums suggested code for select layer by attribute where clause - doesn't work though # whereClause = '"' + crimeField + '" <=' + "'" + crimefieldindex + "'" # arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause + "AND" + whereClause2) # GIS stack exchange suggestion 4 above - whereClause = '"' + crimeField + '" = ' + "'" + crimefieldindex + "'" # from selection above select layer by attribute select "CRIME_IND" <= 0.02 AND "UNIVERSITY" = 1 # arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause + "AND" + whereClause2) # make counties feature layer arcpy.MakeFeatureLayer_management(counties,countiesL) # select workforce and number of farms # new selection on counties layer countiesL  "AGE_18_64" >= 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") # 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, "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, "#", "#", "#") [\CODE] ... View more

Hi thanks for your reply mvolz. I wrote perfect working code last week that is hard-coded. I have already converted 95% of it to generic and it all works apart from the where clause expression. That's the whole problem! Any help with the syntax or maybe even how to print out what it's doing might help.  Is there any way to show exactly what its doing? I can't think of anything else I can do and it's still not right.  I'll keep working on it until the deadline.  All I want is "CRIME_INDE" <= 0.02 AND "UNIVERSITY" = 1 and "AGE_18_64" >= 25000 AND "NO_FARMS87" >=500 That's it. Nothing else. Everything works apart from that! I can even # out the two GetLayerByAttribute lines and it works without the selected features.  It's ONLY the above two statements that are preventing the whole thing from running as perfectly as the hard coded version I wrote last week. Thanks again for you reply. Scottaidh ... View more

Thanks for the answer Jake. I done that last week. I have perfect working code that is hard coded.  The whole problem is the substitution of hard coded code to generic variable user input code. I still can't get the right where clause construction at all and have been at it for days! Thanks though Scottaidh ... View more

Thanks mvolz47. citiesL = "citiesL" This script is for a tool in toolbox so all the workspaces, files etc. are put in by the user in GetParameterAsText - hence no workspace shown etc. Also, SelectlayerByLocation is not the problem - that works fine.  The problem is the SQL syntax in the where clause for the SelectLayerByAttribute code. If the user is inputting the fieldnames and values (they're variables) how do you write the SQL statement for say "CRIME_INDE" <= 0.02 AND "UNIVERSITY" = 1 It's that simple (or not as it may be) but I can't write the SQL for that and neither it seems can the majority.  I've checked other posts, manuals, asking on here and no one can give me a working answer! I appreciate your help but all I need is the whereclause sql code - that's it.  Everything works apart from this - the other issues are not the problem. Can anyone help as I've got a deadline for tomorrow? Thanks Scott ... View more

Thanks JSkinn for your reply I really appreciate it. Unfortunately the code still doesn't work and I get the following error message- <class 'arcgisscripting.ExecuteError'>: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute). Failed to execute (tryWC2). Failed at Thu Apr 11 14:55:11 2013 (Elapsed Time: 2.00 seconds) Please, do you have any more suggestions to what seems to be a typical problem?  Unfortunately I can't find appropriate code in other messages. I've posted my code below with your suggestion in it, please ignore all the add, delete and field mappings etc. as I will get round to them once the select layer by attribute works. Everything works apart from select layer by attributes, well in fact, that does work when nothings selected so it is just the SQL code that I can't get right. Any help would be appreciated and thanks again for your suggestion, I thought that was it! Thanks Scott # 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 + '" <=' + "'" + 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 = ('"' + 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 = ('"' + 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 whereClause3 = ('"' + 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")   # from selection above select layer by attribute select "CRIME_IND" <= 0.02 AND "UNIVERSITY" = 1 arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause + "AND" + whereClause2) # make counties feature layer arcpy.MakeFeatureLayer_management(counties,countiesL) # select workforce and number of farms # new selection on counties layer countiesL  "AGE_18_64" >= 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() [\CODE] ... View more