Optimized Rectangle to 5 Point Polygon

 CREATE OR REPLACE
 FUNCTION Rectangle2Polygon(p_geometry IN mdsys.sdo_geometry)
   RETURN mdsys.sdo_geometry deterministic
 AS
   v_dims             pls_integer;
   v_vertices         mdsys.vertex_set_type;
   v_ordinates        mdsys.sdo_ordinate_array  := NEW mdsys.sdo_ordinate_array(NULL);
   v_elem_info        mdsys.sdo_elem_info_array := NEW mdsys.sdo_elem_info_array(NULL);
   v_ord_count        pls_integer;
   v_num_elements     pls_integer;
   v_num_sub_elements pls_integer;
   v_element          sdo_geometry;
   -- A common utility function that allows for a single vertex
   -- to be added to the end of an sdo_ordinate_array
   -- All inserts are before existing vertex in same position
   --
   PROCEDURE appendVertex(p_ordinates IN OUT nocopy mdsys.sdo_ordinate_array,
                          p_dims      IN INTEGER,
                          p_x         IN NUMBER,
                          p_y         IN NUMBER,
                          p_z         IN NUMBER,
                          p_w         IN NUMBER)
   IS
     v_ord pls_integer := 0;
   BEGIN
     IF ( p_ordinates IS NULL ) THEN
       p_ordinates := NEW sdo_ordinate_array(0);
       p_ordinates.DELETE;
     END IF;
     v_ord := p_ordinates.COUNT + 1;
     p_ordinates.EXTEND(p_dims);
     -- Insert first vertex
     p_ordinates(v_ord)   := p_X;
     p_ordinates(v_ord+1) := p_Y;
     IF (p_dims>=3) THEN
        p_ordinates(v_ord+2) := p_z;
        IF ( p_dims > 3 ) THEN
            p_ordinates(v_ord+3) := p_w;
        END IF;
     END IF;
   END appendVertex;
   FUNCTION hasOptimizedRectangles(p_geometry IN sdo_geometry)
     RETURN INTEGER
   IS
     v_etype           pls_integer;
     v_interpretation  pls_integer;
     v_elements        pls_integer := 0;
     v_rectangle_count NUMBER := 0;
   BEGIN
     IF ( p_geometry.get_gtype() IN (1,2,5,6) ) THEN
       RETURN 0;
     END IF;
     v_elements := ( ( p_geometry.sdo_elem_info.COUNT / 3 ) - 1 );
     <<element_extraction>>
     FOR v_i IN 0 .. v_elements LOOP
       v_etype          := p_geometry.sdo_elem_info(v_i * 3 + 2);
       v_interpretation := p_geometry.sdo_elem_info(v_i * 3 + 3);
       IF ( ( v_etype IN (1003,2003) AND
              v_interpretation = 3 ) ) THEN
          v_rectangle_count := v_rectangle_count + 1;
       END IF;
     END LOOP element_extraction;
     RETURN v_rectangle_count;
   END hasOptimizedRectangles;
   FUNCTION hasCompoundElements(p_geometry IN sdo_geometry)
     RETURN INTEGER
   IS
     v_etype           pls_integer;
     v_elements        pls_integer := 0;
   BEGIN
     IF ( p_geometry.get_gtype() IN (1,2,5,6) ) THEN
       RETURN 0;
     END IF;
     v_elements := ( ( p_geometry.sdo_elem_info.COUNT / 3 ) - 1 );
     <<element_extraction>>
     FOR v_i IN 0 .. v_elements LOOP
       v_etype          := p_geometry.sdo_elem_info(v_i * 3 + 2);
       IF ( v_etype IN (1005,2005) ) THEN
          RETURN 1;
       END IF;
     END LOOP element_extraction;
     RETURN 0;
   END hasCompoundElements;
   PROCEDURE processElement(p_geometry  IN sdo_geometry,
                            p_ordinates IN OUT nocopy mdsys.sdo_ordinate_array,
                            p_elem_info IN OUT nocopy mdsys.sdo_elem_info_array)
   AS
     v_vertices         mdsys.vertex_set_type;
     v_interpretation   pls_integer;
     v_etype            pls_integer;
     v_start_coord      mdsys.vertex_type;
     v_end_coord        mdsys.vertex_type;
     v_temp_coord       mdsys.vertex_type;
     v_num_sub_elements pls_integer;
     v_sub_element      sdo_geometry;
     v_dims             pls_integer;
   BEGIN
     -- Can only be called if p_geometry contains at least one optimized rectangle.
     -- Count sub elements
     v_dims := p_geometry.get_dims();
     v_num_sub_elements := (p_geometry.sdo_elem_info.COUNT / 3);
     FOR i IN 1..v_num_sub_elements loop
       dbms_output.put_line('...... Processing Sub Element ' || i || ' of ' || v_num_sub_elements);
       v_sub_element    := sdo_util.EXTRACT(p_geometry,1,i);
       v_vertices       := sdo_util.getVertices(v_sub_element);
       v_interpretation := v_sub_element.sdo_elem_info(3);
       v_etype          := p_geometry.sdo_elem_info((i-1)*3+2);  /* Use Original etype 2003 for holes as extract turns holes into 1003 */
       -- Update elem_info_array
       p_elem_info.EXTEND(3);
       p_elem_info(p_elem_info.COUNT-2) := v_ordinates.COUNT + 1;
       -- If sub element not an optimized rectangle copy it through
       IF ( v_interpretation <> 3 ) THEN
         dbms_output.put_line('........ Skipping ' || i || ' sub element as is not optimized rectangle.');
         -- Copy through elem_info and ordinates
         p_elem_info(p_elem_info.COUNT-1) := v_sub_element.sdo_elem_info(2);
         p_elem_info(p_elem_info.COUNT)   := v_sub_element.sdo_elem_info(3);
         -- Now copy all ordinates
         FOR i IN 1..v_vertices.COUNT Loop
           appendVertex( p_ordinates, v_dims, v_vertices(i).x, v_vertices(i).y, v_vertices(i).z, v_vertices(i).w );
         END Loop;
         Continue;
       END IF;
       dbms_output.put_line('........ Converting Processing Found Optimized Rectangle.');
       -- Change to optimized rectangle in to a 5 vertex polygon
       p_elem_info(p_elem_info.COUNT-1) := CASE WHEN i=1 THEN v_sub_element.sdo_elem_info(2) ELSE 2003 END;
       p_elem_info(p_elem_info.COUNT)   := 1;
       v_start_coord := v_vertices(1);
       v_end_coord   := v_vertices(2);
       -- Check inverted only if first element
       IF ( i = 1 ) THEN
         v_temp_coord    := v_vertices(1);
         v_temp_coord.x  := least(v_start_coord.x,v_end_coord.x);
         v_temp_coord.y  := least(v_start_coord.y,v_end_coord.y);
         v_end_coord.x   := greatest(v_start_coord.x,v_end_coord.x);
         v_end_coord.y   := greatest(v_start_coord.y,v_end_coord.y);
         v_start_coord.x := v_temp_coord.x;
         v_start_coord.y := v_temp_coord.y;
       END IF;
       -- First coordinate
       appendVertex( p_ordinates, v_dims, v_start_coord.x, v_start_coord.y, v_start_coord.z, v_start_coord.w );
       -- Second coordinate
       IF ( v_etype = 1003 ) THEN
         appendVertex(p_ordinates,v_dims,v_end_coord.x,v_start_coord.y,(v_start_coord.z + v_end_coord.z) /2, v_start_coord.w);
       ELSE
         appendVertex(p_ordinates,v_dims,
                      v_start_coord.x,
                      v_end_coord.y,
                      (v_start_coord.z + v_end_coord.z) /2,
                      (v_end_coord.w - v_start_coord.w) * ((v_end_coord.x - v_start_coord.x) /
                      ((v_end_coord.x - v_start_coord.x) + (v_end_coord.y - v_start_coord.y)) ));
       END IF;
       -- 3rd or middle coordinate
       appendVertex(p_ordinates,v_dims,v_end_coord.x,v_end_coord.y,v_end_coord.z,v_end_coord.w);
       -- 4th coordinate
       IF ( v_etype = 1003 ) THEN
         appendVertex(p_ordinates,v_dims,v_start_coord.x,v_end_coord.y,(v_start_coord.z + v_end_coord.z) /2,v_start_coord.w);
       ELSE
         appendVertex(p_ordinates,v_dims,v_end_coord.x,v_start_coord.y,(v_start_coord.z + v_end_coord.z) /2,
             (v_end_coord.w - v_start_coord.w) * ((v_end_coord.x - v_start_coord.x) /
            ((v_end_coord.x - v_start_coord.x) + (v_end_coord.y - v_start_coord.y)) ));
       END IF;
       -- Last coordinate
       appendVertex(p_ordinates,v_dims,v_start_coord.x,v_start_coord.y,v_start_coord.z,v_start_coord.w);
       dbms_output.put_line('........ Finishing Converting Optimized Rectangle.');
     END Loop;
   END processElement;
 BEGIN
     IF ( p_geometry IS NULL ) THEN
       RETURN p_geometry;
     END IF;
     IF ( p_geometry.get_gtype() NOT IN (3,7) ) THEN
       RETURN p_geometry;
     END IF;
     IF ( hasOptimizedRectangles(p_geometry) = 0 ) THEN
       dbms_output.put_line('p_geometry does not have optimized rectangles.');
       RETURN p_geometry;
     END IF;
     IF ( hasCompoundElements(p_geometry) <> 0 ) THEN
       dbms_output.put_line('p_geometry has compound elements.');
       RETURN p_geometry;
     END IF; 
     v_ordinates.DELETE;
     v_elem_info.DELETE;
     v_dims         := p_geometry.get_dims();
     v_num_elements := mdsys.sdo_util.getNumElem(p_geometry);
     dbms_output.put_line('p_geometry has ' || v_num_elements || ' parts/elements.');
     FOR elem IN 1..v_num_elements loop
        v_element := mdsys.sdo_util.EXTRACT(p_geometry,elem,0);
        dbms_output.put_line('... Extracted part/element ' || elem || ' has ' || hasOptimizedRectangles(v_element) || ' optimized rectangles.');
        IF ( hasOptimizedRectangles(v_element) <> 0 ) THEN
          processElement(v_element,v_ordinates,v_elem_info);
        ELSE
          -- Append
         dbms_output.put_line('........ Appending non-optimized rectangle geometry to output geometry ');
         v_num_sub_elements := (v_element.sdo_elem_info.COUNT / 3);
         v_ord_count := v_ordinates.COUNT;
         FOR i IN 1..v_num_sub_elements loop
           v_elem_info.EXTEND(3);
           v_elem_info(v_elem_info.COUNT-2) := CASE WHEN i=1 THEN v_ord_count+1 ELSE v_element.sdo_elem_info(i) + v_ord_count END;
           v_ord_count := v_elem_info(v_elem_info.COUNT-2);
           v_elem_info(v_elem_info.COUNT-1) := v_element.sdo_elem_info(i+1);
           v_elem_info(v_elem_info.COUNT  ) := v_element.sdo_elem_info(i+2);
         END Loop;
         v_vertices       := sdo_util.getVertices(v_element);
         -- Now copy all ordinates
         FOR i IN 1..v_vertices.COUNT Loop
           appendVertex( v_ordinates, v_dims, v_vertices(i).x, v_vertices(i).y, v_vertices(i).z, v_vertices(i).w );
         END Loop;
        END IF;
     END LOOP;
     RETURN mdsys.sdo_geometry(p_geometry.sdo_gtype,p_geometry.sdo_srid,NULL,v_elem_info,v_ordinates);
 END Rectangle2Polygon;
 /
 SHOW errors

 -- Test
 SET serveroutput ON SIZE unlimited
 WITH tGeoms AS (
   SELECT 'Single rectangle no hole'      AS description, sdo_geometry(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(0,0,1,1)) AS geom FROM dual UNION ALL
   SELECT 'Inverted single rectangle'     AS description, sdo_geometry(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(1,1,0,0)) AS geom FROM dual UNION ALL
   SELECT 'Rectangle with rectangle hole' AS description, SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3, 5,2003,3),SDO_ORDINATE_ARRAY(0,0, 100,100, 40,40, 60,60)) AS geom FROM dual UNION ALL
   SELECT 'Compound polygon'              AS description, SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 7,2,2),SDO_ORDINATE_ARRAY(10.0,128.0, 10.0,125.0, 20.0,125.0, 20.0,128.0, 15.0,130.0, 10.0,128.0)) AS geom FROM dual UNION ALL
   SELECT 'Single Circle element'         AS description, SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,4),SDO_ORDINATE_ARRAY(15.0,145.0, 10.0,150.0, 20.0,150.0)) AS geom FROM dual UNION ALL
   SELECT 'No rectangle elements'         AS description, 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 geom FROM dual UNION ALL
   SELECT 'Multi-Polygon one rectangle'   AS description, SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1, 11,1003,3),SDO_ORDINATE_ARRAY(50.0,105.0, 55.0,105.0, 60.0,110.0, 50.0,110.0, 50.0,105.0, 62.0,108.0, 65.0,112.0)) AS geom FROM dual UNION ALL
   SELECT 'MultiPolygon All Rectangles'   AS description, SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3, 5,2003,3, 9,1003,3),SDO_ORDINATE_ARRAY(0,0,100,100, 40,40,60,60, 200,200,300,300)) AS geom FROM dual
 )
 SELECT f.description,
        f.validOriginalGeom,
        sdo_geom.validate_geometry(f.poly,0.005) AS vGeom,
        f.poly
   FROM (SELECT a.description,
                sdo_geom.validate_geometry(a.geom,0.005) AS validOriginalGeom,
                rectangle2polygon(a.geom) AS poly
           FROM tGeoms a
        ) f;