Find & repair problem ST_GEOMETRY rows — "Shape integrity error"

[Bud]

Oracle 18c; 10.7.1 EGDB; SDE.ST_GEOMETRY

I have a point feature class and a separate feature dataset that can't be copy/pasted into a file geodatabase via Catalog in ArcMap 10.7.1 or Pro 2.6.8:

Failed to paste INFRASTR.GCSM. Shape integrity error.

My workaround so far has been to copy the data from SDE to a personal GDB first (via ArcMap), then copy from the personal GDB to the FGDB. The personal GDB seems to fix the data. Not sure why/how, though.

Rather than use that dodgy workaround, I want to find and fix the problem geometries.

Unfortunately, the Check Geometry and Repair Geometry geoprocessing tools don't support SDE.ST_GEOMETRY. Idea: Repair Geometry GP tool: Support SDE.St_Geometry. And there aren't any ST_GEOMETRY SQL functions that can check or repair geometries either (unlike SDO_GEOMETRY).

How can I find and repair the problem SDE.ST_GEOMETRY rows?

[JohnFannon]

I'm not overly familiar with ST_GEOMETRY in Oracle, but as the data is points, things to check might be:

• Does ST_IsSimple() return true for all records?
• Are there any null geometries? - I recall there are potentially different ways of creating a null geometry (e.g. the geometry column was specified as Null or a geometry with null coordinates was added). You can potentially use ST_IsEmpty() or just where <geometry column name> is null.

Another alternative, if you have access to it, is to use Data Interop or FME to read the records from Oracle and then check the geometries with a GeometryValidator.

[VinceAngelo]

Part of the issue could be using ancient software -- Pro 2.6 was released three years ago, and 10.7.1 six months before that.  The fact that a *point* feature class has topology errors is quite bizarre.

Personally, I refuse to use copy/paste for data transfer -- too many ways for things to go wrong, and no way to determine what is being done.

SDO_GEOMETRY has to have repair utilities because there is no way to get to the data in otherwise. ST_GEOMETRY is not supposed to accept invalid geometry, so there isn't any way to repair it once in place. The fact that it's corrupt is a bug in the Paste procedure (possibly introduced at Copy).

You really ought to bring this dataset to the attention of Tech Support, though it wouldn't hurt to make sure the issue wasn't addressed by 2.7, 2.8, 2.9, 3.0, 3.1, or 10.8, 10.8.1 or 10.8.2 first.

- V

[Bud]

Any thoughts on how to share a ST_GEOMETRY Oracle table with support? If I understand correctly, the SHAPE column is stored as a BLOB (or something similar). So I don't think I could generate SQL insert statements, etc.

[MarceloMarques]

@Bud You need to provide an Oracle Data Pump Export to Esri Technical Support.
How to Move the Oracle Enterprise Geodatabase with the Oracle Data Pump Utility

[MarceloMarques]

You can try this to find the geometries that fail to load.

1. Open ArcCatalog

2. connect to the SDE geodatabase

3. right click the featureclass > Export > XML Workspace Document > Schema Only

4. create a FGDB

5. right click the FGDB > Import > XML Workspace Document

6. import the xml schema only file for the featureclass

7. select the FGDB featureclass > right click > Load > load data...     ( this is the Simple Data Loader Tool )

Input data will be your SDE featureclass 

the Simple Data Loader might show you the records that fail to load.

In Pro you can use the same steps, but there is no "Simple Data Load Tool" and you can use the GP Tool "Append Data".

Other Options to Validate your Featureclass Geometries.

1. Geodatabase TOPOLOGY

Create topology rules such as points must not overlap to check your data.

An overview of topology in ArcGIS—ArcMap | Documentation

Topology in ArcGIS—ArcGIS Pro | Documentation

2. ArcGIS Data Reviewer Extension

If you have the "ArcGIS Data Reviewer Extension" ArcMap or Pro then use the Reviewer Check Geometry Tool.

ArcGIS Data Reviewer checks—ArcGIS Pro | Documentation  - "Check Geometry"

Checks in Data Reviewer—ArcMap | Documentation (arcgis.com) - "Invalid Geometry Check"

3. ArcGIS Data Interoperability Extension or FME Workbench

you can use Model Builder in ArcMap or Pro with the ArcGIS Data Interoperability Extension functions to check the geometry, the same can be done with FME Workbench.

What is the Data Interoperability extension?—ArcMap | Documentation (arcgis.com)

ArcGIS Data Interoperability extension for Desktop—ArcGIS Pro | Documentation

4. ST_GEOMETRY FUNCTIONS

ST_GEOMETRY ST_ISSIMPLE function

ST_IsSimple—ArcMap | Documentation (arcgis.com)

ST_GEOMETRY ST_ISEMPTY function

ST_IsEmpty—ArcMap | Documentation (arcgis.com)

For ArcGIS Pro

Databases and ST_Geometry—ArcGIS Pro | Documentation

stgeometry_reference.pdf (arcgis.com)

You must configure the oracle extproc.ora to access the ST_GEOMETRY library to be able to use ST_GEOMETRY functions in SQL statements.

Configure the extproc to access ST_Geometry in Oracle—ArcMap | Documentation (arcgis.com)

Configure extproc to access ST_Geometry in Oracle—ArcGIS Pro | Documentation

I hope this helps.

[Bud]

For my notes: This issue also applies to ArcGIS Pro 2.9.12.

[Bud]

I think the problem is an annotation feature class. The annotation FC gets automatically copied with the main point FC due to a relationship class.

The reason I think the problem is the annotation FC:

I can add the annotation FC to a map without errors. And the annotations show up in the map if I zoom in. However, when I open the attribute table and navigate to the last row, I get an error:

Failed to load data. Error: Shape integrity error

I haven't determined the cause of that error yet.

[Bud]

In an SQL query, I wrote a custom function that uses ST_INTERSECTS() to check if each feature can be used in a spatial SQL operation. If there is an error, then the function returns "error" to the query's resultset. That let me flag the problem row.

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

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'

Related: ST_GEOMETRY SQL function to find problem annotation shapes

I'm troubleshooting the problem feature with Esri Support:

Esri Case #03605230 - ...(Catalog copy/paste shape integrity error)