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.
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.
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.
Note that, given its stated observational accuracy, it has way too many digits of supposed precision when one (1 decimeter) would be sufficient.
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:
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:
FUNCTION RoundOrdinates(P_Geometry IN Mdsys.Sdo_Geometry,
RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC
C_I_Null_Geometry Constant INTEGER :=-20120;
C_S_Null_Geometry Constant Varchar2(100) :='Input geometry must not be null';
C_I_Null_Tolerance Constant INTEGER :=-20119;
C_S_Null_Tolerance Constant Varchar2(100) :='Input tolerance/dimarray must not be null';
v_geometry mdsys.sdo_geometry := p_geometry;
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);
TYPE SDO_ORDINATE_ARRAY3 IS VARRAY(1048576)OFNUMBER(11,v_round_value);
ORA-06550: line 6,COLUMN62:
PLS-00491: NUMERIC literal required
06550. 00000 -"line %s, column %s:\n%s"
*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 BULKCOLLECT … 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.