sqloracle-databasegeospatialspatialoracle18c

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


Oracle 18c; ArcGIS 10.7.1 enterprise geodatabase:

I have a table called GCSM_HC_ANNO that has a SHAPE column (user-defined spatial type called SDE.ST_GEOMETRY; geometry type = points).

And I have a spatial query that selects GCSM_HC_ANNO points that spatially intersect a polygon row in a BOUNDARY table (ST_Intersects). The query runs without errors when only the first 50 rows are returned:

select 
    anno.objectid, 
    anno.shape
from 
    city.boundary boundary
cross join
    infrastr.gcsm_hc_anno anno
where  
    sde.st_intersects (boundary.shape, anno.shape) = 1

enter image description here

But when I hit CTRL+End in the resultset in SQL Developer to return all rows, I get an error:

ORA-20002: Error converting spatial reference (SHAPE2)
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 740
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2836
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 3698
ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 339

That tells me there is a problem with one of the SHAPES in GCSM_HC_ANNO.

Using SQL, how can I find the specific row that is causing the error?


Solution

  • I can create an inline PL/SQL function (or a regular function).

    The function tests the shapes by intersecting against the BOUNDARY. 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. This technique is more useful than the original query, which throws a generic error but doesn't tell me what row is causing the problem.

    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

    I'm open to ideas about improving the function.

    Related: Why is INTO needed in this PL/SQL function if the variable doesn't get used?