4

How to permanently reclassify "Sort" the attribute table records based on a field value ? I try the geoprocessing tool Sort (Data Management), but this tool always need to write a new output feature or table with a correct values, what I need is to permanently sort the value on the same table or feature class.

1
  • 3
    As far as I know, this is not currently possible. What is the reason that a new table/feature class is unacceptable to you?
    – nmpeterson
    Feb 18, 2015 at 20:15

4 Answers 4

4

The only way to be sure that features are returned in a specific order is to specify an ORDER BY clause in the query (assuming, of course, that the data source supports ORDER BY queries). Note that this can have serious performance consequences, since it may force the optimizer to use a much less efficient query plan, or force an extra pass through the database's sort area after it would have already returned results. It is best practice to avoid ORDER BY queries unless it is absolutely necessary.

Physically changing the contents of a table is the only way to give a preference for returning features in a specific order. The only safe way to do this in-situ is to use a clustered index (again, assuming that clustered indexes are possible). All other means must destroy and replace the source table. Since input destruction is potentially unreliable, there are no common solutions to implement it.

Finally, reorganizing a table on the basis of one set of columns makes it less efficient to search on all other columns. This is particularly true of large and very large tables (over 500k rows), where fragmenting the table on a attribute basis can have a significant impact on spatial searches. Since ArcGIS applies a spatial filter on nearly every query, sorting on anything but spatial order (or a functional equivalent) can significantly (and permanently) impair most spatial queries, without actually guaranteeing preferred return order.

TLDR summation: Sorting tables without a significant investment in performance review/analysis is not wise practice.

5
  • Do you have any idea how often features would be returned in the incorrect order? I generally don't test on large feature classes, but I've never noticed this. Are we talking 1:1,000? 1:1,000,000? More?
    – phloem
    Feb 19, 2015 at 4:17
  • On a spatial query? Pretty much every time the spatial index is used. For full table scan queries? It depends by RDBMS.
    – Vince
    Feb 19, 2015 at 11:52
  • So you should never use a cursor to reference the 'previous' row, without ORDER BY? That calls into question A LOT of scripts out there.
    – phloem
    Feb 19, 2015 at 17:08
  • TTBOMK there is no way to access the previous row without saving it somehow (safely). It was the previous row returned, but there's no guarantee it had the previous rowid, even with an ORDER BY (that row could have been deleted). If you're making assumptions on row order and sequencing without an ORDER BY and validation, you're working without a net.
    – Vince
    Feb 19, 2015 at 17:29
  • Just use pandas
    – user125266
    Oct 29, 2018 at 19:37
4

I think my solution was not proposed yet...

  1. Copy the feature class you want to sort into a temporary "in_memory" feature class.
  2. Sort that temporary feature class and output (overwrite) the sorted feature class into your input.

Probably not the fastest solution, but a simple and effective one

import arcpy, os

in_feature_class = os.path.join("C:", "temp", "my_input_fc.shp")
temp_feature_class = arcpy.CopyFeatures_management(in_feature_class, "in_memory\\temp")
arcpy.Sort_management(temp_feature_class, in_feature_class, [["SORT_FIELD", "ASCENDING"]])

References:

3

You can permanently sort your data with the following script. Make a backup, because this will overwrite the original data.

edit: as noted in the comments to this post, and other answers, this script does not guarantee proper order and will hamper performance on large tables. It works fine on small tables, though, so use it at your own risk.

data = []  
with arcpy.da.SearchCursor("YOUR_FEATURE_CLASS","*") as cursor:  
   for row in cursor:  
       data.append(row)  

data.sort(key=lambda tup: tup[3]) # sorts based on the 4th field, change if you want.  
i = 0  
with arcpy.da.UpdateCursor("YOUR_FEATURE_CLASS","*") as cursor:  
   for row in cursor:  
       row = data[i]  
       i += 1  
       cursor.updateRow(row)
3
  • I am not sure if this will actually achieve what is asked b/c cursors do not guarantee any order and it highly depends on the underlying database structure. At best, you can sort it wrt objectid, but I am not sure your code will do this.
    – Mahdi
    Feb 19, 2015 at 1:00
  • @Mahdi Hmmm, seems to work for my data. I'm sure you can point me to a link where order isn't honored.
    – phloem
    Feb 19, 2015 at 2:30
  • This a property of underlying SQL, you can ask to order it by something but by default it's restricting to maintain an order.
    – Mahdi
    Feb 19, 2015 at 2:39
1

I don't think such a tool exists. However you can create a model:

  1. Sort the origin feature class and therefore it creates a new feature class (e.g. SortedFC)

  2. Delete the origin feature class

  3. Rename the SortedFC to the origin feature class.

2
  • Many thanks guys, I found that the best way to do this is to use the model builder as Farid suggests Feb 23, 2015 at 19:24
  • Thanks for the input. could you please mark it as answer, for the sake of GIS SE rules. Thanks Feb 23, 2015 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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