topic Re: using multiple cursors in Python Questions

using multiple cursors

ChrisLowrie — Fri, 15 Apr 2016 17:02:09 GMT

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?

Re: using multiple cursors

RichardFairhurst — Fri, 15 Apr 2016 17:40:22 GMT

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.

Re: using multiple cursors

DanPatterson_Retired — Fri, 15 Apr 2016 18:13:12 GMT

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

Re: using multiple cursors

ChrisLowrie — Thu, 21 Apr 2016 19:02:44 GMT

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

Re: using multiple cursors

ChrisLowrie — Thu, 21 Apr 2016 21:29:11 GMT

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?

Re: using multiple cursors

DanPatterson_Retired — Thu, 21 Apr 2016 21:32:26 GMT

first Code Formatting... the basics++

too hard to dissect what is going on

Re: using multiple cursors

ChrisLowrie — Sat, 11 Dec 2021 07:08:47 GMT

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

Re: using multiple cursors

RichardFairhurst — Thu, 21 Apr 2016 21:49:33 GMT

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.

Re: using multiple cursors

DanPatterson_Retired — Thu, 21 Apr 2016 21:51:48 GMT

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

Re: using multiple cursors

NeilAyres — Fri, 22 Apr 2016 08:17:13 GMT

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.

Re: using multiple cursors

RichardFairhurst — Fri, 22 Apr 2016 14:48:19 GMT

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.

Re: using multiple cursors

JoshuaBixby — Fri, 22 Apr 2016 16:45:44 GMT

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.

Re: using multiple cursors

RichardFairhurst — Fri, 22 Apr 2016 18:08:24 GMT

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.

Re: using multiple cursors

curtvprice — Sat, 23 Apr 2016 02:40:57 GMT

Back early in ArcGIS time (the summer of 2000) I was warned directly to never never assume record order in ArcGIS like we used to in INFO (a brutally simple [and very fast) database that composed the database part of "Arc/INFO"). I was told different data formats (notably RDBMS data sources) may return data in an unpredictable order due to the RDBMS doing parallel processing that Esri does not control. ORDER BY is supported for all geodatabase data sources. I also have made use of sorting results coming out of a search cursor as in : sorted(arcpy.da.SearchCursor(tbl, field)).

Re: using multiple cursors

RichardFairhurst — Sat, 23 Apr 2016 06:48:57 GMT

I only edit File geodatabase with cursors for performance reasons and download SDE data to that format for all geoprocessing. The predictable order that the file geodatabase format provides only leads me to say that I have one more reason to continue doing what I do. Under any circumstances, ObjectIDs do represent the order that records are created in a geodatabase and cannot be reused once issued or deleted without risking database corruption, and no other order is represented or maintained within the data automatically in ArcGIS without a custom written program. When working with personal geodatbases I have seen the effect when a user corrupted the order and uniqueness of the ObjectID field through an Access update query run outside of ArcGIS, but nothing works correctly in ArcGIS if that happens.

Re: using multiple cursors

DanPatterson_Retired — Sat, 23 Apr 2016 09:49:24 GMT

This whole thread has me seeing issues with 'bits' of the process getting parsed out into separate blocks of discontiguous memory or storage location on disk. This will surely slow down the whole process. I must agree with finishing one task, consolidating access in memory or on disk, then proceeding to the next task as being the best approach rather than to shuffle cursors within cursors. Numpy has an 'iscontiguous' property when working with data arrays, which I use prior to running a second process... if it isn't, I consolidate before continuing. Do File geodatabases or cursors in general, not employ such a protocol?

Re: using multiple cursors

JoshuaBixby — Sat, 23 Apr 2016 15:09:50 GMT

Richard, thanks for providing a link. Although the text hasn't appeared to change from ArcGIS 10.1 to ArcGIS 10.4, there is a corresponding web page in the new online documentation: Fundamentals of ObjectID fields. Unfortunately, but not completely surprising, that page on ObjectIDs doesn't mention anything about sorted-ness or ordered-ness.

The ArcObjects Help for .NET Developers has a Geodatabase library page that I think sheds some light on the this topic:

The Geodatabase library provides the application programming interface (API) for the geodatabase. The geodatabase is a repository of geographic data built on standard industry relational and object relational database technology. The objects in the library provide a unified programming model for all supported data sources in ArcGIS. The Geodatabase library defines many of the interfaces that are implemented by data source providers higher in the architecture.
....
A table has one or more columns, referred to as fields, and contains an unordered collection of rows. For each field, each row has exactly one value in the data type of the field.

For databases, at least relational databases, tables represent unordered collections of rows. That said, tables can be ordered to some extent by adding certain types of indexes, like a clustered index in SQL Server, but that is an implementation matter addressing performance and not a design principle of ordered-ness.

In the above quote, the Geodatabase library page is talking about the API and not the file geodatabase storage itself. It seems reasonable enough that Esri could have implemented a cluster-like index on ObjectID, it would explain a fair amount of behavior you see, but I have never seen or found documentation that speaks to the issue directly. Even if that is the case, it doesn't change the fact that a user accesses the contents of a file geodatabase through the API in one form or another, and that API doesn't guarantee any order on the rows of a table, unless using ORDER BY.

What you have seen empirically with ObjectID ordering and Search Cursors doesn't surprise me, because I do think there is a cluster-like index on ObjectID in file geodatabases, but what you have seen with Update Cursors also doesn't surprise me because order isn't guaranteed in the Geodatabase API unless ORDER BY clauses are present.

Re: using multiple cursors

RichardFairhurst — Sat, 23 Apr 2016 18:44:08 GMT

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.

Re: using multiple cursors

NeilAyres — Mon, 25 Apr 2016 12:07:51 GMT

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