2 Replies Latest reply: Apr 17, 2012 9:46 PM by Simon Greener RSS

    rouinding coordinates in SDO_GEOMETRY

    917745
      Dear All!
      I would like to ask you for your help...

      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
          Luc Van Linden
          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
            Simon Greener
            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 
              * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
              *               http://creativecommons.org/licenses/by-sa/2.5/au/
              **/
            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
            Testing your fragment...
            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))
            If this solves your problem please award points.

            PS There are a number of articles on my website about the benefits or ordinate precision reduction. Start with http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/208/saving-space-when-using-sdo_geometry-data

            regards
            Simon