Oracle 18c; ArcGIS enterprise geodatabase 10.7.1:
I have a table called GCSM_HC_ANNO that has a SHAPE column (user-defined spatial type called SDE.ST_GEOMETRY).
I have a spatial query that selects GCSM_HC_ANNO that spatially intersects a polygon 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
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 row that is causing the error?
I can create an inline PL/SQL function (or a regular function).
The function tests the shape by intersecting against the BOUDNARY table. 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, which is more useful than the original query, which just throws a generic error but doesn’t tell me what row the problem is.
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'
I’m open to ideas about improving the function.