Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Rounding Coordinates or Ordinates in SDO_GEOMETRY

Wednesday April 18 2012 at 12:23

Keywordsround ordinates coordinates tolerance sdo_geometry
Summary

This article presents a function that can be used to round the individual ordinates of the coordinates of an sdo_geometry.

Often Oracle Locator/Spatial users want to know how to round the individual ordinates of the coordinates of an sdo_geometry. This article shows how this can be done.

Background

Oracle imposes no precision limit on the NUMBER that records an ordinate of a coordinate except that imposed by the NUMBER datatype itself.

Also, while one associates metadata with an SDO_GEOMETRY column in a table that describes a tolerance for each ordinate (see the SDO_TOLERANCE field in an SDO_DIM_ELEMENT of an SDO_DIM_ARRAY stored in xxxx_sdo_geom_metadata), it does not enforce it as INSERTs or UPDATEs are applied against the sdo_geometry column. The SDO_TOLERANCE value just mentioned, as also the tolerance or diminfo parameters of many of Oracle’s sdo_geometry functions (eg SDO_GEOM.SDO_AREA(geometry,tolerance) are only used to ensure computations are done to a specified precision. At no stage are ordinate values rounded to a specific precision.

One must also note that sdo_tolerance is not a statement of the precision of a specific ordinate: it is a statement of how close two coordinates can be to be considered to be the same!

Finally, when loading data from an external source such as a shapefile, there is often a mismatch between the way the ordinates are described depending on whether the external data source stores its values in double precision, float, integer etc.

What is a suitable precision of an Ordinate?

This depends on the “sensor” that recorded the original value:

  • Manually surveyed (with theodolite + surveyor) may be both accurate and precise recording observations down to millimeters;
  • High precision differential GPS may record ordinate values down to 1cm;
  • Cheap hand-held GPS may record a specific coordinate to a few meters precision);
  • Data scanned from an old paper/mylar map may be only accurate to +/-10-20meters!
  • Satellite data is variable in precision.

Case 1

But let’s assume we have some road centreline data that is claimed to be accurate to 0.1 of a meter or 1 digit of decimal precision. And our data was loaded from an external source and has lots of imprecise decimal digits of precision.

  1. SELECT SDO_GEOMETRY(2002,NULL,NULL,
  2.                     SDO_ELEM_INFO_ARRAY(1,2,1),
  3.                     SDO_ORDINATE_ARRAY(-816008.499528741,-1071166.1245046,-815846.43719259,-1071166.1245046)) AS oGeom
  4.   FROM dual;
  5. -- Results
  6. --
  7. OGEOM
  8. ------------------------------------------------------------------------------------------------------------------------------------------------------------------
  9. MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(-816008.499528741,-1071166.1245046,-815846.43719259,-1071166.1245046))

Note that, given its stated observational accuracy, it has way too many digits of supposed precision when one (1 decimeter) would be sufficient.

Case 2

I mentioned above that some Oracle functions can take a tolerance value while some do not. Let’s assume we have a long/lat value (via a click on a map) and we want the Google Mercator Map coordinates for that point.

For this we can use the SDO_CS.TRANSFORM function:

  1. SELECT sdo_cs.transform(sdo_geometry(2002,8311,NULL,
  2.                                      sdo_elem_info_array(1,2,1),
  3.                                      sdo_ordinate_array(147.123,-32.456,147.672,-33.739)),3785) AS geom
  4.   FROM dual;
  5. -- Results
  6. --
  7. GEOM
  8. -------------------------------------------------------------------------------------------------------------------
  9. MDSYS.SDO_GEOMETRY(2002,3785,NULL,
  10.                    MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),
  11.                    MDSYS.SDO_ORDINATE_ARRAY(16377657.4439788,-3800381.82007675,16438771.8444243,-3970070.49100647))

Being that the input data was only specified to 0.001 of a degree, an output – in meters – specified to 8 decimal places seems somewhat excessive.

Applying a Precision to the Ordinates of a Coordinate

To apply a specific precision to the numbers in an SDO_ORDINATE array requires a relatively simply PL/SQL function. I created just such a function a long time ago which is available in my GEOM PL/SQL package available for free on this website. But here is a standalone version of it:

  1. CREATE OR REPLACE
  2. FUNCTION RoundOrdinates(P_Geometry        IN Mdsys.Sdo_Geometry,
  3.                         P_X_Round_Factor  IN NUMBER,
  4.                         p_y_round_factor  IN NUMBER DEFAULT NULL,
  5.                         P_Z_Round_Factor  IN NUMBER DEFAULT NULL,
  6.                         p_m_round_factor  IN NUMBER DEFAULT NULL)
  7.   RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC
  8.   IS
  9.    C_I_Null_Geometry  Constant INTEGER       := -20120;
  10.    C_S_Null_Geometry  Constant Varchar2(100) := 'Input geometry must not be null';
  11.    C_I_Null_Tolerance Constant INTEGER       := -20119;
  12.    C_S_Null_Tolerance Constant Varchar2(100) := 'Input tolerance/dimarray must not be null';
  13.    v_ismeasured       BOOLEAN;
  14.    v_dim              pls_integer;
  15.    v_gtype            pls_integer;
  16.    v_measure_ord      pls_integer;
  17.    v_ord              pls_integer;
  18.    v_geometry         mdsys.sdo_geometry := p_geometry;
  19.    V_Ordinates        mdsys.Sdo_Ordinate_Array;
  20.    V_X_Round_Factor   NUMBER := P_X_Round_Factor;
  21.    V_Y_Round_Factor   NUMBER := Nvl(P_Y_Round_Factor,P_X_Round_Factor);
  22.    V_Z_Round_Factor   NUMBER := Nvl(P_z_Round_Factor,P_X_Round_Factor);
  23.    V_W_Round_Factor   NUMBER := NVL(p_m_round_factor,p_x_round_factor);
  24. BEGIN
  25.   IF ( p_x_round_factor IS NULL ) THEN
  26.     raise_application_error(c_i_null_tolerance,c_s_null_tolerance,TRUE);
  27.    END IF;
  28.   IF ( p_geometry IS NULL ) THEN
  29.      raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  30.   END IF;
  31.   V_Ismeasured := CASE WHEN MOD(Trunc(p_geometry.Sdo_Gtype/100),10) = 0 THEN FALSE ELSE TRUE END;
  32.   v_gtype := MOD(p_geometry.sdo_gtype,10);
  33.   v_dim   := p_geometry.get_dims(); -- IF 9i then .... TRUNC(p_geometry.sdo_gtype/1000,0);
  34.   -- If point update differently to other shapes...
  35.   --
  36.   IF ( V_Geometry.Sdo_Point IS NOT NULL ) THEN
  37.     v_geometry.sdo_point.X := round(v_geometry.sdo_point.x,v_x_round_factor);
  38.     V_Geometry.Sdo_Point.Y := Round(V_Geometry.Sdo_Point.Y,V_Y_Round_Factor);
  39.     IF ( v_dim > 2 ) THEN
  40.       v_geometry.sdo_point.z := round(v_geometry.sdo_point.z,v_z_round_factor);
  41.     END IF;
  42.   END IF;
  43.   IF ( p_geometry.sdo_ordinates IS NOT NULL ) THEN
  44.     v_measure_ord := MOD(trunc(p_geometry.sdo_gtype/100),10);
  45.     v_ordinates := NEW mdsys.sdo_ordinate_array(1);
  46.     v_ordinates.DELETE;
  47.     v_ordinates.EXTEND(p_geometry.sdo_ordinates.COUNT);
  48.     -- Process all coordinates
  49.     <<while_vertex_to_process>>
  50.     FOR v_i IN 1..(v_ordinates.COUNT/v_dim) LOOP
  51.        v_ord := (v_i-1)*v_dim + 1;
  52.        v_ordinates(v_ord) := round(p_geometry.sdo_ordinates(v_ord),v_x_round_factor);
  53.        v_ord := v_ord + 1;
  54.        v_ordinates(v_ord) := round(p_geometry.sdo_ordinates(v_ord),v_y_round_factor);
  55.        IF ( v_dim >= 3 ) THEN
  56.           v_ord := v_ord + 1;
  57.           V_Ordinates(v_ord) := CASE WHEN V_Ismeasured
  58.                                      THEN round(p_geometry.sdo_ordinates(v_ord),v_w_round_factor)
  59.                                      ELSE round(p_geometry.sdo_ordinates(v_ord),v_z_round_factor)
  60.                                   END;
  61.           IF ( v_dim > 3 ) THEN
  62.              v_ord := v_ord + 1;
  63.              v_ordinates(v_ord) := round(p_geometry.sdo_ordinates(v_ord),v_w_round_factor);
  64.           END IF;
  65.        END IF;
  66.     END LOOP while_vertex_to_process;
  67.   END IF;
  68.   RETURN mdsys.sdo_geometry(v_geometry.sdo_gtype,
  69.                             v_geometry.sdo_srid,
  70.                             v_geometry.sdo_point,
  71.                             v_geometry.sdo_elem_info,
  72.                             V_Ordinates);
  73. END RoundOrdinates;

This function can be used in any internal Oracle processing. Let’s apply it first to our imaginary centreline:

  1. SELECT RoundOrdinates(
  2.             SDO_GEOMETRY(2002,NULL,NULL,
  3.                          SDO_ELEM_INFO_ARRAY(1,2,1),
  4.                          SDO_ORDINATE_ARRAY(-816008.499528741,-1071166.1245046,-815846.43719259,-1071166.1245046)),
  5.             1) AS rGeom
  6.   FROM dual;
  7. -- Results
  8. --
  9. RGEOM
  10. ---------------------------------------------------------------------------------------------------------------------------------------
  11. MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(-816008.5,-1071166.1,-815846.4,-1071166.1))

Similarly in conjunction with the Google Maps transform above:

  1. SELECT RoundOrdinates(
  2.             sdo_cs.transform(sdo_geometry(2002,8311,NULL,
  3.                                           sdo_elem_info_array(1,2,1),
  4.                                           sdo_ordinate_array(147.123,-32.456,147.672,-33.739)),3785),
  5.             1) AS geom
  6.   FROM dual;
  7. -- Results
  8. --
  9. GEOM
  10. --------------------------------------------------------------------------------------------------------
  11. MDSYS.SDO_GEOMETRY(2002,3785,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),
  12.                                   MDSYS.SDO_ORDINATE_ARRAY(16377657.4,-3800381.8,16438771.8,-3970070.5))

UPDATE

I have had a suggested change from a reader which is very clever. The clever idea used is:

We use to change the precision to “mm” (fix), so we hope to speed up the function by BULK COLLECT into a decreased precision array, instead of rounding rwo by row…

Here is the approach as an anonymous block (I changed some of the ordinate values to display the rounding):

  1. SET SERVEROUTPUT ON SIZE 900000;
  2. DECLARE
  3.    TYPE  SDO_ORDINATE_ARRAY3 IS VARRAY(1048576) OF NUMBER(11,3);
  4.    sdoa3 SDO_ORDINATE_ARRAY3;
  5.    tg    SDO_GEOMETRY := SDO_GEOMETRY(2002,NULL,NULL,
  6.                                       SDO_ELEM_INFO_ARRAY(1,2,1),
  7.                                       SDO_ORDINATE_ARRAY(3310000.12345,5620000.12345, 3310010.3456789,5620000.23456789));
  8. BEGIN
  9.   SELECT x.column_value BULK COLLECT
  10.     INTO sdoa3
  11.     FROM TABLE(tg.sdo_ordinates) x;
  12.   FOR i IN sdoa3.FIRST .. sdoa3.LAST
  13.   LOOP
  14.     dbms_output.put_line('SDO3('||i||')='||sdoa3(i));
  15.   END LOOP;
  16. END;
  17. /
  18. -- Results
  19. anonymous block completed
  20. SDO3(1)=3310000.123
  21. SDO3(2)=5620000.123
  22. SDO3(3)=3310010.346
  23. SDO3(4)=5620000.235

A few comments on this approach.

Speed: I expect that it will perform faster than my longer, pure PL/SQL approach but I have not yet tested it. (Perhaps another day.)

Parameterisation: When put into a function with the rounding value being a parameter, one cannot declare the reduced precision array using a parameter as can be seen in the following code:

  1. CREATE FUNCTION roundOrdinates(p_geom sdo_geometry, p_round_value pls_integer)
  2. RETURN sdo_geometry deterministic
  3. AS
  4.    v_round_value pls_integer := 3;
  5.    TYPE  SDO_ORDINATE_ARRAY3 IS VARRAY(1048576) OF NUMBER(11,v_round_value);
  6.   ...
  7. Error report:
  8. ORA-06550: line 6, COLUMN 62:
  9. PLS-00491: NUMERIC literal required
  10. 06550. 00000 -  "line %s, column %s:\n%s"
  11. *Cause:    Usually a PL/SQL compilation error.

Flexible Rounding: Following on from the previous point, the suggested approach does not allow the application of different rounding values for X, Y or Z. One cannot use the BULK COLLECT … INTO reduced precision array trick. Though it has the benefit of the ability to write straight back to an sdo_geometry compliant sdo_ordinate_array as follows.

  1. SET SERVEROUTPUT ON SIZE 900000;
  2. DECLARE
  3.    v_dims    pls_integer;
  4.    v_x_round pls_integer := 3;
  5.    v_y_round pls_integer := 2;
  6.    v_z_round pls_integer := 1;
  7.    sdoa  SDO_ORDINATE_ARRAY;
  8.    tg    SDO_GEOMETRY := SDO_GEOMETRY(2002,NULL,NULL,
  9.                                       SDO_ELEM_INFO_ARRAY(1,2,1),
  10.                                       SDO_ORDINATE_ARRAY(3310000.12345,5620000.12345, 3310010.3456789,5620000.23456789));
  11. BEGIN
  12.   v_dims := tg.get_dims();
  13.   SELECT CASE MOD(rownum,v_dims)
  14.               WHEN 0 THEN ROUND(x.column_value,v_x_round)
  15.               WHEN 1 THEN ROUND(x.column_value,v_y_round)
  16.               WHEN 2 THEN ROUND(x.column_value,v_z_round)
  17.           END
  18.     BULK COLLECT
  19.     INTO sdoa
  20.     FROM TABLE(tg.sdo_ordinates) x;
  21.   FOR i IN sdoa.FIRST .. sdoa.LAST
  22.   LOOP
  23.     dbms_output.put_line('SDO('||i||')='||sdoa(i));
  24.   END LOOP;
  25. END;
  26. /
  27. -- Results
  28. anonymous block completed
  29. SDO(1)=3310000.12
  30. SDO(2)=5620000.123
  31. SDO(3)=3310010.35
  32. SDO(4)=5620000.235

I hope that either my function and/or the user suggested trick will be useful to someone.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment