Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
Rounding Coordinates or Ordinates in 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.
SELECT SDO_GEOMETRY(2002,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-816008.499528741,-1071166.1245046,-815846.43719259,-1071166.1245046)) AS oGeom FROM dual; -- Results -- OGEOM ------------------------------------------------------------------------------------------------------------------------------------------------------------------ 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:
SELECT sdo_cs.transform(sdo_geometry(2002,8311,NULL, sdo_elem_info_array(1,2,1), sdo_ordinate_array(147.123,-32.456,147.672,-33.739)),3785) AS geom FROM dual; -- Results -- GEOM ------------------------------------------------------------------------------------------------------------------- MDSYS.SDO_GEOMETRY(2002,3785,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), 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:
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 DETERMINISTIC IS 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_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;
This function can be used in any internal Oracle processing. Let’s apply it first to our imaginary centreline:
SELECT RoundOrdinates( SDO_GEOMETRY(2002,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-816008.499528741,-1071166.1245046,-815846.43719259,-1071166.1245046)), 1) AS rGeom FROM dual; -- Results -- RGEOM --------------------------------------------------------------------------------------------------------------------------------------- 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:
SELECT RoundOrdinates( sdo_cs.transform(sdo_geometry(2002,8311,NULL, sdo_elem_info_array(1,2,1), sdo_ordinate_array(147.123,-32.456,147.672,-33.739)),3785), 1) AS geom FROM dual; -- Results -- GEOM -------------------------------------------------------------------------------------------------------- MDSYS.SDO_GEOMETRY(2002,3785,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), 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):
SET SERVEROUTPUT ON SIZE 900000;
DECLARE
TYPE SDO_ORDINATE_ARRAY3 IS VARRAY(1048576) OF NUMBER(11,3);
sdoa3 SDO_ORDINATE_ARRAY3;
tg SDO_GEOMETRY := SDO_GEOMETRY(2002,NULL,NULL,
SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(3310000.12345,5620000.12345, 3310010.3456789,5620000.23456789));
BEGIN
SELECT x.column_value BULK COLLECT
INTO sdoa3
FROM TABLE(tg.sdo_ordinates) x;
FOR i IN sdoa3.FIRST .. sdoa3.LAST
LOOP
dbms_output.put_line('SDO3('||i||')='||sdoa3(i));
END LOOP;
END;
/
-- Results
anonymous block completed
SDO3(1)=3310000.123
SDO3(2)=5620000.123
SDO3(3)=3310010.346
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:
CREATE FUNCTION roundOrdinates(p_geom sdo_geometry, p_round_value pls_integer) RETURN sdo_geometry deterministic AS v_round_value pls_integer := 3; TYPE SDO_ORDINATE_ARRAY3 IS VARRAY(1048576) OF NUMBER(11,v_round_value); ... Error report: ORA-06550: line 6, COLUMN 62: 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 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.
SET SERVEROUTPUT ON SIZE 900000;
DECLARE
v_dims pls_integer;
v_x_round pls_integer := 3;
v_y_round pls_integer := 2;
v_z_round pls_integer := 1;
sdoa SDO_ORDINATE_ARRAY;
tg SDO_GEOMETRY := SDO_GEOMETRY(2002,NULL,NULL,
SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(3310000.12345,5620000.12345, 3310010.3456789,5620000.23456789));
BEGIN
v_dims := tg.get_dims();
SELECT CASE MOD(rownum,v_dims)
WHEN 0 THEN ROUND(x.column_value,v_x_round)
WHEN 1 THEN ROUND(x.column_value,v_y_round)
WHEN 2 THEN ROUND(x.column_value,v_z_round)
END
BULK COLLECT
INTO sdoa
FROM TABLE(tg.sdo_ordinates) x;
FOR i IN sdoa.FIRST .. sdoa.LAST
LOOP
dbms_output.put_line('SDO('||i||')='||sdoa(i));
END LOOP;
END;
/
-- Results
anonymous block completed
SDO(1)=3310000.12
SDO(2)=5620000.123
SDO(3)=3310010.35
SDO(4)=5620000.235
I hope that either my function and/or the user suggested trick will be useful to someone.