I have a large group of gps points that was collected. I want to select all points that have a spar depth that is greater or less than 20% that of the point that came before it and the point after.

GISID      Spar Depth

2022         4.035

2023         4.066   

2024         3.604

2025         3.576

2026         4.593

2027         2.690

2028         2.960

2029         4.59

Ex. compare the Spar Depth for Pt. 2025 to the Spar Depth for Pts 2024 & 2026

counter =0
with arcpy.da.SearchCursor('GPS Gas Main Pipe Point',[ 'GISID', 'SPAR_DEPTH']) as cursor:
     for row in cursor:
     MSDepth = row[1]
     if counter > 0:
         diff = abs(MSDepth-oldMSDepth)
         if diff > 20:
             print('GISID {}'.format(row[0]))
     oldMSDepth = row[1]
     oldGISID = row[0]
     counter+= 1

Edited Question and script:

  • Please format your code so people could easily read it. – Gob Tron Aug 31 '18 at 16:51
  • The reason no result is returned is that you haven't added any code that would update a field or output a file. Also, you would have to sort your data if you ever split a pipe or add a segment, since the rows will not keep the GISIDs together. You also should have a sequencing number or measure values for each segment to keep the segments in a specific order using the sort tool. A dictionary is the best place to organize the values under the GISID as a key with value of a dictionary of sequence number keys and spar_depth values. Then use that with an update cursor so rows can be any order – Richard Fairhurst Aug 31 '18 at 18:37
  • Could you explain what you are trying to do, "compare rows" is not enough. Are you trying to calculate fields oldMSDepth and oldGISID? – BERA Sep 1 '18 at 7:04

You can list all records, then use indexes to compare one record with the record before and after it. Add the point layer to ArcMap and execute code in python window. Im assuming GISID is a integer field or the sql line will have to be modified:

import arcpy
layer = 'GPS Gas Main Pipe Point'
fields = ['GISID', 'SPAR_DEPTH']

all_records = [i for i in arcpy.da.SearchCursor(layer,fields)] #List all records
all_records = sorted(all_records, key=lambda x: x[0]) #Sort by ID

ids_to_select = []
for index in range(1,len(all_records)-1): #Skip first and last record
    if (abs(all_records[index][1]-all_records[index-1][1])/all_records[index][1]>0.2 and abs(all_records[index][1]-all_records[index+1][1])/all_records[index][1] > 0.2):
        ids_to_select.append(all_records[index][0]) #If record >+-20% compared to previous and next record, append id to list

sql = """{0} IN({1})""".format(arcpy.AddFieldDelimiters(datasource=layer, field=fields[0]), ','.join([str(i) for i in ids_to_select])) #Build a query using ids_to_select list. For example 'GISID' IN(2023,2024)
arcpy.SelectLayerByAttribute_management(in_layer_or_view=layer, where_clause=sql)
  • would it be possible to add a definition query to the main layer before running this script or as part of the script? – M.Welch Sep 11 '18 at 19:55
  • Yes, just add it to the layer in arcmap. What query would you set? You can propably achieve the same result by using a sql query in the searchcursor in the code – BERA Sep 11 '18 at 19:58
  • there is another field called JO#, I want to make the tool dynamic so that the user can input the JO# they want to run this on without adding a query to the layer before running it. – M.Welch Sep 11 '18 at 20:11
  • Add a select layer by attributes before all_records... lines using your query. Should give the same results as a definition query – BERA Sep 11 '18 at 20:22

A couple of things.

It appears your indentation is off, but that could be when you copied the code into GIS.SE.

Since you're using the Data Access module Search Cursor, you can get the next row in the cursor using the .next() method. This code is untested, but it should show you how the method works.

with arcpy.da.SearchCursor('GPS Gas Main Pipe Point',['GISID','SPAR_DEPTH'], sql_clause=(None, 'ORDER BY GISID, ASC') as cursor:
  for row in cursor:
    msDepth = row[1]
      nextDepth = cursor.next()[1]
      diff = abs(msDepth-nextDepth)
      if diff > 20:
        print('GISID {}'.format(row[0]))
      del msDepth, nextDepth, diff
    except StopIteration:
      print("No more rows")


I saw the comment about sorting, and realized my answer didn't account for that. Added the sql_clause parameter to the cursor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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