Reducing 5 Vertex Polygon to Optimized Rectangle
I had occasion to convert a set of 5 vertex polygons that defined grid squares to their optimized rectangle equivalent.
To do this I created a simple function as follows.
create or replace
function Polygon2Rectangle( p_geometry in mdsys.sdo_geometry )
return mdsys.sdo_geometry deterministic as
v_vertices mdsys.vertex_set_type;
v_id pls_integer;
v_ords mdsys.sdo_ordinate_array := new mdsys.sdo_ordinate_array(null);
begin
v_ords.DELETE;
v_vertices := mdsys.sdo_util.getVertices(p_geometry);
if ( v_vertices.COUNT <> 5 ) Then
return p_geometry;
End If;
<<for_all_vectors>>
FOR v_id in v_vertices.FIRST..(v_vertices.LAST-1) LOOP
if ( v_id in (1,3) ) then
v_ords.EXTEND(2);
v_ords(v_ords.COUNT-1) := v_vertices(v_id).x;
v_ords(v_ords.COUNT) := v_vertices(v_id).y;
end if;
END LOOP for_all_vectors;
return mdsys.sdo_geometry(p_geometry.sdo_gtype,
p_geometry.sdo_srid,
p_geometry.sdo_point,
mdsys.sdo_elem_info_array(1,1003,3),
v_ords);
end Polygon2Rectangle;
Which can then used as follows:
select polygon2rectangle(sdo_geometry(2003,null,null,
sdo_elem_info_array(1,1003,1),
sdo_ordinate_array(1,1,10,1,10,10,1,10,1,1)))
as optimised_rectangle
from dual;
OPTIMISED_RECTANGLE
-----------------------------
MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1,10,10))
Update
On the basis of the excellent comments made by Farid Cheraghi, I have included his rectangle check (which could also have been done as a check of the diagonals of the rectangle), I have updated the function adding support for more complex sdo_geometry objects.
Note: The Oracle sdo_util.extract function, when it extracts inner rings (2003) renames them to 1003. Also, a complex element’s sdo_elem_info needs to be processed more carefully than is evident in the Oracle pl/sql functions.
create or replace
Function Polygon2Rectangle( p_geometry in mdsys.sdo_geometry,
p_tolerance in number default 0.005 )
Return mdsys.sdo_geometry DETERMINISTIC
AS
v_vertices mdsys.vertex_set_type;
v_ords mdsys.sdo_ordinate_array := new mdsys.sdo_ordinate_array(null);
v_num_elems pls_integer;
v_actual_etype pls_integer;
v_ring_elem_count pls_integer := 0;
v_ring mdsys.sdo_geometry;
v_num_rings pls_integer;
v_out_geom mdsys.sdo_geometry;
Function GetETypeAt(
p_geometry in mdsys.sdo_geometry,
p_element in pls_integer)
Return pls_integer
Is
v_num_elems number;
Begin
If ( p_geometry is not null ) Then
v_num_elems := ( ( p_geometry.sdo_elem_info.COUNT / 3 ) - 1 );
<<element_extraction>>
for v_i IN 0 .. v_num_elems LOOP
if ( (v_i+1) = p_element ) then
RETURN p_geometry.sdo_elem_info(v_i * 3 + 2);
End If;
end loop element_extraction;
End If;
Return NULL;
End GetETypeAt;
Function GetNumRings( p_geometry in mdsys.sdo_geometry,
p_ring_type in integer /* 0 = ALL; 1 = OUTER; 2 = INNER */ )
Return Number
Is
v_elements pls_integer := 0;
v_ring_count pls_integer := 0;
v_etype pls_integer;
v_ring_type pls_integer := case when ( p_ring_type is null OR
p_ring_type not in (0,1,2) )
Then 0
Else p_ring_type
End;
Begin
If ( p_geometry is not null ) Then
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 (1003,1005,2003,2005) and 0 = v_ring_type )
OR ( v_etype in (1003,1005) and 1 = v_ring_type )
OR ( v_etype in (2003,2005) and 2 = v_ring_type ) ) Then
v_ring_count := v_ring_count + 1;
End If;
END LOOP element_extraction;
End If;
Return v_ring_count;
End GetNumRings;
BEGIN
IF ( p_geometry is null ) THEN
RETURN p_geometry;
END IF;
-- Is polygon?
IF ( p_geometry.get_gtype() not in (3,7) ) THEN
RETURN p_geometry;
END IF;
v_num_elems := MDSYS.SDO_UTIL.GETNUMELEM(p_geometry);
<<all_elements>>
FOR v_elem_no IN 1..v_num_elems LOOP
-- Need to process and check all inner rings
--
-- Process all rings in the extracted single - 2003 - polygon
v_num_rings := GetNumRings(MDSYS.SDO_UTIL.EXTRACT(p_geometry,v_elem_no),0);
<<All_Rings>>
FOR v_ring_no IN 1..v_num_rings LOOP
v_ring := MDSYS.SDO_UTIL.EXTRACT(p_geometry,v_elem_no,v_ring_no);
v_actual_etype := GetEtypeAt(p_geometry,(v_ring_elem_count+1));
v_ring_elem_count := v_ring_elem_count + v_ring.sdo_elem_info.COUNT / 3;
IF ( v_ring is not null ) THEN
IF ( v_ring.sdo_elem_info(2) = 1003 AND
v_ring.sdo_elem_info(2) <> v_actual_etype ) THEN
-- Replace etype as Oracle extracts 2003 as 1003
v_ring.sdo_elem_info(2) := v_actual_etype;
End If;
v_vertices := mdsys.sdo_util.getVertices(v_ring);
IF ( v_vertices.COUNT = 5 ) THEN
-- Do the five vertices form a rectangle?
-- inner product of two consequent vector must equals to zero
IF ( (v_vertices(1).x-v_vertices(2).x) * (v_vertices(2).x-v_vertices(3).x) +
(v_vertices(1).y-v_vertices(2).y) * (v_vertices(2).y-v_vertices(3).y) = 0 AND
(v_vertices(3).x-v_vertices(4).x) * (v_vertices(4).x-v_vertices(5).x) +
(v_vertices(3).y-v_vertices(4).y) * (v_vertices(4).y-v_vertices(5).y) = 0 ) THEN
v_ring.sdo_elem_info(1) := 1;
v_ring.sdo_elem_info(3) := 3;
v_ords.DELETE;
v_ords.EXTEND(4);
v_ords(1) := v_vertices(1).x;
v_ords(2) := v_vertices(1).y;
v_ords(3) := v_vertices(3).x;
v_ords(4) := v_vertices(3).y;
v_ring := mdsys.sdo_geometry(v_ring.sdo_gtype,
v_ring.sdo_srid,
v_ring.sdo_point,
v_ring.sdo_elem_info,
v_ords);
END IF;
END IF;
IF ( v_out_geom is null ) THEN
v_out_geom := v_ring;
ELSE
v_out_geom := mdsys.sdo_util.APPEND(v_out_geom,v_ring);
END IF;
END IF;
END LOOP All_Rings;
END LOOP all_elements;
RETURN mdsys.sdo_geometry(p_geometry.sdo_gtype,
p_geometry.sdo_srid,
p_geometry.sdo_point,
v_out_geom.sdo_elem_info,
v_out_geom.sdo_ordinates);
END Polygon2Rectangle;
Testing…..
select sdo_geom.validate_geometry(a.geometry,0.005) as valid,
polygon2rectangle(a.geometry) as optimised
from (select sdo_geometry(2003,null,null, sdo_elem_info_array(1,1003,1), sdo_ordinate_array(1,1,10,1,10,10,1,10,1,1)) as geometry from dual
union all select sdo_geometry(2007,null,null,sdo_elem_info_array(1,1003,1,11,1003,1,21,2003,3), sdo_ordinate_array(1,1,10,1,10,10,1,10,1,1, 15,15,25,15,25,25,15,25,15,15, 18,18,20,20)) as geometry from dual
union all select sdo_geometry(2007,null,null,sdo_elem_info_array(1,1003,1,11,1003,1,21,2003,1), sdo_ordinate_array(1,1,10,1,10,10,1,10,1,1, 15,15,25,15,25,25,15,25,15,15, 16,16,16,24,24,24,24,16,16,16)) as geometry from dual
union all select sdo_geometry(2003,null,null,sdo_elem_info_array(1,1005,2, 1,2,1, 5,2,2, 11, 2003, 1), sdo_ordinate_array(6,10, 10,1, 14,10, 10,14, 6,10, 8,8,8,12,12,12,12,8,8,8)) as geometry from dual
union all select sdo_geometry(2003,null,null,sdo_elem_info_array(1,1003,2,11,2003,1), sdo_ordinate_array(15.0, 115.0, 20.0, 118.0, 15.0, 120.0, 10.0, 118.0, 15.0, 115.0, 13.6,116.7,13.6,118.3,16.3,118.3,16.3,116.7,13.6,116.7)) as geometry from dual
) a;
Valid Optimised
----- ---------
TRUE MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1,10,10))
TRUE MDSYS.SDO_GEOMETRY(2007,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3,5,1003,3,9,2003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1,10,10,15,15,25,25,18,18,20,20))
TRUE MDSYS.SDO_GEOMETRY(2007,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3,5,1003,3,9,2003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1,10,10,15,15,25,25,16,16,24,24))
TRUE MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,1,5,2,2,11,2003,3),MDSYS.SDO_ORDINATE_ARRAY(6,10,10,1,14,10,10,14,6,10,8,8,12,12))
TRUE MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,2,11,2003,3),MDSYS.SDO_ORDINATE_ARRAY(15,115,20,118,15,120,10,118,15,115,13.6,116.7,16.3,118.3))
I hope this function helps someone.