da.SearchCursor ORDER BY SHAPE@AREA

2938
9
Jump to solution
02-07-2017 08:49 AM
JamesCrandall
MVP Frequent Contributor

I'm not having any success sorting an arcpy.da.SearchCursor by the SHAPE Area values.  No errors but it doesn't sort descending from my testing.

Anyone able to confirm this or offer a suggestion?

with arcpy.da.SearchCursor(clip_fc,['SHAPE@AREA','POLYGON_NM'],sql_clause=(None,'ORDER BY SHAPE.STArea() DESC')) as clipcur:
    for cliprow in clipcur:
        areaval = cliprow[0]
0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

sorted is a Python built-in, and your code is following a standard Python pattern.  If it works, great.  A couple of things to think about.

  • Sorted returns "a new sorted list from the items in iterable."  In this case, it means your entire cursor will have to be iterated and stored in a new list before sorted sorts it and gives you a list back.  For very large data sets, it may not be desirable to copy the entire cursor into memory.
  • Since your cursor is returning more than one field, it would be best for you to specify a key to select the one and only column you are interested in sorting on.  In your current example, you are actually comparing a combination of SHAPE@AREA and POLYGON_NM, whatever the latter is.  For the key, you can either use operator.itemgetter() or define a lambda/anonymous function to select just the area field.

View solution in original post

9 Replies
JamesCrandall
MVP Frequent Contributor

This appears to be one solution:

for cliprow in sorted(arcpy.da.SearchCursor(clip_fc,['SHAPE@AREA','POLYGON_NM']),reverse=True):

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

What is your backend database and what geometry storage type is being used?

0 Kudos
JamesCrandall
MVP Frequent Contributor

1, SQL Server

2. I'm guessing ST_geometry since Shape.STArea() is the name of the area column on polygon FC's.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

SQL Server doesn't support ST_Geometry.  For SQL Server it will be GEOMETRY or GEOGRAPHY if using native types, or SDEBINARY if using an Esri type.

JoshuaBixby
MVP Esteemed Contributor

I just tested with ArcMap 10.5 connecting to a 10.4.x enterprise geodatabase in SQL Server, and using a SQL clause works:

>>> with arcpy.da.SearchCursor(r'Database Connections\Default@SGDB@localdb.sde\SGDB.DBO.Infra', ["SHAPE@AREA","OID@"],sql_clause=(None,"ORDER BY SHAPE.STArea() DESC")) as cur:
...     for area,oid in cur:
...     print "{} {}".format(area,oid)
...
39593.1062297 3
26339.7586344 2
20269.7498507 6
8894.95466122 11
8412.44189983 4
8049.8586996 1
7620.10032388 10
7431.46963174 5
6953.70626041 8
6316.28689426 9
6142.45012789 7‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
JamesCrandall
MVP Frequent Contributor

It looks like I implemented the same but the cursor was not sorted as expected!

Edit: I'm on ArcGIS 10.3

0 Kudos
JamesCrandall
MVP Frequent Contributor

Do you have any comment on the solution I mentioned above?  (It's linked to from the 10.1 documentation under example #5 "sorting Mini-HOW TO" -- which I thought was strange).

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

sorted is a Python built-in, and your code is following a standard Python pattern.  If it works, great.  A couple of things to think about.

  • Sorted returns "a new sorted list from the items in iterable."  In this case, it means your entire cursor will have to be iterated and stored in a new list before sorted sorts it and gives you a list back.  For very large data sets, it may not be desirable to copy the entire cursor into memory.
  • Since your cursor is returning more than one field, it would be best for you to specify a key to select the one and only column you are interested in sorting on.  In your current example, you are actually comparing a combination of SHAPE@AREA and POLYGON_NM, whatever the latter is.  For the key, you can either use operator.itemgetter() or define a lambda/anonymous function to select just the area field.
JamesCrandall
MVP Frequent Contributor

itemgetter implemented.

Thanks for your input.

0 Kudos