topic Re: Do SQL statements in arcpy DA Searchcursors accept shape tokens? in Python Questions

Do SQL statements in arcpy DA Searchcursors accept shape tokens?

ZacharyHart — Wed, 27 Oct 2021 15:29:55 GMT

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.

Re: Do SQL statements in arcpy DA Searchcursors accept shape tokens?

ZacharyHart — Wed, 27 Oct 2021 17:49:27 GMT

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)

Re: Do SQL statements in arcpy DA Searchcursors accept shape tokens?

DanPatterson — Wed, 27 Oct 2021 20:38:40 GMT

SearchCursor—ArcGIS Pro | Documentation

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

Re: Do SQL statements in arcpy DA Searchcursors accept shape tokens?

JoshuaBixby — Thu, 28 Oct 2021 14:49:18 GMT

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.