nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

using multiple cursors

ChrisLowrie

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?

Find more posts tagged with

Accepted answers

All comments

NeilAyres

I didn't expect my few lines of comment to generate such a rich & long discussion on the underlying nature of the data tables.

Curtis, I remember Info well. Did a lot with that in the depths of info processing. Yes, Info ran on ordered lists. So was not a rdms in any modern sense. Do you recall odd / even record processing.....?

RichardFairhurst

I do not base many processes on the order of ObjectIDs, but everything I see says that Order By on any other column is slower, which probably has more to do with the fact the ObjectID is always the first column in a geodatabase than any internal storage of the records relative to each other. It is guaranteed that a sort on the 40th column in a database will be much slower when read from disk using Order By than an Order By on the ObjectID in the first column. Most sorted processing I do is through in memory processing of lists or dictionaries, or through Summary Statistics outputs that position the sorted fields at the beginning of the schema. Order By also should never be done on a column that is not indexed.

In any event, order can play an important part of any process (drawing order, analysis, etc), and if I reorganize a database using the Sort tool or create a Summary Statistics table to make the ObjectID column reflect that order it absolutely does benefit performance of tasks that use that order. Again this may be more because the ObjectID is always the first column in the schema, it is always indexed for quick access, and it has a known field type all of which optimize it for the quickest access and processing. Constantly processing an SQL Order By on a random field column in the database that the user may have failed to index that has to repeatedly determine its field position and value type each time you want to run a sort algorithm is bound to be slower. Column position in a database is not random and the first column is the fastest column to access.

The documentation makes this a black box, so all we can argue is whether or not empirically it is useless to get an ordered ObjectID or not. I say it is useful where quick access to ordered records is essential based on everything I have experienced, whether or not I can explain why that is occurring to your satisfaction. I care far more about giving advice on the practical effect of doing or not something than I do about the actual internal mechanism used to achieve that effect in a Black Box that I cannot access. So if my guesses on the Black Box are wrong, but the practical outcome is real nonetheless, my advice on the forum will always be of more value for the second than the first, and I am satisfied with that. I also do not advise anyone to constantly recreate their feature class every time they want to insert a single record into the middle of the ObjectID order, since that is very inefficient, but periodically optimizing for order can be useful if you don't rely on the ObjectID representing the same record each time you access the database. I actually don't sort most of my data, because in most cases the value of having an ObjectID in my master database consistently return the same record every time I access it is more valuable to me than having it represent a constantly changing order. Also the design of the ObjectID is primarily intended to support consistent access to the same record in a feature class, although that is only reliable if a feature class that has never been moved or recreated.

RichardFairhurst

The only documentation about the FID/ObjectID I have seen is here. It is not a technical discussion of the implementation of the ObjectID, nor is it an exhaustive discussion of the implications of the ObjectID for a programmer, and it in no way contradicts the behavior I am reporting that I have repeatedly observed for a Search Cursor. Few things are fully documented for programmers about the internal mechanisms Esri has implemented, and most everything that goes beyond the exposed interfaces that may have critical implications for the programmer has to be deduced through experimentation and testing and only gets documented through the forum.

I do not have a document reference for this behavior. However, having used Search Cursors for years on a daily basis it has been empirically established to a degree that I am certain that the FID/ObjectID is the established default order of the Search Cursor. Clues that Esri databases with FID/ObjectIDs actively maintain some kind of internal order involving the FID/ObjectID for performance include the fact that FID/ObjectID fields are always automatically indexed by the system. Search Cursor record counting is accurate and predictable and always ordered by the FID/ObjectID when used without any Order By parameter by the user. FID/ObjectID lists returned by methods that access record Selection Sets always list the FID/ObjectID values in order.

I will acknowledge that Update Cursors have sometimes shown erratic behavior as far as reading records, especially if combined with a parallel Insert Cursor operation. Update Cursors have been known to read the same record two or more times during a single pass of the cursor, usually reading the same record again immediately after an updateRow operation during consecutive for loop passes, but this is a bug as far as I am concerned and is a behavior that has never been documented by Esri or explained. That Update Cursor behavior is a source of complaint on the forum since it screws up all record counting procedures with an Update Cursor and no one has been able to make sense of it.

bixb0012

Richard Fairhurst wrote:
Search cursors do in fact read the records in the FID/ObjectID order by default, ....

I don't recall seeing any Esri documentation that explicitly states ArcGIS or ArcPy cursors include a default ORDER BY clause on the FID/OID field for any kind of data store. If you have found some, I am interested in the link.

RichardFairhurst

In ArcGIS databases designed by Esri, a concept of order and gaps is in a database has been created by the automatic creation and maintenance of the FID/ObjectID, which uniquely identifies each record at the time it is created. New numbers are always appended to the end of the number order and any deleted record creates a gap in the FID/ObjectID numbers that cannot be filled, without transferring the data into a new table/FC. Search cursors do in fact read the records in the FID/ObjectID order by default, which is the fastest order based on how the records are indexed and stored. Cursors can be sorted, but all other record orders can only be read at the cost of a substantial performance hit when read directly from disk. Most record sorting of tables initially is read in ObjectID order and is only resorted in memory through things like a tableview before being presented to the user to overcome this performance hit. No other automatically maintained record numbering is associated with the Esri databases.

NeilAyres

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.

DanPatterson_Retired

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

RichardFairhurst

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

#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.

DanPatterson_Retired

first Code Formatting... the basics++

too hard to dissect what is going on

ChrisLowrie

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?

ChrisLowrie

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 = 3600hr + 60mn + 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

DanPatterson_Retired

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

RichardFairhurst

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.