arcpy - SearchCursor with where_clause = "SHAPE IS NOT NULL" returns RuntimeError on in_memory feature class

MaximeDemers · ‎07-17-2017

Why the arcpy.da.SearchCursor with where_clause="SHAPE IS NOT NULL" returns RuntimeError: An invalid SQL statement was used when the feature class is stored in_memory?

For instance this works:

source = "\\\\cnatrtd8\\geo\\ArcGIS Server\\Connections\\GEODEV011.sde\\GEO09_WEB_MERCATOR\\GEO09E04_MUNCP_GEN"

with arcpy.da.SearchCursor(query_layer, ["*"], where_clause="SHAPE IS NOT NULL") as cursor:
    for row in cursor:
        print row[0]‍‍‍‍‍

while this returns a RuntimeError: An invalid SQL statement was used

source = "\\\\disk1\\myDB.sde\\myDataSet\\myFeatureClass"
copy = arcpy.CopyFeatures_management(source, "in_memory\\copy")

with arcpy.da.SearchCursor(copy, ["*"], where_clause="SHAPE IS NOT NULL") as cursor:
    for row in cursor:
        print row[0]‍‍‍‍‍‍

Any idea what is going wrong? Is there a workaround?

Thank you!

JoshuaBixby · ‎07-18-2017

I am running ArcGIS 10.5.1 and see the same thing whether I use the in-memory feature class directly in a search cursor or through a feature layer pointing back to that in-memory feature class.

In terms of your first question, i.e., what is going wrong? It appears in-memory tables and feature classes don't support using the shape field in a WHERE clause. It does seem odd given that it works fine with a feature class in a file geodatabase, but Esri does state Using in-memory workspace—Help | ArcGIS Desktop :

Data written to the in-memory workspace is temporary and will be deleted when the application is closed.
Tables, feature classes, and rasters can be written to the in-memory workspace.
The in-memory workspace does not support extended geodatabase elements such as subtypes, domains, representations, topologies, geometric networks, and network datasets.
Feature datasets or folders cannot be created in the in-memory workspace.

In-memory workspaces are not full-fledged workspaces, some geodatabase features/functionality work, some don't. In this case, I would say it is a bug since I can check the OID field, another Esri-managed field, for NULL but not the Shape field. Bug, oversight, intentional design? I don't think it matters because it will be months or longer before it is addresses, assuming it is addressed at all.

Your second question hits the name on the head, i.e., what is the workaround? In this case, I don't see an SQL-based workaround. My approach would be to use a generator expression to filter out NULL in the Shape field before executing your for loop:

source = "\\\\disk1\\myDB.sde\\myDataSet\\myFeatureClass"
copy = arcpy.CopyFeatures_management(source, "in_memory\\copy")

with arcpy.da.SearchCursor(copy,"*") as cur:
    shp_idx = cur.fields.index("Shape")
    shp_cur = (r for r in cur if r[shp_idx])
    for row in shp_cur:
        print row‍‍‍‍‍‍‍‍

Also, it is a poor practice to use "*" with cursors. Not only does retrieving more columns than needed impact performance, using the wild card makes maintaining code more difficult because the field order in the cursor can change if someone alters the schema of the feature class.

View solution in original post

JoshuaBixby · ‎07-18-2017