ST_GEOMETRY SQL function to find problem annotation shapes

Question

ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 EGDB; SDE.ST_GEOEMTRY:

I have a issue with an annotation feature class:

1. In 2.9.12, I get a "shape integrity error" when I open the attribute table and navigate to the last row. Although, I don't have that problem in 2.9.5.
2. When I copy/paste the FC to an EGDB or FDGB via Catalog, I get a "shape integrity error".
3. If I export using Feature Class to Feature Class, only 72 of 800 features get exported.
   • Same problem with the Append tool in a blank annotation FC.
   • If I exclude row 73 via a selection, then 357 rows of 799 get exported; still, not all features get exported.
   • I haven't had any luck trying to exclude additional rows in the 355-360 range. Still, only ~355 rows get exported.
4. The Repair Geometry tool doesn't support SDE.ST_GEOEMTRY.
5. Esri Case #03605230 - ...Catalog copy/paste shape integrity error

It seems like some rows are broken and preventing GP tools from working properly.

Is there a ST_GEOMETRY SQL function I could use to check for broken annotation shapes? For example, a function that would throw an error if the shape is broken.

I would use an approach like this: Find problem XML values by catching XMLTABLE() errors in custom function. If the custom function (which would use an ST_GEOMETRY function) has an error, then it would flag the row as having an error in the query resultset. That would let me isolate the problem rows and edit or replace them in ArcGIS Pro.

Answer 1

I can use ST_Intersects in an inline PL/SQL function (or a regular function).

The function tests the shapes by intersecting against a BOUDNARY FC. If the intersect is successful, then the function returns "no error". But if there is a problem, then it returns "error".

That lets me flag the problem row.

with function check_shape(anno_shape sde.st_geometry, boundary_shape sde.st_geometry) return varchar2 
is
 v_test_result varchar2(10);
begin
 select
 sde.st_intersects (boundary_shape, anno_shape)
 into 
 v_test_result
 from
 dual;
 return 'no error';
exception
 when others then
 return 'error';
end;
select 
 anno.objectid, 
 anno.shape as anno_shape,
 check_shape(anno.shape, boundary.shape) as check_shape
from 
 city.boundary boundary
cross join 
 infrastr.gcsm_hc_anno anno
where 
 check_shape(anno.shape, boundary.shape) = 'error'

enter image description here

Source: Find row with problem shape (SDE.ST_GEOMETRY spatial type)