2 Replies Latest reply: Mar 5, 2012 11:17 AM by 233535 RSS

    ORA13226 when using view with geometry created using sdo_util.simplify

    233535
      I have a polygon table (and spatial index) using Ora11.1 that i can query such as:

      Select ID From MyTable A where (MDSYS.SDO_RELATE(A.GEOMETRY, SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(73.091805, 18.312310, 72.250051, 19.076872)), 'mask=INSIDE+COVEREDBY+EQUAL querytype=window') = 'TRUE');

      This query returns 2 rows. I have created a view:

      create or replace view v_MyTable as
      select id, sdo_util.simplify(geometry,20,2) as s_geometry
      from MyTable;

      The subsequent query returns ORA13226

      Select ID From v_MyTable A where (MDSYS.SDO_RELATE(A.S_GEOMETRY, SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(73.091805, 18.312310, 72.250051, 19.076872)), 'mask=INSIDE+COVEREDBY+EQUAL querytype=window') = 'TRUE');

      If I create a view which does not use the simplify function the query works.
        • 1. Re: ORA13226 when using view with geometry created using sdo_util.simplify
          Simon Greener
          tcbalent,

          This is correct behaviour if you are trying the query the view:
          Select ID
            From V_MyTable A 
           where (MDSYS.SDO_RELATE(A.GEOMETRY, SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(73.091805, 18.312310, 72.250051, 19.076872)), 'mask=INSIDE+COVEREDBY+EQUAL querytype=window') = 'TRUE');
          This is because the A.GEOMETRY is not the geometry in the original table (MyTable) but a programmatically
          constructed geometry on the fly which is not indexed.

          You can fix this with a function based index as follows (NOTE: This uses my own test data that is projected and not geodetic):
          DROP  table myTable;
          create table myTable
          as 
          select rownum as Id, geom as geometry
           from  projpoly2d
           where polytype NOT IN ('COMPOUNDOUTERSHELL','VERTEXWITHARCNOHOLE');
          
          delete from user_sdo_geom_metadata where table_name = 'MYTABLE' and column_name = 'GEOMETRY'; commit;
          insert into user_sdo_geom_metadata(table_name, column_name,diminfo,srid)
          select 'MYTABLE','GEOMETRY',diminfo,srid
            from user_sdo_geom_metadata 
           where table_name = 'PROJPOLY2D' 
             and column_name = 'GEOM'; 
          commit;
           
          create or replace view v_MyTable
          as
          select id, sdo_util.simplify(geometry,20,2) as S_geometry
           from MyTable;
           
           -- Create index metadata
          delete from user_sdo_geom_metadata where table_name = 'MYTABLE' and column_name = 'MDSYS.SDO_UTIL.SIMPLIFY(GEOMETRY,20,2)'; commit;
          insert into user_sdo_geom_metadata(table_name, column_name,diminfo,srid)
          select 'MYTABLE','MDSYS.SDO_UTIL.SIMPLIFY(GEOMETRY,20,2)',diminfo,srid
            from user_sdo_geom_metadata 
           where table_name = 'MYTABLE' and column_name = 'GEOMETRY'; 
          commit;
          select * from user_sdo_geom_metadata;
          -- Now create index
          drop   index MyTable_sgeometry_spdx;
          create index MyTable_sgeometry_spdx on MyTable(MDSYS.SDO_UTIL.SIMPLIFY(GEOMETRY,20,2))
               indextype is mdsys.spatial_index parameters('sdo_indx_dims=2, layer_gtype=polygon');
          
          Select ID 
            From v_MyTable A 
           where (MDSYS.SDO_RELATE(A.S_GEOMETRY,
                                   MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), MDSYS.SDO_ORDINATE_ARRAY(190000.0, 5120000.0, 640000.0, 5630000.0)),
                                  'mask=INSIDE+COVEREDBY+EQUAL querytype=window') = 'TRUE');
          -- Result
          ID                     
          ---------------------- 
          5                      
          1                      
          4                      
          2                      
          6
          If this fixes your problem please mark this thread as answered.

          regards
          Simon