topic Re: da.SearchCursor ORDER BY SHAPE@AREA in Python Questions

da.SearchCursor ORDER BY SHAPE@AREA

JamesCrandall — Sat, 11 Dec 2021 16:11:59 GMT

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]‍‍‍

Re: da.SearchCursor ORDER BY SHAPE@AREA

JamesCrandall — Sat, 11 Dec 2021 16:12:01 GMT

This appears to be one solution:

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

‍‍

Re: da.SearchCursor ORDER BY SHAPE@AREA

JoshuaBixby — Tue, 07 Feb 2017 18:37:47 GMT

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

Re: da.SearchCursor ORDER BY SHAPE@AREA

JamesCrandall — Tue, 07 Feb 2017 18:43:32 GMT

1, SQL Server

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

Re: da.SearchCursor ORDER BY SHAPE@AREA

JoshuaBixby — Sat, 11 Dec 2021 16:12:04 GMT

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: da.SearchCursor ORDER BY SHAPE@AREA

JamesCrandall — Tue, 07 Feb 2017 18:45:50 GMT

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

Re: da.SearchCursor ORDER BY SHAPE@AREA

JamesCrandall — Tue, 07 Feb 2017 18:47:47 GMT

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

Edit: I'm on ArcGIS 10.3

Re: da.SearchCursor ORDER BY SHAPE@AREA

JoshuaBixby — Tue, 07 Feb 2017 18:49:18 GMT

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.

Re: da.SearchCursor ORDER BY SHAPE@AREA

JoshuaBixby — Tue, 07 Feb 2017 18:58:53 GMT

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.

Re: da.SearchCursor ORDER BY SHAPE@AREA

JamesCrandall — Tue, 07 Feb 2017 19:58:56 GMT

itemgetter implemented.

Thanks for your input.