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.
!http://www.spatialdbadvisor.com/images/44.png (Polygon with inverted outer shell)!
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.
!http://www.spatialdbadvisor.com/images/47.png (Polygon with doubly inverted outer shell)!
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.
!http://www.spatialdbadvisor.com/images/45.png (Polygon with exverted outer shell)!
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.
!http://www.spatialdbadvisor.com/images/46.png (Polygon with exverted outer shell – A classic bow tie)!
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:
!http://www.spatialdbadvisor.com/images/48.png (Oracle Error 13349 produced by Sdo_Geom.Validate_Geometry)!
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):
!http://www.spatialdbadvisor.com/images/50.png (Polygon overlaying Doubly Inverted Inverted Polygon)!
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:
!http://www.spatialdbadvisor.com/images/53.png (Union With Double Inverted Polygon)!
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:
!http://www.spatialdbadvisor.com/images/52.png (Intersection With Double Inverted Polygon)!
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.