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
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?
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'
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?