This discussion is archived
2 Replies Latest reply: Apr 17, 2012 7:46 PM by Simon Greener

# rouinding coordinates in SDO_GEOMETRY

Currently Being Moderated
Dear All!

I have a polygon, which SDO_GOEMETRY is:

MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-816008.499528741,-1071166.1245046,-815846.43719259, ....

Is it possible to round the coordinates (for 2 decimal places), that it looks like this:?

MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-815846.44,-1071166.12,-816008.50, ....

Thank You very much for Your help!

Katerina Figallova
• ###### 1. Re: rouinding coordinates in SDO_GEOMETRY
Currently Being Moderated
Hi Katerina

You can always search first on the forum, often there are already good solutions on it.

John O'Toole posted a couple of years a function to do so.

Re: SDO_ORDINATE_ARRAY precision control

Luc
• ###### 2. Re: rouinding coordinates in SDO_GEOMETRY
Currently Being Moderated
Katerina,

Luc is quite right.

There is a function available in my free PL/SQL packages (see http://www.spatialdbadvisor.com/source_code see GEOM or LINEAR Package Documentation).

Here it is standalone.
``````Create or Replace
Function RoundOrdinates(P_Geometry        In Mdsys.Sdo_Geometry,
P_X_Round_Factor  In Number,
p_y_round_factor  In Number Default null,
P_Z_Round_Factor  In Number Default Null,
p_m_round_factor  In Number Default null)
RETURN MDSYS.SDO_GEOMETRY
/** ----------------------------------------------------------------------------------------
* @function   : RoundOrdinates
* @precis     : Rounds ordinate values (sdo_ordinate_array) of an sdo_geometry
* @version    : 1.0
* @description: The function rounds ordinate values (sdo_ordinate_array) of an sdo_geometry
* @usage      : select RoundOrdinates(p_geometry) from dual
* @param      : p_geometry        : MDSYS.SDO_GEOMETRY : sdo_geometry whose ordinates will be rounded
* @param      : P_X_Round_Factor  : Number : X ordinate tolerance
* @return     : p_y_round_factor  : Number : Y ordinate tolerance
* @return     : P_Z_Round_Factor  : Number : Z ordinate tolerance
* @return     : p_m_round_factor  : Number : M ordinate tolerance
* @return     : modified geometry : MDSYS.SDO_GEOMETRY : SDO_Geometry with rounded ordinates
* @history    : Simon Greener - Jun 2010
**/
Is
c_i_null_tolerance CONSTANT INTEGER       := -20119;
c_s_null_tolerance CONSTANT VARCHAR2(100) := 'Input tolerance/dimarray must not be null';
c_i_null_geometry  CONSTANT INTEGER       := -20120;
c_s_null_geometry  CONSTANT VARCHAR2(100) := 'Input geometry must not be null';

v_ismeasured       boolean;
v_dim              pls_integer;
v_gtype            pls_integer;
v_measure_ord      pls_integer;
v_ord              pls_integer;
v_geometry         mdsys.sdo_geometry := p_geometry;
V_Ordinates        mdsys.Sdo_Ordinate_Array;
V_X_Round_Factor   Number := P_X_Round_Factor;
V_Y_Round_Factor   Number := Nvl(P_Y_Round_Factor,P_X_Round_Factor);
V_Z_Round_Factor   Number := Nvl(P_z_Round_Factor,P_X_Round_Factor);
V_W_Round_Factor   Number := NVL(p_m_round_factor,p_x_round_factor);
Begin
If ( p_x_round_factor Is Null ) Then
raise_application_error(c_i_null_tolerance,c_s_null_tolerance,true);
End If;
If ( p_geometry is null ) Then
raise_application_error(c_i_null_geometry,c_s_null_geometry,true);
End If;
V_Ismeasured := Case When Mod(Trunc(p_geometry.Sdo_Gtype/100),10) = 0 Then False Else True End;
v_gtype := Mod(p_geometry.sdo_gtype,10);
v_dim   := p_geometry.get_dims(); -- IF 9i then .... TRUNC(p_geometry.sdo_gtype/1000,0);
-- If point update differently to other shapes...
--
If ( V_Geometry.Sdo_Point Is Not Null ) Then
v_geometry.sdo_point.X := round(v_geometry.sdo_point.x,v_x_round_factor);
V_Geometry.Sdo_Point.Y := Round(V_Geometry.Sdo_Point.Y,V_Y_Round_Factor);
If ( v_dim > 2 ) Then
v_geometry.sdo_point.z := round(v_geometry.sdo_point.z,v_z_round_factor);
End If;
END IF;
IF ( p_geometry.sdo_ordinates is not null ) THEN
v_measure_ord := MOD(trunc(p_geometry.sdo_gtype/100),10);
v_ordinates   := new mdsys.sdo_ordinate_array(1);
v_ordinates.DELETE;
v_ordinates.EXTEND(p_geometry.sdo_ordinates.count);
-- Process all coordinates
<<while_vertex_to_process>>
FOR v_i IN 1..(v_ordinates.COUNT/v_dim) LOOP
v_ord := (v_i-1)*v_dim + 1;
v_ordinates(v_ord) := round(p_geometry.sdo_ordinates(v_ord),v_x_round_factor);
v_ord := v_ord + 1;
v_ordinates(v_ord) := round(p_geometry.sdo_ordinates(v_ord),v_y_round_factor);
if ( v_dim >= 3 ) Then
v_ord := v_ord + 1;
V_Ordinates(v_ord) := Case When V_Ismeasured
then round(p_geometry.sdo_ordinates(v_ord),v_w_round_factor)
else round(p_geometry.sdo_ordinates(v_ord),v_z_round_factor)
End;
if ( v_dim > 3 ) Then
v_ord := v_ord + 1;
v_ordinates(v_ord) := round(p_geometry.sdo_ordinates(v_ord),v_w_round_factor);
End If;
End If;
END LOOP while_vertex_to_process;
END IF;
RETURN mdsys.sdo_geometry(v_geometry.sdo_gtype,
v_geometry.sdo_srid,
v_geometry.sdo_point,
v_geometry.sdo_elem_info,
V_Ordinates);
END RoundOrdinates;
/
show errors``````
``````select RoundOrdinates(MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-816008.499528741,-1071166.1245046,-815846.43719259,-1071166.1245046)),2) as rGeom
from dual;
-- Results
--
RGEOM
---------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-816008.5,-1071166.12,-815846.44,-1071166.12))``````