SpatialDB Advisor

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.