OracleSpatialRestrictBadData

From NGDCWiki

Jump to: navigation, search

Hongming wrote the following:

Tom/Ken,

I have one issue discuss with you guys. It is about the polygon verification. I want to know if the incorrect ploygon like clockwise polygon will mess up other correct ploygon in the same table and if the correct polygon can be queried out from this table with incorrect polygon. I found there was the query error while I tried to query the data from the table with incorrect polygon. Sometimes our analysts will input some clockwise polygon into data set, so this case will affect our loading process. Is there the simple way in Oracle Spatial to find the clockwise polygon from the table, convert it and then insert it into table again? It looks like that the Oracle Spatial function valid_geometry_with_context couldn't seperate this case with other invalidate cases. If there is no such existing function, we'd like to save these clockwise polygons, convert them and load them later. In addition, I think that it's better to roll back invalid inserted polygon from the table if they would mess up other valid polygon during the query.

Thanks! Hongming

==========================================

~~#FF0000:Note to the reader:

The trigger creation at the very bottom of this page is a good example of how the sdo_geom.validate_geometry_with_context function can be used to prevent bad data from ever entering a database table. Please note, however, that Oracle has put what I think is an odd restriction on this function. As documented in chapter 11 of the Oracle Spatial User's Guide and Reference, Release 9.2, the spatial bounds for a given layer are checked only if the tolerance specified in the call to sdo_geom.validate_geometry_with_context are zero. With a non-zero tolerance, any goofy numbers for simple x,y points are accepted. Testing is always a good idea! You've been warned!~~ (:razz:)

Here is a brief summary of how the sdo_geom.validate_geometry_with_context works when called in its different forms and with different tolerances:

Note that SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT performs all the checks of VALIDATE_GEOMETRY_WITH_CONTEXT in addition to checking SRID and SDO_GTYPE as specified in the metadata view.

~pp~ select count(*) from nosa.mesonet_tsqp a where sdo_geom.validate_geometry_with_context(a.shape, 0.0005) != 'TRUE'; -- 0, because of the 0.0005 tolerance is invalidating any bounds checking.

select count(*) from nosa.mesonet_tsqp a where sdo_geom.validate_geometry_with_context(a.shape, 0.0) != 'TRUE'; -- get ORA-13011, value is out of range because of the 0.0 tolerance

select count(*) from nosa.mesonet_tsqp a where sdo_geom.validate_geometry_with_context(a.shape, mdsys.SDO_DIM_ARRAY(mdsys.SDO_DIM_ELEMENT(NULL, -180, 180, 0),

                   mdsys.SDO_DIM_ELEMENT(NULL, -90, 90, 0))) != 'TRUE';

-- 1, which is correct. Use this form always. ~/pp~

==========================================

1. Create a spatial table and make the objectid column unique.

~pp~ drop table hongming_test; create table hongming_test (description varchar2(50),

                           objectid        number(38),
                           shape           mdsys.sdo_geometry);

alter table hongming_test add unique (objectid); ~/pp~

2. Insert some rows into this test table. All of the rows will be polygons, and some will be correct and some incorrect.

~pp~ insert into hongming_test values ('Correct polygon', 1,

     mdsys.SDO_GEOMETRY(2003, 8265, NULL, 
                        mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                        mdsys.SDO_ORDINATE_ARRAY(-110, -10, -95, -10, -95, 10, -110, 10, -110, -10)));

commit; ~/pp~

3. Insert a row into the Oracle metadata table.

~pp~ delete from user_sdo_geom_metadata where table_name = 'HONGMING_TEST'; insert into user_sdo_geom_metadata values (

  'HONGMING_TEST','SHAPE',
  mdsys.SDO_DIM_ARRAY(mdsys.SDO_DIM_ELEMENT(NULL, -180, 180, .0005), 
                      mdsys.SDO_DIM_ELEMENT(NULL, -90, 90, .0005)),
     8265);

commit; ~/pp~

4. Build a generic R-tree index on this table and column.

~pp~ drop index hongming_test_spidx1 force; create index hongming_test_spidx1 on hongming_test(shape) indextype is mdsys.spatial_index; ~/pp~


5. Validate this table with context to see what shows up. Nothing should, as the polygon that was entered was in a CCW order and the points are not coincident at all.

~pp~ select count(*) from hongming_test c

     where SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape, 0.005) != 'TRUE';

~/pp~

-- The result is 0, as expected.

6. Now, insert two bad rows. One row will have the same polygon as the first one, but this time it will be inserted in clockwise order. The other row will be very similar to the first one, except that an extra point will be added, and one of these points will be identical to another.

~pp~ insert into hongming_test values ('Polygon in CW order', 2,

     mdsys.SDO_GEOMETRY(2003, 8265, NULL, 
                        mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                        mdsys.SDO_ORDINATE_ARRAY(-120, -20, -120, 20, -95, 30, -95, -15, -120, -20)));

insert into hongming_test values ('Polygon with duplicate point', 3,

     mdsys.SDO_GEOMETRY(2003, 8265, NULL, 
                        mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                        mdsys.SDO_ORDINATE_ARRAY(-150, -5, -75, -10, -75, -10, -100, 12, -110, 10, -150, -5)));

commit; ~/pp~

7. Do the query that checks the valid geometries:

~pp~ select count(*) from hongming_test c

     where SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape, 0.005) != 'TRUE';

~/pp~

-- The result is 2, as expected.


8. Get the details of these invalid rows.

~pp~ column valid_description format a30 select c.description, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape, 0.005) valid_description from hongming_test c; ~/pp~

Here are the results: ~pp~ DESCRIPTION VALID_DESCRIPTION


------------------------------

Correct polygon TRUE Polygon in CW order 13367 [Element <1>] [Ring <1>] Polygon with duplicate point 13356 [Element <1>] [Coordinat

                                                  e <3>][Ring <1>]

~/pp~

9. Try doing the same geometry validation check on the fly.

~pp~ select SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(

     mdsys.SDO_GEOMETRY(2003, 8265, NULL, 
                        mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                        mdsys.SDO_ORDINATE_ARRAY(-110, -10, -110, 10, -95, 10, -95, -10, -110, -10)),
     mdsys.SDO_DIM_ARRAY(mdsys.SDO_DIM_ELEMENT(NULL, -180, 180, .0005), 
                      mdsys.SDO_DIM_ELEMENT(NULL, -90, 90, .0005))

) from dual; ~/pp~ -- Works good. Returns an invalid description, as it should.

~pp~ select SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(

     mdsys.SDO_GEOMETRY(2003, 8265, NULL, 
                        mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                        mdsys.SDO_ORDINATE_ARRAY(-110, -10, -95, -10, -95, 10, -110, 10, -110, -10)),
     mdsys.SDO_DIM_ARRAY(mdsys.SDO_DIM_ELEMENT(NULL, -180, 180, .0005), 
                      mdsys.SDO_DIM_ELEMENT(NULL, -90, 90, .0005))

) from dual; ~/pp~ -- Works good. Returns TRUE as it should.


10. Place the on-the-fly check into a before-row insert or update trigger on this particular table.

~pp~ drop trigger hongming_test_bir; CREATE OR REPLACE TRIGGER hongming_test_bir

      BEFORE INSERT or update ON hongming_test FOR EACH ROW

declare

 l_valid_geom_result      varchar2(200);
 l_error_code             number;

BEGIN

 -- This test trigger will make sure that the spatial object being
 -- inserted or updated is valid.  This trigger will simply do a
 -- select of the function sdo_geom.validate_geometry_with_context
 -- and examine the result.
 --
 -- If the result is anything other than 'TRUE', then raise an 
 -- application error.  This exception will be raised in the
 -- calling program and prevent the particular row from being
 -- inserted or updated.
 -- Do a select from the dual table using the 
 -- sdo_geom.validate_geometry_with_context function.  Be sure to store
 -- the error code that results from this query in case we need to refer
 -- to it when raising the error.
 select SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(:new.shape,
     mdsys.SDO_DIM_ARRAY(mdsys.SDO_DIM_ELEMENT(NULL, -180, 180, .0005), 
                      mdsys.SDO_DIM_ELEMENT(NULL, -90, 90, .0005))) into l_valid_geom_result from dual;
 -- Examine the return value from this select and raise an exception if necessary.
 if l_valid_geom_result != 'TRUE' then
   select -1 * to_number(substr(l_valid_geom_result,1,instr(l_valid_geom_result,' '))) into l_error_code from dual;
   raise_application_error(-20000, 'Invalid geometry: ' || sqlerrm(l_error_code));
 end if;

END; / ~/pp~

11. Let's insert a good row after the trigger has been created.

~pp~ insert into hongming_test values ('Another correct polygon', 4,

     mdsys.SDO_GEOMETRY(2003, 8265, NULL, 
                        mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                        mdsys.SDO_ORDINATE_ARRAY(-110, -10, -95, -10, -95, 10, -110, 10, -110, -10)));

~/pp~ -- This row is accepted. Good!

12. Now, insert a bad row after the trigger has been created. ~pp~ insert into hongming_test values ('Another polygon with duplicate point', 5,

     mdsys.SDO_GEOMETRY(2003, 8265, NULL, 
                        mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                        mdsys.SDO_ORDINATE_ARRAY(-150, -5, -75, -10, -75, -10, -100, 12, -110, 10, -150, -5)));

~/pp~ -- This row is rejected, as it should be.

13. Update all of the rows so that they are all good. ~pp~ update hongming_test set description = 'Correct polygon',

                        shape = 
     mdsys.SDO_GEOMETRY(2003, 8265, NULL, 
                        mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                        mdsys.SDO_ORDINATE_ARRAY(-110, -10, -95, -10, -95, 10, -110, 10, -110, -10));

~/pp~ -- All of these updates are accepted.

14. Update all of the rows so that they are all bad. ~pp~ update hongming_test set description = 'Bad polygon',

                        shape = 
     mdsys.SDO_GEOMETRY(2003, 8265, NULL, 
                        mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                        mdsys.SDO_ORDINATE_ARRAY(-150, -5, -75, -10, -75, -10, -100, 12, -110, 10, -150, -5));

~/pp~ -- Good. The first one is rejected, thus rejected all of them. This is expected.

Personal tools