idea Select By Attributes — SQL expression using SHAPE field in ArcGIS Pro Ideas

Select By Attributes — SQL expression using SHAPE field

Bud — Fri, 27 Oct 2023 17:11:19 GMT

ArcGIS Pro 2.6.8; Oracle 18c; 10.7.1 EGDB; versioned (with the option to move edits to base)

As far as I can tell, it isn't possible to Select By Attributes using a SQL expression that references the SHAPE column:

Whereas, it is possible to do it in a definition query.

That seems like an oversight. We should be able to query the SHAPE column, such as WHERE SHAPE IS NULL. And more complex use cases.

Could that functionality be changed?

Re: Select By Attributes — SQL expression using SHAPE field

Robert_LeClair — Fri, 27 Oct 2023 17:31:43 GMT

I'm using ArcGIS Pro. 3.2.x and in the Select by Attributes GP window, you do not see the SHAPE field to build a query via the Clause Builder. But if you switch it to the SQL Builder, you can create a query here. So I wonder if it's a ArcGIS Pro version issue and/or a DB issue.

See image below.

If the goal is to find geometry issues, there is a Repair Geometry (Data Management) GP tool to assist.

Re: Select By Attributes — SQL expression using SHAPE field

Bud — Fri, 27 Oct 2023 17:39:39 GMT

@Robert_LeClair Thanks.

If I recall, Repair Geometry doesn't support ST_GEOMETRY, even though geometry problems are possible in ST_GEOMETRY features. Find & repair problem ST_GEOMETRY rows — "Shape integrity error"

In this case, I was just trying to select features that didn't have the SHAPE populated. So that I could investigate why the shapes were blank, so it wasn't a case of broken geometry, just null geometry. Null geometry is a legitimate state for features.

Re: Select By Attributes — SQL expression using SHAPE field

Bud — Mon, 30 Oct 2023 20:29:26 GMT

I think the problem might be specific to versioning. I created a copy FC that isn't versioned and Select By Attributes worked on it without issue. If I register as versioned, then I get the error.

For what it's worth, SHAPE IS NULL works in definition queries, regardless if the FC is versioned or not. So that makes me think there is a bug specifically with Select By Attributes.

Re: Select By Attributes — SQL expression using SHAPE field

Robert_LeClair — Fri, 27 Oct 2023 19:03:13 GMT

Certainly - happy to assist where I can.

You're correct that Repair Geometry does not support ST_Geometry spatial types only the following ones:

Microsoft SQL Server—Geometry and Geography
PostgreSQL—PostGIS, Geometry, and Geography
Oracle—SDO_Geometry
SAP HANA—ST_Geometry