Optimized Rectangle to 5 Point Polygon
Here is a function that takes a any polygon whose elements are defined by an optimized rectangle and converts all elements to 5 vertex polygons. Polygon may be multi; may have non-optimized rectangle components. Does NOT process geometries with compound elements.
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
Now let’s test the function:
-- 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;
Results are as follows:
| DESCRIPTION | VALIDORIGINALGEOM | VGEOM | POLY |
|---|---|---|---|
| Single rectangle no hole | TRUE | TRUE | SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(0,0,1,0,1,1,0,1,0,0)) |
| Inverted single rectangle | TRUE | TRUE | SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(0,0,1,0,1,1,0,1,0,0)) |
| Rectangle with rectangle hole | TRUE | TRUE | SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1),SDO_ORDINATE_ARRAY(0,0,100,0,100,100,0,100,0,0,40,40,40,60,60,60,60,40,40,40)) |
| Compound polygon | TRUE | TRUE | SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,1,7,2,2),SDO_ORDINATE_ARRAY(10,128,10,125,20,125,20,128,15,130,10,128)) |
| Single Circle element | TRUE | TRUE | SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,4),SDO_ORDINATE_ARRAY(15,145,10,150,20,150)) |
| No rectangle elements | TRUE | TRUE | SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1,31,1003,1),SDO_ORDINATE_ARRAY(50,168,50,160,55,160,55,168,50,168,51,167,54,167,54,161,51,161,51,162,52,163,51,164,51,165,51,166,51,167,52,166,52,162,53,162,53,166,52,166)) |
| Multi-Polygon one rectangle | TRUE | TRUE | SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1,11,1003,1),SDO_ORDINATE_ARRAY(50,105,55,105,60,110,50,110,50,105,62,108,65,108,65,112,62,112,62,108)) |
| MultiPolygon All Rectangles | TRUE | TRUE | SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1,21,1003,1),SDO_ORDINATE_ARRAY(0,0,100,0,100,100,0,100,0,0,40,40,40,60,60,60,60,40,40,40,200,200,300,200,300,300,200,300,200,200)) |
I hope this is of use to someone.