Do SQL statements in arcpy DA Searchcursors accept shape tokens?

335
3
Jump to solution
10-27-2021 08:29 AM
ZacharyHart
Regular Contributor

I'm trying to create a simple function to sort based on a point FC X & Y values and then add a sequential ID. I know the 'old' update cursor supports sort fields directly, where arcpy.da.searchcursor you must use an SQL statement such as ORDER BY.

I'm leaning towards using shape tokens since the input FC may or may not have an X & Y coordinate field.

fields = ['OID@','SHAPE@X','SHAPE@Y','NewID']
sqlOrder = "ORDER BY {0}, {1} ASC".format(fields[1],fields[2])
startNumber = 100
 with arcpy.da.UpdateCursor('Renum', fields,sql_clause = (None, sqlOrder) as cusor:
   for row in cursor:
       row[3] = startNumber + 1
       cursor.updateRow(row)

For which I'm getting:

Traceback (most recent call last):
  File "<string>", line 2, in <module>
RuntimeError: Bad syntax in request. (status code 400).

The input FC is in a fGDB so SQL ORDER BY is supported (according to the documentation).

I guess this is less so about evaluating this code as it is the question: do SQL statements accepts shape tokens?

I guess my alternative is to simply add XY values and then have a known set of fields to work with.

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

A shape token is an Esri construct that backend datastores (shapefile, file geodatabase, enterprise geodatabase, etc...) don't know exist, so passing "SHAPE@X" in a SQL WHERE clause will fail regardless of the backend datastore.

In general, the SQL clauses with ArcPy DA cursors are passed straight through to the backend datastore, so what you can do in the SQL clauses varies.  For shapefiles and file geodatabases, I don't believe any spatial methods are supported in SQL clauses; however, you can use spatial methods in SQL clauses with enterprise geodatabases. 

View solution in original post

3 Replies
ZacharyHart
Regular Contributor

Here's the code I'm currently using BTW. Autoincrement is just the sample from Esri docs:

import arcpy
from AutoIncrement import AutoIncrement

def SpatialRenumberPoints(InLayer):
    arcpy.management.AddXY(InLayer)
    arcpy.AddField_management(InLayer, "NewID", 'LONG')
    fields = ['OID@','POINT_X','POINT_Y','NewID']
    sqlOrder = "ORDER BY {0} DESC, {1} ASC".format(fields[2], fields[1])
    with arcpy.da.UpdateCursor(InLayer, fields,sql_clause = (None, sqlOrder)) as cursor:
        for row in cursor:
            row[3] = AutoIncrement()
            cursor.updateRow(row)
0 Kudos
DanPatterson
MVP Esteemed Contributor

SearchCursor—ArcGIS Pro | Documentation

see example 5A and 5B if ordering/sorting is the issue, there is python alternative to sql


... sort of retired...
JoshuaBixby
MVP Esteemed Contributor

A shape token is an Esri construct that backend datastores (shapefile, file geodatabase, enterprise geodatabase, etc...) don't know exist, so passing "SHAPE@X" in a SQL WHERE clause will fail regardless of the backend datastore.

In general, the SQL clauses with ArcPy DA cursors are passed straight through to the backend datastore, so what you can do in the SQL clauses varies.  For shapefiles and file geodatabases, I don't believe any spatial methods are supported in SQL clauses; however, you can use spatial methods in SQL clauses with enterprise geodatabases.