Extracting Inner Rings Changed Ordinate Ordering: A Trap For Players Who Don’t Read Documentation!
In Developing Some Ideas For The Compression/Decompression Of Sdo_Geometry Objects, I Had Cause To Create Two PL/SQL Functions called Geocompress and Geodecompress.
I Have Not Done This For A While And Slapped Myself When I Ran Into something I Should Have Known Would Arise, as I have seen it before, and because it is documented as happening!
The issue came about when using the Sdo_Util.Extract function for processing the inner rings (holes) of a polygon:
SDO_UTIL.EXTRACT( geometry IN SDO_GEOMETRY, element IN NUMBER, ring IN NUMBER DEFAULT 0 ) RETURN SDO_GEOMETRY;
Description
Returns The Two-Dimensional Geometry That Represents A Specified Element (And Optionally A Ring) Of The Input Two-Dimensional Geometry.
….
For A Polygon With One Or More Holes, The Returned Geometry Representing An Extracted Interior Ring Is Reoriented So That Its Vertices Are Presented In Counterclockwise Order .
Let’s take a look at a multipolygon with two outer rings with the first having a single inner ring/hole:
WITH Tgeom AS ( SELECT Sdo_Geometry(2007,NULL,NULL, Sdo_Elem_Info_Array( 1,1003,1, 11,2003,1, 31,1003,1), SDO_ORDINATE_ARRAY(50.0,168.0, 50.0,160.0, 55.0,160.0, 55.0,168.0, 50.0,168.0, 51.0,167.0, 54.0,167.0, 54.0,161.0, 51.0,161.0, 51.0,162.0, 52.0,163.0, 51.0,164.0, 51.0,165.0, 51.0,166.0, 51.0,167.0, 52.0,166.0, 52.0,162.0, 53.0,162.0, 53.0,166.0, 52.0,166.0)) AS original FROM dual ) SELECT e.Column_value AS element, r.Column_value AS ring, sdo_util.EXTRACT(a.original,e.Column_Value,r.column_value) AS eGeom FROM Tgeom A, TABLE(Geom.Generate_Series(1,Sdo_Util.Getnumelem(A.Original),1)) E, TABLE(geom.Generate_Series(1,Sdo_Util.GetNumRings(sdo_util.EXTRACT(a.original,e.Column_Value)),1)) r; ELEMENT RING EGEOM ------- ---- ---------------------------------------------------------------------------------------------------------------------------------------------------- 1 1 SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(50,168,50,160,55,160,55,168,50,168)) 1 2 Sdo_Geometry(2003,NULL,NULL,Sdo_Elem_Info_Array(1,1003,1),Sdo_Ordinate_Array(51,167,51,166,51,165,51,164,52,163,51,162,51,161,54,161,54,167,51,167)) 2 1 SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(52,166,52,162,53,162,53,166,52,166))
Notice how the EXTRACT function reverses the ordinates for the first polygon’s inner ring. An inner ring (2003) in a polygon always has clockwise orientation while an outer boundary (1003) always has anti-clockwise orientation.
Thus, EXTRACT’s reversing of the inner ring (2003) is quite correct as an extracted single polygon must have an outer ring (1003) which must have clockwise orientation.
However, If You Are Using Extract To Implement Some Sort Of Geoprocessing For A Polygon That Requires You To Extract The Individual Elements/Rings, Process Them, And Then Put The Geometry Back Together, You Must Take Into Account This Reversing Of Inner Rings,
when putting the polygon geometry back together. This can be done using the SDO_UTIL.REVERSE_LINESTRING function.
Note, you cannot use the reverse_linestring function on a polygon:
WITH Tgeom AS ( SELECT Sdo_Geometry(2007,NULL,NULL, Sdo_Elem_Info_Array( 1,1003,1, 11,2003,1, 31,1003,1), SDO_ORDINATE_ARRAY(50.0,168.0, 50.0,160.0, 55.0,160.0, 55.0,168.0, 50.0,168.0, 51.0,167.0, 54.0,167.0, 54.0,161.0, 51.0,161.0, 51.0,162.0, 52.0,163.0, 51.0,164.0, 51.0,165.0, 51.0,166.0, 51.0,167.0, 52.0,166.0, 52.0,162.0, 53.0,162.0, 53.0,166.0, 52.0,166.0)) AS original FROM dual ) SELECT E.Column_Value AS Element, R.Column_Value AS Ring, CASE WHEN e.column_value > 1 THEN sdo_util.reverse_linestring(sdo_util.EXTRACT(a.original,e.Column_Value,r.column_value)) ELSE sdo_util.EXTRACT(a.original,e.Column_Value,r.column_value) END AS eGeom FROM Tgeom A, TABLE(Geom.Generate_Series(1,Sdo_Util.Getnumelem(A.Original),1)) E, TABLE(Geom.Generate_Series(1,Sdo_Util.Getnumrings(Sdo_Util.EXTRACT(A.Original,E.Column_Value)),1)) R;
We must “cast” the geometry to a linestring, reverse it, and the “cast” it back to a polyygon:
WITH Tgeom AS (
SELECT Sdo_Geometry(2007,NULL,NULL,
Sdo_Elem_Info_Array( 1,1003,1,
11,2003,1,
31,1003,1),
SDO_ORDINATE_ARRAY(50.0,168.0, 50.0,160.0, 55.0,160.0, 55.0,168.0, 50.0,168.0,
51.0,167.0, 54.0,167.0, 54.0,161.0, 51.0,161.0, 51.0,162.0, 52.0,163.0, 51.0,164.0, 51.0,165.0, 51.0,166.0, 51.0,167.0,
52.0,166.0, 52.0,162.0, 53.0,162.0, 53.0,166.0, 52.0,166.0)) AS original
FROM dual
)
SELECT E.Column_Value AS Element,
R.Column_Value AS Ring,
CASE WHEN E.Column_Value > 1
THEN Sdo_Geometry(
REPLACE(REPLACE(REPLACE(
Sdo_Util.Reverse_Linestring(
Sdo_Geometry(
REPLACE(REPLACE(REPLACE(
Sdo_Util.EXTRACT(A.Original,E.Column_Value,R.Column_Value).Get_Wkt(),
'POLYGON','LINESTRING'),'))',')'),'((','('),
A.Original.Sdo_Srid
)
).Get_Wkt(),
'LINESTRING','POLYGON'),')','))'),'(','(('),
a.original.sdo_srid)
ELSE Sdo_Util.EXTRACT(A.Original,E.Column_Value,R.Column_Value)
END AS eGeom
FROM Tgeom A,
TABLE(Geom.Generate_Series(1,Sdo_Util.Getnumelem(A.Original),1)) E,
TABLE(Geom.Generate_Series(1,Sdo_Util.Getnumrings(Sdo_Util.EXTRACT(A.Original,E.Column_Value)),1)) R;
ELEMENT RING EGEOM
------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------
1 1 SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(50,168,50,160,55,160,55,168,50,168))
1 2 SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(51,167,51,166,51,165,51,164,52,163,51,162,51,161,54,161,54,167,51,167))
2 1 SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(52,166,53,166,53,162,52,162,52,166))
Note, while we have done all the processing in SQL, the “trick” is really for use within PLSQL objects.
From the GeoCompress function:
... v_num_elements := MDSYS.SDO_UTIL.GETNUMELEM(p_geometry); <<all_elements>> FOR v_element_no IN 1..v_num_elements LOOP IF ( v_num_elements = 1 ) THEN v_element := p_geometry; ELSE v_element := MDSYS.SDO_UTIL.EXTRACT(p_geometry,v_element_no,0); IF ( v_element IS NULL ) THEN Continue; END IF; END IF; IF ( V_Element.Get_Gtype() = 3) THEN -- Process the outer ring and all inner rings of this polygon v_num_rings := sdo_util.GetNumRings(v_element); <<All_Rings>> FOR v_ring_no IN 1..v_num_rings LOOP V_Ring := Mdsys.Sdo_Util.EXTRACT(P_Geometry,V_Element_No,V_Ring_No); IF ( V_Ring_No > 1 ) THEN -- inner ring needs reversing V_Rgeom := Sdo_Geometry(V_Dims*1000+2,V_Ring.Sdo_Srid,NULL,Sdo_Elem_Info_Array(1,2,1),V_Ring.Sdo_Ordinates); V_Ring.Sdo_Ordinates := Mdsys.Sdo_Util.Reverse_Linestring(V_Rgeom).Sdo_Ordinates; END IF; ..... END LOOP; ELSE .... END IF; ... END LOOP;
I hope this reminder to read the documentation is of use to someone out there!