2 Replies Latest reply on Nov 27, 2009 12:52 PM by 719401

    SDO_ORDINATE_ARRAY precision control

      Dear Everyone,



      I met a problem dealing with storing the geometry with proper precision of SDO_ORDINATES values. When I upload the Geometries into Oracle, I had chosen FME (one software does transforming and uploading data automatically.) BUT, when I export the geometry ordinates for other programs for calculations or analysis, I found the precisions for each coordinates are too many (I do not really need that much, besides, it brought problems for floating number calculations.) For example, the lat/long value would be 10.0000000000000001/ 20.0000000000.

      In other program, I can trim the data without these many digits, but I also need to refer back to the database, and there is already some data lost. I would like to have the data trimmed in the database after I upload the data into oracle. I can not find any function or command so far, neither from the FME nor the oracle documents. Anyone would have known this, please leave me your comments. Thanks in advance.

      Kind Regards
        • 1. Re: SDO_ORDINATE_ARRAY precision control
          John O'Toole
          You can do this in FME using the [CoordinateRounder |http://docs.safe.com/fme/html/Transformers/content/transformers/coordinaterounder.htm] transformer.

          There's no function available in Spatial to do this, but its pretty straightforward to do. Here's a sample function:
          CREATE OR REPLACE Function Round_Coordinates(p_geometry IN MDSYS.SDO_GEOMETRY, p_tolerance IN NUMBER)
               v_dim            number;
               v_gtype          number;
               v_geometry       mdsys.sdo_geometry := p_geometry;
               v_ordinates      mdsys.sdo_ordinate_array;
               v_round_factor number;
              v_gtype := Mod(p_geometry.sdo_gtype,10);
              v_dim :=  SUBSTR(p_geometry.sdo_gtype,1,1);
              v_ordinates := p_geometry.sdo_ordinates;            
              -- Compute rounding factors
              v_round_factor := round(log(10,(1/p_tolerance)/2));
              -- 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_round_factor);
                v_geometry.sdo_point.Y := round(v_geometry.sdo_point.y, v_round_factor);
                If v_dim > 2 Then
                  v_geometry.sdo_point.z := round(v_geometry.sdo_point.z, v_round_factor);
                End If;
              END IF;
              IF ( v_gtype <> 1 ) THEN
                   FOR i in v_ordinates.FIRST..v_ordinates.LAST LOOP
                        v_ordinates(i) := round(v_ordinates(i), v_round_factor);
                    END LOOP;
               END IF;
              RETURN mdsys.sdo_geometry(v_geometry.sdo_gtype,
          END Round_Coordinates;
          • 2. Re: SDO_ORDINATE_ARRAY precision control
            Thanks and cheers