I am trying to write a script that will go through a grid layer that has individual 535 grid squares.

I want to find a way that will tell me how much gas main is left to be checked in each grid. Any pipe newer than 2011 does not need to be checked.

I would like to write a script that could be run on a weekly basis to check the progress of the gas main layer because it is being edited by numerous users. To manually do this for grid square I usually do select by attributes with the query "Grid number = (#)", the layer has from 1 to 535, after I use select by location to select all gas main that is inside that individual grid square, then I remove the pipe that has prime and is newer than 2011, then export it to an Excel file.

I want the output of the script to create an Excel file with all remaining OIDs in the gas main layer to be validated for each individual grid square, so 535 different Excel sheets.

How do I get the value that is in the grid number field and store it as a variable to use in the select by attributes tool ("Grid_Number = (variable)" and change as it iterates through each row?

import arcpy
count = arcpy.da.UpdateCursor("keymap", "miles_left")

with count as cursor:
    if(row0]>=0 and row[0] <= 354):
        select by attributes(key map = %)
        select by location (gas main in kepy map)
        select by attributes (remove prime, remove new than 2011)
        export to excel
        update miles_left field
  • 3
    Can you post a copy of your script so that we can determine the best approach for your workflow?
    – MrBubbles
    Commented May 6, 2016 at 18:15
  • 1
    can you elaborate on your question? are you trying to find out how many features are in the grid layer? is there a reason you couldn't do an intersect and spatial join (to get the count field) between your two layers?
    – crld
    Commented May 6, 2016 at 20:20
  • The pseudo code appears to outline a very inefficient approach to this problem and you should look at alternative approaches that are far less complicated. Spatial Join should do this using the One To One option, which can do counts and sums. Whatever the step that removes prime and new than 2011 is about, it should probably be the first step if you don't want those features counted. Commented May 6, 2016 at 22:17
  • I tend to agree with Richard on this and your pseudo-code is pretty confusing. Can you revise your question to tell us what exactly you're trying to achieve? As I understand it, you have a 'grid layer' and another layer -- we'll call that 'Layer2'. You want to iterate over each feature in the grid layer and determine how many features from 'Layer2' intersect the current 'grid' in the 'grid layer'. You then want to do something else with that result but I don't understand what from your pseudo code.
    – MrBubbles
    Commented May 6, 2016 at 23:07
  • Thanks for the feedback everyone, sorry for not being clearer. I've made some edits to what I'm attempting to do so hopefully it cleared up my end goal for everyone.
    – elagarde
    Commented May 7, 2016 at 2:09

1 Answer 1


Your process is very inefficient since you do not understand how Spatial Join works. You should abandon your process to use the Spatial Join as the basis for all matching. Splitting the resulting Spatial Join output into separate Excel spreadsheets can be accomplished several ways, but the Spatial Join itself does all of the steps for the selections and matching in a single operation for all of the cells and will be at least 50 times faster than any loop that does repeated selections of one grid cell at a time using the Select by Attribute and Select by Location tool.

So the pseudo code should be:

Select by attributes on pipes (remove prime [explain], remove new than 2011)
Spatial Join with the One to Many option: Pipes = target; Grids = Join
Get unique list of Grid IDs in spatial join using Search Cursor
Loop through list to do Select by Attribute on Spatial Join output
Export to Excel.

The basic code to do this is:

import arcpy
target_features = "C:/data/mygdb.gdb/pipes"
join_features = "C:/data/mygdb.gdb/grids"
fc = "C:/data/mygdb.gdb/pipe_grid_join"
arcpy.MakeFeatureLayer_management(target_features, "pipes_lyr")

arcpy.SelectLayerByAttribute_management("pipes_lyr", "NEW_SELECTION", 'Expression that removes Prime and newer than 2011')

arcpy.SpatialJoin_analysis("pipes_lyr", join_features, fc, "JOIN_ONE_TO_MANY")
values = [row[0] for row in arcpy.da.SearchCursor(fc, ("GRID_ID"))]
uniqueValues = set(values)
arcpy.MakeFeatureLayer_management(fc, "lyr")
for value in uniqueValues:
    arcpy.SelectLayerByAttribute_management("lyr", "NEW_SELECTION", 'GRID_ID = {}'.format(value))
    arcpy.TableToExcel_conversion("lyr", "C:/data/GRID{}.xls".format(value))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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