ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()

I was sent a request for help the other day from some I know overseas:

Do you know the true meaning and impact of ‘ORA-13011 value is out of range’? I have a dataset with a lot of very (almost impossible) precise data coming from Bentley microstation. And we have a ‘businessrule’ that the default tolerance in all the tables is 0.005. Any suggestion how to react on a ORA-13011 error when using sdo_geom.validate_layer_with_context

What was not said till a later email was that (my italics highlight what I suspected):

We use FME to transform … DGN-files to Oracle Spatial. An existing table is truncated and refilled with geometries. Because the tables already exist, they are already registered in de user_metadata and FME is not updating it.

Answer

Let’s construct an example that you can all try that shows this problem.

First, let’s create a 10,000 rectangular polygons inside this area.

               +————————+ 608222 5497524
|                        |
|                        |
|                        |
|                        |
|                        |
|                        |
294829 5162028 +————————+

 DROP TABLE rectangles;
   DROP TABLE rectangles succeeded.
 
 CREATE TABLE rectangles ( 
   rect_id    Integer primary key,
   geometry   mdsys.sdo_geometry
 );
   CREATE TABLE succeeded.
 
 INSERT /*+APPEND*/ INTO rectangles (rect_id,geometry)
 SELECT rownum as rect_id,
        mdsys.sdo_geometry(2003,28355,NULL,mdsys.sdo_elem_info_array(1,1003,3),
                    MDSYS.SDO_ORDINATE_ARRAY(ROUND(w.x - (w.rectWidth/2),w.rndf), ROUND(w.y - (w.rectHeight/2),w.rndf),
                                             ROUND(w.x + (w.rectWidth/2),w.rndf), ROUND(w.y + (w.rectHeight/2),w.rndf) )
                          ) as geometry
  FROM ( SELECT ROUND(dbms_random.value(w.x - ( w.WinWidth  / 2 ), w.x + ( w.WinWidth  / 2 )),w.rndf) as x,
                 ROUND(dbms_random.value(w.y - ( w.WinHeight / 2 ), w.y + ( w.WinHeight / 2 )),w.rndf) as y,
                 dbms_random.value(50, 1000)  as rectWidth, 
                 dbms_random.value(50, 1000)  as rectHeight,
                 w.rndf
           FROM ( SELECT ( 294000  + 609000  ) / 2    as X,
                          ( 5162000 + 5498000 ) / 2    as y,
                          ( 5498000 - 5162000 )        as WinHeight,
                          ( 609000  - 294000  )        as WinWidth,
                          10000                        as RectCount,
                          ROUND(log(10,1/0.05))+1      as rndf
                    FROM DUAL 
                ) w,
                TABLE(CAST(MULTISET(select level from dual connect by level <= w.RectCount) as SYS.UTL_NLA_ARRAY_INT)) s
       ) w;
   10000 rows inserted
 
 ALTER TABLE rectangles LOGGING;
   ALTER TABLE rectangles succeeded.
 
 COMMIT;
   commited

Let’s summary what was inserted into the table…

 SELECT count(*) || ' rectangles were stored' As Result
   FROM rectangles;
 
 RESULT                                                          
 --------------------------------------------------------------- 
 10000 rectangles were stored  

Now, let’s create an Oracle Metadata entry that is DELIBERATELY too small.

 DELETE FROM user_sdo_geom_metadata WHERE table_name = 'RECTANGLES';
 1 rows deleted
 
 COMMIT;
 commited
 
 INSERT INTO user_sdo_geom_metadata (  
    table_name, 
    column_name, 
    diminfo, 
    srid 
 ) VALUES (
    'RECTANGLES',
    'GEOMETRY', 
    MDSYS.SDO_DIM_ARRAY( 
          MDSYS.SDO_DIM_ELEMENT(
                'X', 
                ( ( 294000  + 609000  ) / 2 ) - 1000,
                ( ( 294000  + 609000  ) / 2 ) + 1000,
                0.05), 
          MDSYS.SDO_DIM_ELEMENT(
                'Y', 
                ( ( 5162000 + 5498000 ) / 2 ) - 1000,
                ( ( 5162000 + 5498000 ) / 2 ) + 1000,
                0.05)
    ), 
    28355
 );
 1 rows inserted
 
 COMMIT;
 commited

Now, let’s run SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT;

 DROP   TABLE rectangles_v;
   DROP TABLE rectangles_v succeeded.
 
 CREATE TABLE rectangles_v (sdo_rowid ROWID, result varchar2(1000));
   CREATE TABLE succeeded.
 
 EXECUTE SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('RECTANGLES','GEOMETRY','RECTANGLES_V');
   anonymous block completed
 
 SELECT result, count(*) as rcount
   FROM rectangles_v
  GROUP BY result;
 
 RESULT                    RCOUNT                 
 ------------------------- -----
 13011                     10000                  
 Rows Processed <10000>    1                      
 
 2 rows selected

This indicates that of the 10,000 rectangles processed all are invalid (see following) as “The result table contains one row for each invalid geometry”.

As a check, let’s run SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() on a 10% sample of all the rectangles in the table:

 SELECT DISTINCT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(a.geometry,b.diminfo)
   FROM RECTANGLES SAMPLE (10) a,
        user_sdo_geom_metadata b
  WHERE b.table_name  = 'RECTANGLES' 
    AND b.column_name = 'GEOMETRY';
 
 SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.GEOMETRY,B.DIMINFO)
 -------------------------------------------------------------
 13011
 
 1 rows selected

Notice how, in both cases, we get the Oracle error 13011 which is:

 ORA-13011: value is out of range
     Cause: A specified dimension value is outside the range defined for that dimension.
     Action: Make sure that all values to be encoded are within the defined dimension range.

Now, let’s change the user_sdo_geom_metadata to be correct by calculating it from the actual data….

 UPDATE user_sdo_geom_metadata 
    SET diminfo = ( SELECT MDSYS.SDO_DIM_ARRAY( 
                                 MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.05), 
                                 MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.05)) as diminfo 
                      FROM ( SELECT TRUNC( MIN( v.x ) - 1,0) as minx,
                                    ROUND( MAX( v.x ) + 1,0) as maxx,
                                    TRUNC( MIN( v.y ) - 1,0) as miny,
                                    ROUND( MAX( v.y ) + 1,0) as maxy
                               FROM (SELECT SDO_AGGR_MBR(a.geometry) as mbr
                                       FROM rectangles a) b,
                                            TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
                            )
                  )
  WHERE table_name = 'RECTANGLES'
    AND column_name = 'GEOMETRY';
 
 1 rows updated
 
 COMMIT;
 commited

Now, let’s re-run the validate_layer_with_context….

 DROP   TABLE rectangles_v;
  DROP TABLE rectangles_v succeeded.
 
 CREATE TABLE rectangles_v (sdo_rowid ROWID, result varchar2(1000));
  CREATE TABLE succeeded.
 
 EXECUTE SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('RECTANGLES','GEOMETRY','RECTANGLES_V');
  anonymous block completed
 
 SELECT result, count(*) as rcount
   FROM rectangles_v
  GROUP BY result;
 
 RESULT                    RCOUNT
 ------------------------- ------
 Rows Processed <10000>    1
 
 2 rows selected

SDO_GEOM.VALIDATE_LEYER_WITH_CONTEXT does not write a row if a geometry is valid. There are no rows in the table other than the row that is written indicating how many rows have been processed.

This can be checked by running SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() against the whole layer in an ordinary SQL statement:

 SELECT DISTINCT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(a.geometry,b.diminfo)
   FROM RECTANGLES a,
        user_sdo_geom_metadata b
  WHERE b.table_name  = 'RECTANGLES' 
    AND b.column_name = 'GEOMETRY';
 
 SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.GEOMETRY,B.DIMINFO)
 -------------------------------------------------------------
 TRUE
 
 1 rows selected

I hope this is of help to someone.