using multiple cursors

5884
18
04-15-2016 10:02 AM
ChrisLowrie
New Contributor

I need to be able to search through a set of data points, each with a time stamp, and insert rows between two data points that are more than 5 seconds apart.  The first pass with the cursor is used to turn the time stamp into an integer so that I can work with it, but after that it won't reset and attempts to open another cursor, even after deleting the first, don't work.  Any ideas of how to do this?

0 Kudos
18 Replies
RichardFairhurst
MVP Honored Contributor

It is good practice to only attempt one pass with an update cursor to completion, then after that use a new cursor to do any inserts.  You do not have to create a new field to work with the time stamp as an integer, and could instead do this within a python dictionary or list in memory, so that you would use a search cursor to load it all into memory, analyze it there, then use an insert cursor to create the new points.  That way no conflict between cursors would occur.  But without more info I cannot propose any code.  Post the code you are attempting so we have some idea of what you are actually trying.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

a few lines of the table would also be interesting as well.  Given your description, it doesn't appear that you will be joining the table back to anything, since the row insertion would foul up the current ID system.  options abound

0 Kudos
ChrisLowrie
New Contributor

Thanks!

Here is what I have so far.  It creates a report of where the gaps are, but I can't get a second cursor to exist.

def process_table():#create the hms and day fields, create lists and totals for the summaries
#Creates a cursor to iterate through the table, grabs the field we are interested in
rows = arcpy.UpdateCursor(table)
#Create initial conditions for the previous row, and a bool for the first iteration
prev_t = 0
prev_x = 0
prev_y = 0
prev_day = ""
first = True
dic = {}
total_gaps = 0
over_2 = 0
over_5 = 0
over_100_meters = 0
delta_t = 0
extremes_lst = []
for row in rows:
full_date = row.getValue(date_fld) #Get the date and time
date_lst = full_date.split("T")   #Split into date and time
row.setValue("Day", str(date_lst[0]))#Set the date to be the first half
full_time = date_lst[1]#Full time is the second
time_lst = full_time.split(":")#Split the time
hr = int(time_lst[0])#into hours
mn = int(time_lst[1])#into minutes
sec = int(time_lst[2][0:2])#into seconds, cut off the "Z"
hms = 3600*hr + 60*mn + sec           #make the integer time
row.setValue("HMS", hms)#store the integer time
rows.updateRow(row)#save the row
if first != True:
if hms - prev_t> 5:

distance = calculate_distance(prev_x, row.getValue("Longitude"), prev_y, row.getValue("Latitude"))
rows_to_add = (hms-prev_t)/5 - 1
total_gaps += rows_to_add
delta_t = str((hms-prev_t)/60) + " minutes and " + str((hms-prev_t)%60) + " seconds"
to_add = (rows_to_add, delta_t, distance)
fid_lst.append(  (int(row.getValue("FID"))-1,  rows_to_add,  (prev_x, row.getValue("Longitude")),    (prev_y, row.getValue("Latitude")),  distance)  )
#0: Row's FID
#1: Number of rows to add
#2: Tuple of x's
#3: Tuple of y's
#4: Distance

if distance > 100:
over_100_meters +=1
extremes_lst.append(to_add)
if rows_to_add > 24:
over_2 +=1
if rows_to_add > 60:
over_5 +=1
if to_add not in extremes_lst:
extremes_lst.append(to_add)

if rows_to_add not in dic:
dic[rows_to_add] = 1
else:
dic[rows_to_add] += 1

#Update the previous parameters
first = False
prev_t = hms
prev_x = row.getValue("Longitude")
prev_y = row.getValue("Latitude")
prev_day = row.getValue("Day")
return extremes_lst, dic, over_2, over_5, over_100_meters, total_gaps, fid_lst

From this I've been trying to use fid_lst to find where to insert rows.  Something like:

insert = arcpy.InsertCursor(table)

for row in insert:

     if row.getValue("FID") == fid_lst[0][0]:  #the fid

          rows_to_add = fid_lst[0][1]   #the rows to add

          for x in range(1, rows_to_add + 1)

               #add new rows

          fid_lst.pop(0)

          for i in fid_lst:

               i[0] += rows_to_add    #accounts for the shift in FID from adding the new rows

Any advice is appreciated!

Chris

0 Kudos
ChrisLowrie
New Contributor

I actually just went back through and researched the arcpy.da.InsertCursor object.  It inserts rows now, but is there a way to insert the row in the middle of the table instead of at the end?

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

first Code Formatting... the basics++

too hard to dissect what is going on

RichardFairhurst
MVP Honored Contributor

You should also change any search or update cursor to the da version, since they are 10 times faster than the old style Python cursors.

Unfortunately the answer is no, you can't insert new records between existing records within an existing feature class.  ObjectIDs are issued consecutively and cannot be shifted and if deleted they cannot be reused.  The only safe way to get the records organized based on attributes is by using the Sort tool, which will create a new feature class.  This tool resets all ObjectIDs in the new feature class to fit the new order and removes any ObjectID gaps that were created by deleted records.  You then would replace the original FC with the new sorted FC.

If you really insist on not creating a new feature class and are willing to put all of your data at risk, then after inserting every record, you could load everything to a sorted dictionary or list and then use an update cursor to overwrite every record, setting all editable fields with the data of the appropriate record that fits the order you want.  ObjectID gaps from deleted records would remain gaps after using this approach, but the records would be reordered without creating a new FC.  Aside from the risk to your data, if you have GlobalIDs or other non-editable fields that don't reset based on the changes you make then you probably don't want to do this, since there is no way their field values could be kept with the original record they were associated with.

ChrisLowrie
New Contributor
#Chris Lowrie
#Refactoring 1
#Bluespace Report and Row Creation Script


#import the necessary bits
import arcpy, math, os


#Get the points to use
table = arcpy.GetParameterAsText(0)
name = arcpy.GetParameterAsText(1)
fields = ["Latitude","Longitude", "HMS", "Day"]


date_fld = "DateTimeS"
arcpy.env.workspace = "C:\Users\project-user\Desktop\BlueSpace"
f = open(name + "_Bluespace_report.txt", "w")
fid_lst = []






#Calculate the distance between two points near Wellington, New Zealand
#Can be made more accurate if desired
def calculate_distance(x0, x1, y0, y1):
  #111,200 meters per degree of latitude
  #67,097 meters per degree of longitude at 41 degrees latitude (Wellington)
  dx = abs(x1-x0)*67097
  dy = abs(y1-y0)*111200
  distance = math.sqrt(math.pow(dx,2) + math.pow(dy,2))
  return distance




def fill_gap(row, rows_to_add):
  edit.startOperation()
  insert_cursor = arcpy.da.InsertCursor(table, fields)
  f.write("Insert")
  i = 1
  f.write(str(rows_to_add))
  while i < rows_to_add+1:
  to_insert = (row[0], row[1], row[2]+5*i, row[3])
  insert_cursor.insertRow(to_insert)
  i += 1
  edit.stopOperation()




def process_table():
  #Creates an update cursor
  rows = arcpy.da.SearchCursor(table, fields)


  prev_t = 0
  prev_x = 0
  prev_y = 0
  total_gaps = 0
  over_1 = 0
  over_5 = 0
  over_100_meters = 0
  delta_t = 0


  first = True
  dic = {}
  extremes_lst = []
  fid_lst = []

  #Iterator
  for row in rows:
  #the time
       hms = row[2]

       #If not the first pass
       if first != True:
            if hms - prev_t > 5:
                 distance = calculate_distance(prev_x, row[1], prev_y, row[0])
                 delta_t = hms-prev_t
                 f.write(str(delta_t)+"\n")
                 delta_t_str = str(delta_t/60) + " minutes " + str(delta_t%60) + " seconds "
                 rows_to_add = delta_t/5 - 1

                 fid_lst.append(prev_row)
                 fill_gap(prev_row, rows_to_add) 


                 first = False
                 prev_t = hms
                 prev_x = row[1]
                 prev_y = row[0]
                 prev_row = row








workspace = os.path.dirname(table)
edit = arcpy.da.Editor(workspace)
edit.startEditing(False, False)
process_table()
edit.stopEditing(True)

Thanks, much needed formatting tutorial.  It now adds rows with the values that I want, but I'd like them to be added directly beneath the row I am sending to fill_gap.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Chris your formatting is still off.  your fill_gap def has no indentation in the while loop, so it won't work and your spacing changes from 2 to 4 spaces within the function.  Can you confirm that the above is what you are working with or is it just a bad copy

0 Kudos
NeilAyres
MVP Frequent Contributor

Maybe one of the db gurus can step in here, but I think there is no concept of ordering or gaps in a database table.

All inserts will be appended to the end of the table.

The only way to get them to be ordered is to reprocess and recreate the data.

You can obviously impose an ordering on your view of the data (like sorting the table in ArcMap), but this is doing nothing to the actual order on disc.

0 Kudos