jplovato

Using PL/SQL to Find Features Not Suitable for ST_TRANSFORM

Blog Post created by jplovato on Apr 16, 2015

Background

Recently one of my customers indicated that they used the following UPDATE statement in Oracle at version 10.1 of ArcSDE to calculate acres of polygons, but that it didn't work anymore after we upgraded ArcSDE to version 10.2.2.

 

update SDE.ACTIVITY_TIMBERHARVEST a set gis_acres = round (to_number(((sde.st_area(sde.st_transform(a.shape, 5070)))*0.0002471054)),3);

 

At 10.2.2 it throws the following Oracle ERROR stack:

ORA-20603: Spatial References are not compatible

ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 720

ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2623   

 

This lead me to verify that our external procedure set up was good for Oracle. Then I checked the spatial reference of the source data which was using NAD93 datum and the Albers (5070) spatial reference which also was  using NAD83 datum.  Then I checked the two packages referenced in the error message ST_GEOMETRY_SHAPELIB_PKG and ST_GEOMETRY_OPERATORS to make sure they were valid and up to date.

 

Everything checked out.  The problem had to be with the data itself.

 

Diagnosis

I wrote the following PL/SQL block which uses a cursor to step through all the features in the ACTIVITY_TIMBERHARVEST feature class to see if I could find the bad features.  The code uses the same update statement to calculate GIS_ACRES as the original statement the customer was using, but does it one feature at a time.  When the feature is processed by ST_TRANSFORM correctly it updates the GIS_ACRES field with the acreage.  When the feature is bad, it updates GIS_ACRES with 0.

 

Sure enough the code found 218 features out of 465261 that ST_TRANSFORM (10.2.2) could not handle.  Upon closer inspection, I realized these features were "self-intersecting" polygons. At version 10.1, ST_TRANSFORM would process these types of features, at 10.2.2 it does not.

 

The error messages from Oracle don't indicate that the features are bad and ESRI customer support could not find the problem.

 

PL/SQL Block

DECLARE

    SR_EXCEPTION EXCEPTION;

    PRAGMA EXCEPTION_INIT(SR_EXCEPTION, -20603);

   

    v_Rownum NUMBER;

    v_Objectid SDE.ACTIVITY_TIMBERHARVEST.OBJECTID%TYPE;

    v_Shape SDE.ACTIVITY_TIMBERHARVEST.SHAPE%TYPE;

    v_GisAcres SDE.ACTIVITY_TIMBERHARVEST.GIS_ACRES%TYPE;

   

    CURSOR c1 IS

        SELECT

            rownum,

            objectid,

            shape,

            gis_acres

        FROM SDE.ACTIVITY_TIMBERHARVEST ORDER BY ROWNUM FOR UPDATE OF GIS_ACRES NOWAIT;

BEGIN

    DBMS_OUTPUT.ENABLE (buffer_size => NULL);

    OPEN c1;

    LOOP

        FETCH c1 INTO v_Rownum,v_Objectid,v_Shape,v_GisAcres;

        EXIT WHEN c1%NOTFOUND;

        -- DBMS_OUTPUT.PUT_LINE('Attempting to update ROWNUM:' || v_Rownum || ' OBJECTID:' || v_Objectid);

       

        BEGIN

            UPDATE

                SDE.ACTIVITY_TIMBERHARVEST

                SET GIS_ACRES = ROUND (TO_NUMBER (((sde.st_area (sde.st_transform

                                                                 (

                                                                     SHAPE,

                                                                     5070

                                                                 ))) * 0.0002471054)),

                                       3)

                WHERE CURRENT OF c1;

        EXCEPTION

                WHEN SR_EXCEPTION THEN

                    DBMS_OUTPUT.PUT_LINE('Tranform ERROR on ROWNUM: ' || v_Rownum || ' OBJECTID: ' || v_Objectid || ' GIS_ACRES not updated');           

                    UPDATE SDE.ACTIVITY_TIMBERHARVEST SET GIS_ACRES = 0 WHERE CURRENT OF c1;

                    CONTINUE;

        END;

    END LOOP;

    COMMIT;

    CLOSE c1;

   

END;

/

Outcomes