topic Re: Modifying Permanent Sort script by Chris Snyder in Geoprocessing Questions

Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Tue, 03 Aug 2010 03:42:43 GMT

Chris:

I am not sure how best to contact you directly, but I have been trying to modify a script you wrote and posted on the public scripts page about a year ago. Your script permanently sorts a feature class or table to a new output feature class or table.

I have been trying to modify the script to optionally add a valid additional field of type Long to the output that will store a copy of the original data source's OID field values. Your script by default drops the original OID values from the output. However, I need these OID values where I want to join the output back to the source, but the data source has no other unique ID than the OID field and I cannot alter the source data schema. I have some code that basically works if I can assume that all OID fields are actually named "OBJECTID", but I do not think that is the case.

In VBA there are methods that can query a data source to confirm it has an OID and then get the OID field's name (which tells me there is more than one way these fields can be named). Is there anything equivalent in Python? Or how would you approach checking these two aspects of a data source to add appropriate error checking routines to your Python script to handle a subroutine like the one I want? If the new subroutine works, I would always be able to optionally include a join field in my sorted output that would work with any source that has an OID value without ever having to modify the source schema.

Thanks for the script and I hope you can help me.

Rich

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Tue, 03 Aug 2010 14:31:36 GMT

I may have answered my initial question. I found that an output variable holding the describe information of my input feature class/table has a method called OIDFieldName that can extract the source OID field name. That should be all I need in order to verify that an input has an OID field and to make sure that I transfer information from it using its proper OID field name.

However, I have a follow up question about ModelBuilder tools in general. I noticed that when I use the tool Chris created for this script that whenever I choose an input feature class or table, an output feature class/table name is automatically created for the output parameter that appends "_sortrecords" to the original input name. It also makes sure that the output name is unique within the output workspace by appending a number to the name if necessary.

How is the output name parameter automatically being populated by the tool? I tried checking the script and every property tab for the tool, but I cannot see what is triggering this behavior. I also don't see any evidence of a IGPFunction asociated with the tool. I would like to know how to replicate this kind of behavior for other tools I might build or modify.

Anyone know how its done? Thanks.

Rich

Re: Modifying Permanent Sort script by Chris Snyder

ChrisSnyder — Tue, 03 Aug 2010 16:02:43 GMT

Preserving the original OID is a good idea! And the .oidfieldname property is the right one to use...

The output names are given the default toll name suffix by the Toolbox GUI seemingly automatically now in v9.3+ (the default naming is not handled in a controlled fashion by the script of tool parameter settings). It wasn't this way pre-v9.2, and depending on how you look at it, it can be a good or bad thing. Personally I don't like it, since it seems to override the "default" parameter values that you assign in the tool setup dialog.

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Tue, 03 Aug 2010 16:59:31 GMT

Chris:

Thanks for the answers. I can see your point about the auto fill-in behavior. It could be annoying since I do not really know the conditions that trigger it and can't control when I want it or don't.

I also should mention that I modified your script to support up to four (4) sort fields on the input source so that the sorting capabilities are equivalent to an Advanced Table Sort on a Tableview in 9.3. That makes the tool more consistent and complimentary with the Desktop interface.

I do not want to publiish my version on the Scripts page, since my version really is mostly your code. I would like to encourage you to publish a revision of your script to do these things for everyone interested. If you think it would save you any time, I can forward you my version, but I am sure you can incorporate these revisions as well or better than I have. However, either way, it would be nice if you could review my script modifications, since you understand what the overall script is doing and could provide me with some valuable feedback on whether I am using Python to its full potential. I am just beginning to transition from VBA to Python and I really don't know the Python language or coding conventions that well.

Rich

Re: Modifying Permanent Sort script by Chris Snyder

ChrisSnyder — Tue, 03 Aug 2010 21:33:45 GMT

Rich,

I will post an update to acrscripts in the next few days. So as I understand, the requirements are:

1. The user can select up to four fields to sort.
2. The user can specify a new field name that will contain the original OID prior to sorting.

Sound Good?

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Tue, 03 Aug 2010 23:11:33 GMT

Rich,

I will post an update to acrscripts in the next few days. So as I understand, the requirements are:

1. The user can select up to four fields to sort.
2. The user can specify a new field name that will contain the original OID prior to sorting.

Sound Good?

Exactly. Sounds great.

I will be interested in comparing your final code to mine. I expect I will learn a few more things I didn't know about Python. Thanks for taking this on. Your original tool was cool, but this new version will be way cool! :cool:

Rich

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Tue, 03 Aug 2010 23:24:18 GMT

I guess the old ArcScripts site is now uneditable - Damn! Until I get a CodeGallery thing going, here's the new script/toolbox. Didn't do much extensivee bug testing - Let me know if you find any issues...

I guess our posts just missed each other. I tried to open your zip file to WinZip using IE8 and get an error message. I tried Mozilla Firefox instead and that worked fine, for anyone else trying to download your file. (I guess Microsoft is trying to protect me from myself again. 😛 )

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Sun, 12 Dec 2021 04:28:55 GMT

Didn't do much extensivee bug testing - Let me know if you find any issues...

Chris:

The new script did what I requested, but I noticed you did not rewrite the portion of your error checking tests to ensure that duplicate field names were not entered into multiple Sort Field choices so that the test accounted for the new sort field parameter.

I rewrote the section beginning at line 133 as follows to both check for all possible field duplications and to brake it down into separate field tests to keep it more readable:

    #make sure the same field isn't trying to be sorted twice
    if (sortFieldName4 not in ["","#"," "] and (sortFieldName4 in [sortFieldName1,sortFieldName2,sortFieldName3])):
        message = "ERROR: Duplicate field name in Sort Field #4.  Can't sort the same field twice! Exiting script..."; showPyError(); sys.exit()
    if (sortFieldName3 not in ["","#"," "] and (sortFieldName3 in [sortFieldName1,sortFieldName2])):
        message = "ERROR: Duplicate field name in Sort Field #3.  Can't sort the same field twice! Exiting script..."; showPyError(); sys.exit()
    if (sortFieldName1 == sortFieldName2):
        message = "ERROR: Duplicate field name in Sort Field #2.  Can't sort the same field twice! Exiting script..."; showPyError(); sys.exit()

I noticed that when I just wanted to sort just two fields that I as long as I put the first sort field in the Sort Field #1 parameter I could add the second sort field to any of the other 3 sort field parameters (as long as I specified the sort order that paired with the sort field parameter I chose) and the script worked the same. That is nice. Also, thanks for the quick modification.

I am still trying to understand the way the nested try and if block works in the section where you transferred the OID values. Your code worked, but based on the comment above that section I would have thought that it would have thrown an error when the OIDFieldName tried to calculate to the output's true OIDFieldName and that the user provide OID field would not have been populated. Obviously I made a wrong assumption somewhere, but where did I go wrong? When both tables have a matching OID field name and the setvalue matches the names and attempts to overwrite the inserted record's true OID value, does that not cause an error? Did I miss that somehow the code does not actually try to overwrite a true OID value in the output? Please provide me with a little insight. (Faulty assumptions like that can sometimes stick in my thinking and cause me to write unneeded code to work around a non-problem 😮 .)

Thanks again.

Rich

Re: Modifying Permanent Sort script by Chris Snyder

ChrisSnyder — Wed, 04 Aug 2010 15:03:47 GMT

Great - thanks for the error checking part - I will add that to my version.

The part where the OIDs get populated: The deal is that you can't populate some fields in a fc/table like OID, Length, Area, etc. That is what the "try" is for: Loop through the fieldList, and for each field, try to populate that field (but we can't for OID, Shape, etc). However, we can popualte the ORIG_OID field (whataver its called) and other fields since they are writable. I have some other logic in there for dealing with the 10 character limitation for .dbf/.shp files, and there probably is a better way to organize that, but... Also some logic for dealing with the ORIG_OID field if the user provided a field name, otherwise don't populate it (since we never added it in the 1st place).

Hope that helps...

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Sun, 12 Dec 2021 04:28:58 GMT

I realized why the loop made no sense to me. It is written so that every time the loop writes to a field that can have its value set, it also writes to the ouput OID field provided by the user (if the input has an OID field). So if there are 10 writable fields, the user OID field is writen 10 times per record (more writeable fields only makes processing the loop worse due to twice as many write attempts). My VBA training from ESRI on loops has taught me to place as much of my testable conditions outside of the loop as possible and then minimize writing to a cursor within the loop to maximize speed and efficiency.

To achieve those objectives I set some variables outside the loop that have pre-tested whether a write of the user provided OID field should even be attemped within the loop. I then only attempt to write to the user field when the input OID field is being read from the input (if the input has no OID field the user provided field never gets a write attempt and a related warning is reported at the end). All other non-OID fields being read from the input will bypass the user provided field and use your normal field writing code. This revison of the loop results in only a single write attempt for each field in a record. This should definitely improve overall speed of the cursor writing process.

My revision of the loop is as follows:

    inputLayerOidFieldName = dsc.oidfieldname
    #Only attempt to write to the users OID field if the a field name is provided and the input has an OID field.
    if originalOidFieldName not in ["","#"," "] and inputLayerOidFieldName > " ":
        tryOidFieldWrite = True
        origOidPopulateSuccessFlag = True
    else:
        tryOidFieldWrite = False
        origOidPopulateSuccessFlag = False
    while searchRow:
        insertRow = insertRows.newrow()
        for fieldName in inputLayerFieldList:
            try: #you can't write the OID, length, area, etc. fields!
                if outShpDbfFlag == False:
                    # Only attempt to write to the user OID field when the input OID field is being read
                    if tryOidFieldWrite == True and inputLayerOidFieldName == fieldName:
                        try:
                            insertRow.setvalue(originalOidFieldName, searchRow.getvalue(inputLayerOidFieldName))
                        except:
                            origOidPopulateSuccessFlag = False
                            pass
                    # All fields other than the input OID field are passed to this code.  The input OID will also be passed if the user did not provide an output OID field name, but being non-writable it will fail.
                    else:
                        insertRow.setvalue(fieldName, searchRow.getvalue(fieldName))
                else:
                    # Only attempt to write to the user OID field when the input OID field is being read
                    if tryOidFieldWrite == True and inputLayerOidFieldName == fieldName:
                        try:
                            insertRow.setvalue(originalOidFieldName[0:10], searchRow.getvalue(inputLayerOidFieldName))
                        except:
                            origOidPopulateSuccessFlag = False
                            pass
                    # All fields other than the input OID field are passed to this code.  The input OID will also be passed if the user did not provide an output OID field name, but being non-writable it will fail.
                    else:
                        insertRow.setvalue(fieldName[0:10], searchRow.getvalue(fieldName))
            except:
                pass
        recordCount = recordCount + 1
        insertRows.insertrow(insertRow)        
        searchRow = searchRows.next()

    message = "Successfully wrote " + str(recordCount) + " records!"; showPyMessage()
    # Only publish a warning if the OID field write attempt should have been made and failed or if the user provided a field but the input had no OID field.
    if originalOidFieldName not in ["","#"," "] and tryOidFieldWrite == True and origOidPopulateSuccessFlag == False:
        message = "WARNING: For some reason, the original OIDs could not be preserved in the output!"; showPyWarning()
    if originalOidFieldName not in ["","#"," "] and tryOidFieldWrite == False:
        message = "WARNING: The input had no OID field and could not be preserved in the output!"; showPyWarning()

I hope that these revisions meet with your approval and can be incorporated into your final code.

Rich

Re: Modifying Permanent Sort script by Chris Snyder

ChrisSnyder — Wed, 04 Aug 2010 20:05:34 GMT

Rich,

You are totally correct! Great observation... I thought it seemed like when I was testing this stuff yesterday it was running a bit slow when I used the ORIG_OID option... Another idea to structure it would be to append the ORIG_OID field (if the user provides it) to the inputLayerFieldList, and then have a condition in the loop that says if inputLayerField == originalOidFieldName, then attempt to write it.

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Wed, 04 Aug 2010 20:06:45 GMT

I wanted to also modify the loop processing to implement a standard ArcObjects practice, but I do not know if Python supports it. In ArcObjects it is much more efficient to use a field's index number that indicates the field's relative numeric position in the table rather than its field name to read and write data. The reason is that when a field name is passed to ArcObject methods that use a field name, the method in the background processes an invisible loop through the table schema to get at a field's index position. However, by using methods that directly use the field index number a loop is not triggered and it operates more like accessing an array element with an index value.

It is a standard practice in ArcObjects to speed up loops using dual cursors to first match up the input and output field names to obtain a key pair array of field index values outside of the loop. Then within the loop the key pair indexes are used as directly as possible and methods that trigger the field name/field schema matching hidden loop are avoided. Using the preloop matched field index directly in ArcObjects can speed up cursor get and set value operations by a factor that averages the number of fields divided by 2. The effect on performance becomes very pronounced in a loop.

Is there anything equivalent in Python that will provide and use a field index value with a cursor rather than a field name? If not I believe all loops with get and set value cursor operations that use a field name in reality are processing two loops (and getting an increased hit as fields toward the end of the schema are accessed). If no similar field index capability exists for Python, that might provide a partial explanation for the attrocious performance of Python on joined tables as opposed to VBA or VB Script in the ModelBuilder field calculator.

Rich

Re: Modifying Permanent Sort script by Chris Snyder

ChrisSnyder — Sun, 12 Dec 2021 04:29:01 GMT

Wow that's good info! So I do know of a way to get the field's index via Python, but it's slow and hokey, and you have to use a featurelayer or tableview as input. For example:

gp.makefeaturelayer(fc,"fl")
print gp.describe("fl").fieldinfo.findfieldbyname("TER_ID")
6 #that is to say TER_ID is the 7th field, 0 being the 1st.

However, I don't think there is a way to retrieve a field value in a cursor using it's index value. Maybe I'm wrong though... Seems that would be a major performance enhancement like you said. I have mostly shied away from embedded cursors (current script being an exception) since they tend to be slow.

One thing I have used a lot (and that could be useful here if the table wasn't too huge and you didn't care to transfer the geometry) is using a Python Dictionary object to emulate a table structure in memory. Once you have loaded a table into a dictionary (using a searchcursor) it is EXTREEMLY fast to access the field values. This is especially useful to when you have a need to constantly look up lots and lots of unordered values. For example, I wrote a script that performs traces of a stream network (created using the hydrology tools). The idea is that every arc is flowing downhill, so has a startnode that is upstream of the endnode. The basic approach I took was to load the OIDs and their start/end nodes into a dictionary. Then (if tracing downstream) I basically traverse the items in the dictionary, looking up the arcs end node, searching for an arc that has its startnode == the last end node and so on. See: http://forums.esri.com/thread.asp?t=275202&f=1729&c=93#948148 for the code.

A simple dictionary example:

lookupDict = {}
searchRows = gp.searchcursor(lookupTbl)
searchRow = searchRows.next()
while searchRow:
   lookupDict[searchRow.LOOKUPITEM] = [searchRow.FIELDVALUE1,searchRow.FIELDVALUE2]
   searchRow = searchRows.next()
del searchRow
del searchRows

updateRows = gp.updatecursor(tableToUpdateTbl)
updateRow = updateRows.next()
while updateRow:
   updateRow.MEAN = (lookupDict[updateRow.LOOKUPITEM][0] + lookupDict[updateRow.LOOKUPITEM][1]) / 2
   updateRows.UpdateRow(updateRow)
   updateRow = updateRows.next()
del updateRow
del updateRows
del lookupDict

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Wed, 04 Aug 2010 20:42:09 GMT

Rich,

You are totally correct! Great observation... I thought it seemed like when I was testing this stuff yesterday it was running a bit slow when I used the ORIG_OID option... Another idea to structure it would be to append the ORIG_OID field (if the user provides it) to the inputLayerFieldList, and then have a condition in the loop that says if inputLayerField == originalOidFieldName, then attempt to write it.

I perfer my version, because it allows me to distinguish three cases:

1. No user OID field name provided.
2. User OID field provided but no input OID available
3. User OID field provided, input OID available, but write attempt fails anyway.

Case 2 might not be anticipated by the user. Case 3 is especially likely to happen when a geodatabase is being ported to a .dbf or .shp file and field name truncations do not match the way the schema was imported into the output feature class/table.

Case 3 could also occur because the code currently does not test if the user provided a valid field against the output data source, which could happen if the user unknowingly uses a data source keyword or included spaces in the name or something else. A gp.ValidateFieldName probably should be used in the code and if a different name is returned by that tool the new name should be appended to the output table and the user should be warned about the field name transformation. This report would also help for the possible errors in field tranferrance that may result from trucating field names in and geodatabase to .dbf and .shp file transformations. The appended user OID would still allow the user to join and manually transfer any missed field data to the output when they determined there was a failure that cannot be remedied with the current implementation.

I hope this helps.

Rich

Re: Modifying Permanent Sort script by Chris Snyder

ChrisSnyder — Wed, 04 Aug 2010 20:48:07 GMT

Jinx posting. Look at my post right above yours concerning dictionaries. This is all great stuff - I'm a hack self-taught programmer, so it's great to get someone else's suggestions, tips, tricks, etc.

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Wed, 04 Aug 2010 21:22:14 GMT

However, I don't think there is a way to retrieve a field value in a cursor using it's index value. Maybe I'm wrong though... Seems that would be a major performance enhancement like you said. I have mostly shied away from embedded cursors (current script being an exception) since they tend to be slow.

Your current code only creates a hit that is about twice as bad as using a single cursor since you are not really doing a record match on both sides of the cursors. You are just reading a record on one side and writing a new record on the other. The practice I mentioned for ArcObjects should still dramatically speed performance for a single cursor and for this kind of simple embedded cursor pair if Python had an efficient way to implement it. However, for truly embedded cursor routines that have to look up records against each other or walk through a sorted pair of cursors (using multi-field keys especially), the primary hit is from maintaining the secondary query synchronization on unsorted records or records that are only sorted in memory.

Your tool that permanently sorts to a new table (without reordering fields on the original to use a summary/frequency) offers me the potential to solve the dual cursor synchronization issue, which was partly why I am so interested in it. Developing an efficient way to do a single pass on a pair of cursors against tables with permanently sorted records could speed up the embedded cursors significantly. By operating on all fields at once in a record with the cursor pair I should be able to be much more efficient than using the field calculator on a pair of joined tables operating one field at a time (recognizing that there are diminishing returns as I need to do this with fewer fields). The OID addition I requested means that by doing a second sort back to original OID order I could use the same technique to transfer the data back to the original data source (without having to do a memory based sort). Linking back to the original is particularly important to me where the original cannot be overwritten because it has topologies, feature linked annotation or other advanced features (or I do not control the original data but need to maintain a duplicate that can use these techiniques to extend and enhance the source data with data that I maintain).

The dictionary approach is valuable when the spatial information does not need to be taken into consideration. Unfortunately, the spatial data almost always plays a roll in my cursor routines. In my case I may use Visual Studio to do the embedded cursor work (or I might port your Python code to Visual Studio and see if I can get a substantial performance gain using an ArcObject cursor method I described above). I am not really comfortable porting my VBA to Visual Studio either, but if I am going to have to do another learning curve I want others to benefit (like I have from this exchange). Anyway, thanks for pointing me in a new direction that I think really begins to tackle some of the problems I am trying to solve.

(Edit) Actually, the dictionary approach may offer some interesting possibilies for me since I am in Transportation and most of my problems relate to traversing line networks in different ways. Linear referencing has solved some of my problems, but it would be nice to create efficient cursor routines that can quickly traverse Route IDs and measure values on joined event tables of different inventories (signals, signs, curb ramps, accidents) and perform sophisticated analysis for output to a summary table. My bosses would like to do segment analysis based on 1000 intervals or between interesections and I don't see a way to really do it without some kind of cursor operation. Route IDs and measures provide me with a way to understand direction of travel and network traversal without usiing the shapes (if used together with an X/Y coordinate the analysis possibilities become very interesting). Trying to train the computer to talk with end user data and needs is where I spend a lot of my time. Anyway, ordering data seems to be the biggest factor in making these routines really perform well so this is a subject near and dear to my heart.

Rich

Re: Modifying Permanent Sort script by Chris Snyder

RichardFairhurst — Wed, 04 Aug 2010 21:36:28 GMT

Jinx posting. Look at my post right above yours concerning dictionaries. This is all great stuff - I'm a hack self-taught programmer, so it's great to get someone else's suggestions, tips, tricks, etc.

Double jinx posting. My background is the same. I am not part of IT. I'm just a guy who wants to get the job done and transform the way my Department sees and does things through GIS.

Re: Modifying Permanent Sort script by Chris Snyder

ChrisSnyder — Wed, 04 Aug 2010 21:39:15 GMT

However, for truly embedded cursor routines that have to look up records against each other or walk through a sorted pair of cursors (using multi-field keys especially), the primary hit is from maintaining the secondary query synchronization on unsorted records or records that are only sorted in memory.

This is a great description of the case where the dictionary structure is so fast/usefull!

Re: Modifying Permanent Sort script by Chris Snyder

ChrisSnyder — Wed, 04 Aug 2010 21:45:53 GMT

Holy crap! Rich, you got me thinking here and check this out. I had no idea you could do this (load the geometry object ito a dictionary so easily). Not sure how to use it, but that so damn cool!!! basically this way you could store an entire FC as a Python dictionary! Wow!

dict = {}
>>> searchRows = gp.searchcursor(fc)
>>> searchRow = searchRows.next()
>>> dict[0] = searchRow.Shape
>>> dict
{0: <geoprocessing describe geometry object object at 0x00C1AAE8>}
>>> del searchRow
>>> del searchRows
>>> dict[0].getpart(0)
<geoprocessing array object object at 0x00C1A890>
>>> dict[0].getpart(0).next().x
820175.92491082847
>>>

Re: Modifying Permanent Sort script by Chris Snyder

ChrisSnyder — Sun, 12 Dec 2021 04:29:04 GMT

Rich, you will like this:

import arcgisscripting
gp = arcgisscripting.create(9.3)
fc = r"D:\csny490\oesf_nso_models_20100623\landscape_model\results\results_bu.gdb\summary_results"
fieldList = gp.listfields(fc)
fieldIndex = 0
fieldNameDict = {}
for field in fieldList:
    fieldNameDict[field.name] = fieldIndex
    fieldIndex = fieldIndex + 1
fcDictionary = {}
oidFieldName = gp.describe(fc).oidfieldname
searchRows = gp.searchcursor(fc)
searchRow = searchRows.next()
while searchRow:
    recordReadCount = 0
    oidFieldValue = searchRow.getvalue(oidFieldName)
    fcDictionary[oidFieldValue] = []
    for field in fieldList:
        fcDictionary[oidFieldValue].append(searchRow.getvalue(field.name))
    searchRow = searchRows.next()
del searchRow
del searchRows

#Now that it's in the memory, fetch the habitat density field value of OID = 31 and its width...
fcDictionary[31][fieldNameDict["HAB_DENSITY"]] + " - " + str(fcDictionary[31][fieldNameDict["Shape"]].extent.width) + " feet wide!"