I am writing code to iterate thru each feature in a polygon feature class and use the selected feature to clip a roads layer and calculate length. The data from the polygon feature class, and the length of the roads for each polygon is output to a CSV file. The code is working except for the arcpy.SelectLayerByAttribute_management line (line 173). This seems to break my code and I am have troubles figuring out why or coming up with a reasonable solution. Any help is greatly appreciated.
# -*- coding: utf-8 -*- # --------------------------------------------------------------------------- # 2_TrapStatsAndFloatingRoadDensity.py # Created on: 2015-4-8 1:30 pm # Completed on: 2015-4-9 # Created by: L.M.Blackburn # ArcGIS 10.1 # Notes: iterates thru orders shapefiles and creates a buffer # around each point. The round buffer is converted to a feature envelope # which makes a square polygon around the point. This square is used to # clip the roads layer. Then I calculate length for the clipped roads by adding # a field and calculating geometry. Run statistics to get the sum length # of roads in each square. export sum length, block name, centroid of # square out to a text file # (2nd script for 4th part of analysis) # Needed data: Order sublayer from VRP # List of fields to drop # Roads network to get length data # --------------------------------------------------------------------------- # Import arcpy module import arcpy, os, sys, traceback, csv # Set the necessary product code import arceditor import arcinfo # Set workspace environment - this is where the solved route layers are located arcpy.env.workspace = 'F:\\Workspace\\Sandy\\GM_costAnalysis\\analysis2\\OrdersProjected\\' # --------------------Set local variables ------------------ # ---workspace for order layers ordersLyr = 'F:\\Workspace\\Sandy\\GM_costAnalysis\\analysis2\\OrdersProjected\\' # ---workspace for buffer layers bufferLyr = 'F:\\Workspace\\Sandy\\GM_costAnalysis\\analysis2\\Buffers\\' # ---set default buffer distance bufDefault = "6000 Meters" # ---workspace for envelope layers envelopeLyr = 'F:\\Workspace\\Sandy\\GM_costAnalysis\\analysis2\\Envelopes\\' # ---set workspace path for clipped roads outDataPath = 'F:\\Workspace\\Sandy\\GM_costAnalysis\\analysis2\\ClipRoads\\' # ---set road layer - for use in clip & length Road = 'F:\\Workspace\\Sandy\\GM_costAnalysis\\RoadsForSnap\\SDC Edge Source.shp' # ---fields to delete from Orders layers dropFields = ["Descriptio", "ServiceTim", "TimeWind_1", "TimeWind_2", "TimeWind_3", "TimeWindow", "MaxViolati", "MaxViola_1", \ "PickupQuan", "DeliveryQu", "Revenue", "SpecialtyN", "Assignment", "RouteName", "ViolatedCo", "CumulTrave", \ "CumulDista", "CumulTime", "ArriveCurb", "DepartCurb", "DepartTime", "WaitTime", "ViolationT", "CumulWaitT", \ "CumulViola", "ArriveTime", "SourceID", "SourceOID", "PosAlong", "SideOfEdge", "CurbApproa", "Status"] try: # Step 1 - loop through each route layer and do the following processes print 'Opening CSV file...' # Create table for output data # Needed fields: From script [Block, Scale, Sequence, FromPrevTr, FromPrevDi, totTime, totDist, x, y, totalRdLength] CSVFile = 'F:\\Workspace\\Sandy\\GM_costAnalysis\\analysis2\\AllTrapsData.csv' f = open (CSVFile, "wb") w = csv.writer(f, delimiter=',', lineterminator='\n') fieldNames = ['Block', 'Scale', 'Sequence', 'FromPrevTr', 'FromPrevDi', 'x', 'y', 'bufferDist','totTime', \ 'totDist', 'totalRdLength', '\n'] w.writerow(fieldNames) # Step 2 - Loop through route layers and export Order sublayers to orders folder # Create variable to hold layer files orderList = arcpy.ListFiles("*.shp") # Loop through layers & break name into segments to be used when naming orders lyrs print 'Creating Buffers...' for shp in orderList: lyrs = arcpy.mapping.Layer(ordersLyr + shp) splitName =shp.split('_') block = splitName[0] scale = splitName[1].rstrip('.shp') # define buffer distance - double check the buffer distances w/ Sandy (I may need to double these values) if scale == "1K": bufDist = "500 Meters" elif scale == "2K": bufDist = "1000 Meters" elif scale == "3K": bufDist = "1500 Meters" elif scale == "4K": bufDist = "2000 Meters" elif scale == "6K": bufDist = "3000 Meters" elif scale == "500": bufDist = "250 Meters" else: bufDist = bufDefault print 'Order: ' + shp + ', Buffer: ' + bufDist # Jump into orderLayers to delete unneeded fields & add XY arcpy.DeleteField_management(shp, dropFields) arcpy.AddXY_management(shp) # Step 4 - Run buffer on order layers arcpy.Buffer_analysis(shp, bufferLyr + 'buf' + '_' + block + '_' + scale, bufDist, 'FULL', 'ROUND', 'NONE', '#') arcpy.Buffer_analysis(shp, bufferLyr + 'buf2' + '_' + block + '_' + scale, bufDefault, 'FULL', 'ROUND', 'NONE', '#') bufferList = [] for dpath, dnames, fnames in arcpy.da.Walk(bufferLyr, datatype = 'FeatureClass', type = 'Polygon'): for files in fnames: bufferList.append(os.path.join(files)) # Step 5 - Run feature envelope to polygon on buffer layers print 'Converting round buffers to squares...' for bufShp in bufferList: bufSplitName = bufShp.split('_') blockID = bufSplitName[1] scaleID = bufSplitName[2].rstrip('.shp') bufID = bufSplitName[0] print 'Buffer: ' + bufShp + ' Block: ' + blockID + ' Scale: ' + scaleID + ' BufferType: ' + bufID arcpy.FeatureEnvelopeToPolygon_management(bufferLyr + bufShp, envelopeLyr + bufID + '_' + blockID + '_' + scaleID, 'SINGLEPART') # Step 6 - Calculate totalTime and totalDistance using insert cursor # loop through each record in the table - calculate values and... # use selected features to clip roads layer & calculate geometry # add values to the CSV table print 'Populating CSV file...' envelopeList = [] for dirpath, dirnames, filenames in arcpy.da.Walk(envelopeLyr, datatype = 'FeatureClass', type = 'Polygon'): for filename in filenames: envelopeList.append(os.path.join(filename)) for eLayer in envelopeList: dataList = [] eLayerName = eLayer.split('_') blockEID = eLayerName[1] scaleEID = eLayerName[2].rstrip('.shp') bufEID = eLayerName[0] dataList.append(str(blockEID)) dataList.append(str(scaleEID)) print 'eLayer: ' + eLayer + ' Block: ' + blockEID + ' Scale: ' + scaleEID + ' BufferType: ' + bufEID eLyrPath = envelopeLyr + eLayer #Make a layer from the feature class - needed for selecting records to be used in the clipping arcpy.MakeFeatureLayer_management(eLyrPath, "clipLayer") # use search cursor to grab data row by row with arcpy.da.SearchCursor(eLyrPath, ("Sequence", "FromPrevTr", "FromPrevDi", "POINT_X", "POINT_Y", "BUFF_DIST")) as cursor: for row in cursor: dataList.append(str(row[0])) dataList.append(str(row[1])) dataList.append(str(row[2])) dataList.append(str(row[3])) dataList.append(str(row[4])) dataList.append(str(row[5])) n = int(row[0]) n2 = n +1 sel = "=" + str(n2) prevTime = float(row[1]) prevDist = float(row[2]) # rQuery = '"Sequence" = \''+ n +'\'' ---this line throws errors--- expression = arcpy.AddFieldDelimiters(eLyrPath, "Sequence")+ sel qrow = arcpy.SearchCursor(eLyrPath, where_clause=expression, fields = "FromPrevTr; FromPrevDi") row2 = qrow.next() while row2: postTime = row2.getValue("FromPrevTr") # print postTime totTime = prevTime + float(postTime) # print totTime dataList.append(str(totTime)) postDist = row2.getValue("FromPrevDi") # print postDist totDist = prevDist + float(postDist) # print totDist dataList.append(str(totDist)) row2 = qrow.next() # select single record to use when clipping # 4/9/15 - having issues with this line arcpy.SelectLayerByAttribute_management("clipLayer", "NEW_SELECTION", '"Sequence" = \''+ n +'\'') # Clip roads w/ selected polygon - for this I will need a search cursor clipRoads = outDataPath + 'rdsClip_' + blockEID + '_' + scaleEID + '_' + bufEID + '_' + str(n) arcpy.Clip_analysis(Road, "clipLayer", clipRoads) clipRoadsShp = clipRoads + ".shp" # Use geometry/length to get the total length of clipped roads(in meters) g = arcpy.Geometry() geometryList = arcpy.CopyFeatures_management(clipRoadsShp, g) length = 0 for geometry in geometryList: length +=geometry.length # append length (meters) at end of line to csv dataList dataList.append(str(length)) # print length # Write dataList to csv file ---- may need to dedent this one more time --- w.writerow(dataList) # print dataList print 'Script complete' f.close() except: if arcpy.Exists(CSVFile): w.writerow(dataList) print 'Data written in CSV file' f.close() print 'Program failed.' print 'Check python errors.' tb = sys.exc_info()[2] tbinfo = traceback.format_tb(tb)[0] pymsg = "PYTHON ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n " + str(sys.exc_type) + ": " + str(sys.exc_value) + "\n" msgs = "ARCPY ERRORS:\n" + arcpy.GetMessages(2) + "\n" arcpy.AddError(msgs) arcpy.AddError(pymsg) print msgs print pymsg arcpy.AddMessage(arcpy.GetMessages(1)) print arcpy.GetMessages(1) # print ("Points to select: {0}, Radius: {1}".format(centroidPath, radius))
Solved! Go to Solution.
One of the reasons for the select by attribute statement to throw an error (not looking at the cursor) is this:
In your original code you used this:
print '"Sequence" = \''+ n +'\''
Which in my case will throw this error if n is integer (which I deduce from your code)
Traceback (most recent call last): File "<module1>", line 2, in <module> TypeError: cannot concatenate 'str' and 'int' objects
If n would be string:
n = '1' print '"Sequence" = \''+ n +'\''
... you get this:
"Sequence" = '1'
In the code you changed you do this:
seqField = "Sequence" where_clause = "{} = '{}'".format(seqField, n)
... which will yield (is n = '1') this:
# Sequence = '1'
Since the syntax of the where clause depends on the data source (shapefile, PGDB, FGDB, EGDB, etc) you should use the AddFieldDelimiters to make sure that the field name will end up correctly in the where clause:
where = "{0} = '{1}'".format(arcpy.AddFieldDelimiters("clipLayer", seqField), n)
... this is assuming that "clipLayer" can be found by the system and that seqField is a text field.
What exactly is the error code it is throwing?
I would guess you are having an issue with your query statement, check this post Python - Select by attributes query issue. Joshua Bixby shows a great example of using python string formatting for query statements. You are using string formatting in your code, so it should be simple to adapt it for your query statement.
Hope this helps
Here are my python errors:
ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).
PYTHON ERRORS:
Traceback Info:
File "F:\Workspace\Sandy\GM_costAnalysis\scripts\Scripts_GMSurvellience-Analysis 4\2_TrapStatsAndFloatingRoadDensity.py", line 172, in <module>
arcpy.SelectLayerByAttribute_management("clipLayer", "NEW_SELECTION", '"Sequence" = \''+ n +'\'')
Error Info:
<class 'Queue.Empty'>:
Here is the latest version of the selectLayerByAttribute call
seqField = "Sequence" where_clause = "{} = '{}'".format(seqField, n) arcpy.SelectLayerByAttribute_management("clipLayer", "NEW_SELECTION", where_clause)
I think the issue is likely the syntax of the where clause. Though, I tried using formats exactly like that from the ArcGIS help. The other issue that it may be - is that I am performing this selection inside of a cursor loop. Any help or suggestions are greatly appreciated!
Not sure if this will solve your problem or not, but old-style cursors often behave unexpectedly (or predictably badly) when nested. Try converting the inside cursor to a da.SearchCursor.
Thanks Darren - I will try that and see what happens. I was a bit confused by the two different styles of search cursors when I started writing the code.
When in doubt, use a da cursor.
One of the reasons for the select by attribute statement to throw an error (not looking at the cursor) is this:
In your original code you used this:
print '"Sequence" = \''+ n +'\''
Which in my case will throw this error if n is integer (which I deduce from your code)
Traceback (most recent call last): File "<module1>", line 2, in <module> TypeError: cannot concatenate 'str' and 'int' objects
If n would be string:
n = '1' print '"Sequence" = \''+ n +'\''
... you get this:
"Sequence" = '1'
In the code you changed you do this:
seqField = "Sequence" where_clause = "{} = '{}'".format(seqField, n)
... which will yield (is n = '1') this:
# Sequence = '1'
Since the syntax of the where clause depends on the data source (shapefile, PGDB, FGDB, EGDB, etc) you should use the AddFieldDelimiters to make sure that the field name will end up correctly in the where clause:
where = "{0} = '{1}'".format(arcpy.AddFieldDelimiters("clipLayer", seqField), n)
... this is assuming that "clipLayer" can be found by the system and that seqField is a text field.
Thanks Xander, Darren and Ian!
Xander, you were correct - it was the fact that n was an integer. That and the problem of syntax in the where clause. So I ended up doing the following and now my code works.
sN = "=" + str(n) seqField = "Sequence" where_clause = arcpy.AddFieldDelimiters("clipLayer", seqField)+ sN arcpy.SelectLayerByAttribute_management("clipLayer", "NEW_SELECTION", where_clause)