SpatialDB Advisor

ESRI ArcSDE Exverted and Inverted Polygons and Oracle Spatial

Anyone who has used an ESRI client software to edit data which is then stored in Oracle via ArcSDE has probably come across some peculiarly organised polygons that pass ArcSDE validation but not Oracle’s.

(Note: Both products are OGC Simple Features Specification compliant. So why is this? In the main this is because ESRI’s ArcSDE pre-existed the OGC and SQL/MM standards. It would seem to me that the standards compliance process only tests a product’s adherence to the declared aspects of the standard: they do not look at support for features or functions not declared as part of the standard. Nor do they demand that features not in the standard be removed. This could be construed as being a loop-hole allowing vendors to “embrace and extend” the standard, but in this situation it is simply a case of something historically pre-existing the standard.)

These peculiarly organised polygons are inverted or exverted polygons that have their genesis in the original programming of the Spatial DataBase Engine (SDBE) 1.0 by Geomatic Technologies Incorporated (GTI) based in Bellingham, Washington (a company owned by the brilliant Mike Butler) before its sale to ESRI.

(As side node: GTI produced SDBE only for Interbase. One of the main features of Interbase was an Array datatype which GTI used to store the coordinates of a spatial feature in the POINTS column of the Fnnn table. When ESRI bought SDBE and ported it to Oracle they decided to store the points in a LONG RAW column making it impossible for a non-ArcSDE client to access and understand without some effort at reverse engineering. PL/SQL has no functions that allow a programmer to see “inside” a LONG RAW based SDEBINARY geometry.)

I am sure inverted polygons exist in ArcSDE (and have double checked the original “Introduction to ArcSDE” course notes I wrote for ESRI Inc back in 1995) but I cannot remember if exverted polygons were a part of ArcSDE. When I held the position of GIS Manager at my last employer, my team constantly had problems with ArcInfo creating bow tie (ie exverted) polygons which ArcSDE would pass and store in Oracle (we used SDO_GEOMETRY storage) yet would not pass our daily PL/SQL based DBMS_JOB that checked all spatial edits done that day. Because of this I have included discussion of exverted (eg bow tie ) polygons in this article.

OK, since a picture is worth a thousand words, let’s have a look at these polygons.

Firstly, let’s create a table to hold our data.

drop   table inverted;
delete from user_sdo_geom_metadata where table_name = 'INVERTED';
commit;
create table inverted (
  oid integer, 
  geom_type varchar2(25), 
  geom sdo_geometry
);
insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid)
values ('INVERTED','GEOM',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(X,0,5000,0.05),MDSYS.SDO_DIM_ELEMENT(Y,0,5000,0.05)),NULL);
commit;

Now, let’s create a singly inverted polygon.

insert into inverted values(1,'Singly inverted polygon',
sdo_geometry(2003,null,null,
  sdo_elem_info_array(1,1003,1),
  sdo_ordinate_array(100,0,400,0,400,150,250,100,250,200,400,150,400,300,100,300,100,0)));

This polygon looks like this.

Now, let’s create a doubly inverted polygon.

insert into inverted values(2,'Doubly inverted polygon',
sdo_geometry(2003,null,null,
  sdo_elem_info_array(1,1003,1),
  sdo_ordinate_array(450,0,800,0,800,150,725,100,650,150,575,100,500,150,575,200,650,150,725,200,800,150,800,300,450,300,450,0)));

This polygon looks like this.

Now let’s create a simple, exverted polygon.

insert into inverted values(3,'Exverted Polygon',
sdo_geometry(2003,null,null,
  sdo_elem_info_array(1,1003,1),
  sdo_ordinate_array(125,400,200,400,200,450,275,425,275,475,200,450,200,525,125,525,125,400)));

This polygon looks like this.

Finally, though unnecessary (as the previous exverted polygon is also a “bow tie”), let’s create a classic Bow Tie (exverted) polygon.

insert into inverted values(4,'Bow Tie',
  sdo_geometry(2003,null,null,
  sdo_elem_info_array(1,1003,1),
  sdo_ordinate_array(400,400,575,475,725,400,725,550,575,475,400,550,400,400)));
commit;

This polygon looks like this.

Now let’s check the status of these geometries

select i.oid,
       i.geom_type,
       substr(sdo_geom.validate_geometry(i.geom,0.5),1,6) as valid,
       i.geom.sdo_elem_info
  from inverted i;

OID                    GEOM_TYPE                 VALID  GEOM.SDO_ELEM_INFO
---------------------- ------------------------- ------ ------------------
1                      Singly inverted polygon   13349  NUMBER(1,1003,1) 
2                      Doubly inverted polygon   13349  NUMBER(1,1003,1)
3                      Exverted Polygon          13349  NUMBER(1,1003,1)
4                      Bow Tie                   13349  NUMBER(1,1003,1)

4 rows selected

Note that all of them are described in the SDO_ELEM_INFO array by a single outer shell (ie 1003). This is correct with respect to the organisation of inverted and exverted polygons. For those not used to Oracle, the error number 13349 is described as follows:

Of course, because they are not OGC/SFS or SQL/MM compliant polygons, they fail Oracle’s validation software.

Now, check what happens when they are passed through Oracle’s own, internal, Sdo_Util.Rectify_Geometry routine.

select geom_type,
       substr(sdo_geom.validate_geometry(geom,0.5),1,6) as ovalid,
       r.geom.sdo_elem_info as oelements,
       r.rectgeom.sdo_elem_info as relements,
       substr(sdo_geom.validate_geometry(r.rectgeom,0.5),1,10) as rvalid
  from (select i.geom_type,
               i.geom,
               sdo_util.rectify_geometry(i.geom,0.5) as rectgeom
          from inverted i )r;

GEOM_TYPE                OVALID OELEMENTS          RELEMENTS                            RVALID 
------------------------ ------ ------------------ ------------------------------------ ------
Singly inverted polygon  13349  NUMBER(1,1003,1)   NUMBER(1,1003,1,13,2003,1)           TRUE
Doubly inverted polygon  13349  NUMBER(1,1003,1)   NUMBER(1,1003,1,13,2003,1,23,2003,1) TRUE 
Exverted Polygon         13349  NUMBER(1,1003,1)   NUMBER(1,1003,1,9,1003,1)            TRUE
Bow Tie                  13349  NUMBER(1,1003,1)   NUMBER(1,1003,1,9,1003,1)            TRUE

4 rows selected

They are corrected. Normally one would correct these either in the client or in the database via a trigger or a DBMS_SCHEDULER task run at the end of each day to check the day’s edits. Fixing via interactive SQL executed in SQLPlus or SQL Developer would occur as follows:

update inverted i
   set i.geom = sdo_util.rectify_geometry(i.geom,0.5)
 where sdo_geom.validate_geometry(i.geom,0.5) <> 'TRUE';

Whilever these geometries exist in the database uncorrected, what is the effect on geoprocessing results?

Let’s check.

First, are buffers correctly formed?

select oid,
       geom_type,
       sdo_geom.validate_geometry(sdo_geom.sdo_buffer(i.geom,10,0.05),0.5) as bValie 
  from inverted i;

OID                    GEOM_TYPE                 BVALIE
---------------------- ------------------------- ------
1                      Singly inverted polygon   TRUE
2                      Doubly inverted polygon   TRUE
3                      Exverted Polygon          TRUE
4                      Bow Tie                   TRUE

4 rows selected

Yes they are. Secondly let’s create a test, overlay geometry.

insert into inverted values(5,'Overlay Geometry',
sdo_geometry(2003,null,null,
  sdo_elem_info_array(1,1003,1),
  sdo_ordinate_array(423,-87,880,-98,867,119,429,158,423,-87)));
commit;

Which looks like this (over oid = 2):

Firstly let’s union (SDO_GEOM.SDO_UNION) this geometry with the ‘Doubly Inverted Polygon’ (oid 2).

select i1.oid,
       sdo_geom.validate_geometry(sdo_geom.sdo_union(i1.geom,i2.geom,0.05),0.05) as uValid
  from inverted i1,
       inverted i2
 where i1.oid = 2
   and i2.oid = 5;

OID                    UVALID
---------------------- ------
2                      TRUE 

1 rows selected

Which (correctly) looks like:

Now let’s intersect (SDO_GEOM.SDO_INTERSECTION) this geometry with the ‘Doubly Inverted Polygon’ (oid 2).

select il.oid,
       sdo_geom.validate_geometry(sdo_geom.sdo_intersection(i1.geom,i2.geom,0.05),0.05) as intValid
  from inverted i1,
       inverted i2
 where i1.oid = 2
   and i2.oid = 5;

OID                    INTVALID
---------------------- --------
2                      TRUE 

1 rows selected

Which (correctly) looks like:

Of course, one should really only conduct geoprocessing on valid geometries, but if you have to do so on invalid geometries, please check the results oneself as Oracle, I believe, does not guarantee the results of processing when the geoemtry data is invalid.

I hope this article is of use to someone.