# Funky Fix Ordinates By Formula

There was a posting over on the Oracle Spatial forum on Oracle Technet recently where a poster wanted to know how to fix the ordinates of an existing SDO_GEOMETRY object because the values that had been entered were incorrect (multiplied by 100000). This is the orginal post:

bq. I was given a spatial network table, I have a problem though, when they built the original spatial table the lat longs were in whole numbers with precision of 5 ie(4787580, -9409401 which really should have been 47.87580, -94.09401), so my question for you all is… is there a way to loop through the table and perhaps alter each rows SDO_ORDINATE_ARRAY and say divide all the values for each row by 100000?

Siva Ravada (one of the Oracle Spatial development team) answered his question with the following bespoke function.

``` create or replace function fix_ordinates(geometry sdo_geometry)
return SDO_GEOMETRY deterministic
as
idx number;
result sdo_geometry;
begin
result := geometry;
FOR idx in 1 .. result.sdo_ordinates.count LOOP
result.sdo_ordinates(idx) := result.sdo_ordinates(idx)/100000;
END LOOP;
return result;
end;
```

This function got me wondering what a more generic function might look like that could provide a greater range of potential corrections that could be applied to the ordinates in an SDO_GEOMETRY. Immediately this got me thinking about allowing a user to provide a *_formula_* for each of the elements of a coordinate ie X, Y, Z and W (ie M).

Here is my coding of the function (which has been added to my GEOM package).

```   Function fix_ordinates(p_geometry  in sdo_geometry,
p_x_formula in varchar2,
p_y_formula in varchar2,
p_z_formula in varchar2 := null,
p_w_formula in varchar2 := null)
Return SDO_GEOMETRY
Is
v_measure_posn PLS_INTEGER; /* Ordinate position of the Measure value in an LRS geometry */
v_dims         PLS_INTEGER;
v_gtype        PLS_INTEGER;
v_sdo_point    mdsys.sdo_point_type;
v_ordinates    MDSYS.SDO_Ordinate_Array := new MDSYS.SDO_Ordinate_Array();
v_vertex       mdsys.vertex_type;
v_sql          varchar2(4000);
NULL_GEOMETRY  EXCEPTION;
Begin
If ( p_geometry is NULL ) Then
raise NULL_GEOMETRY;
End If;
v_dims  := TRUNC(p_geometry.sdo_gtype/1000,0);
v_gtype := Mod(p_geometry.sdo_gtype,10);
-- If sdo_geometry is a single point coded in sdo_point, then update it
v_sdo_point := p_geometry.sdo_point;
If ( v_gtype = 1 And p_geometry.sdo_point is not null ) Then
v_sql := 'SELECT mdsys.sdo_point_type(' || p_x_formula || ',' ||
p_y_formula || ',' ||
case when p_z_formula is null
then 'NULL'
else p_z_formula
end || ')
FROM (SELECT :X as X,:Y as Y,:Z as Z FROM DUAL )';
EXECUTE IMMEDIATE v_sql
INTO v_sdo_point
USING v_sdo_point.x,
v_sdo_point.y,
v_sdo_point.z;
End If;
If ( p_geometry.sdo_ordinates is not null ) Then
v_measure_posn := MOD(trunc(p_geometry.sdo_gtype/100),10);
/* Need to UNPIVOT x,y,z,w records from "b" query into ordinate list to collect into v_ordinates */
v_sql := '
SELECT CASE A.rin
WHEN 1 THEN b.x
WHEN 2 THEN b.y
WHEN 3 THEN CASE ' || v_measure_posn || '
WHEN 0 THEN b.z
WHEN 3 THEN b.w
END
WHEN 4 THEN b.w
END as ord
FROM (SELECT LEVEL as rin
FROM DUAL
CONNECT BY LEVEL <= ' || v_dims || ') a,
(SELECT rownum as cin, ' ||
case when p_x_formula is null
then 'x'
else p_x_formula
end || ' as x,' ||
case when p_y_formula is null
then 'y'
else p_y_formula
end || ' as y,' ||
case when p_z_formula is null
then 'z'
else p_z_formula
end || ' as z,' ||
case when p_w_formula is null
then 'w'
else p_w_formula
end || ' as w
FROM (SELECT v.x,
v.y, ' ||
CASE WHEN v_measure_posn <> 3 /* If measured geometry and measure position is not 3 then Z is coded in this position */
THEN 'v.z'
ELSE 'NULL'
END || ' as z, ' ||
CASE WHEN v_measure_posn = 3 /* If measured geometry and measure position is 3 then Z has been coded with W so move it */
THEN 'v.z'
ELSE 'v.w'
END || ' as w
FROM TABLE(mdsys.sdo_util.GetVertices(:1)) v
)
) b
ORDER BY B.cin,A.rin';
EXECUTE IMMEDIATE v_sql
BULK COLLECT INTO v_ordinates
USING p_geometry;
End If;
Return sdo_geometry(p_geometry.sdo_gtype,
p_geometry.sdo_srid,
v_sdo_point,
p_geometry.sdo_elem_info,
v_ordinates);
EXCEPTION
WHEN NULL_GEOMETRY Then
raise_application_error(CODESYS.CONSTANTS.c_i_null_geometry,
CODESYS.CONSTANTS.c_s_null_geometry,TRUE);
RETURN p_geometry;
WHEN OTHERS THEN
dbms_output.put_line('Error ('|| SQLCODE ||') of ' || SQLERRM(SQLCODE) );
RETURN p_geometry;
End fix_ordinates;
```

Note that the function expects 4 formulae that are applied to the X,Y,Z and W ordinates depending on the dimensionality of the SDO_Geometry. Note that if a 2D geometry has been coded with a measure in the Z position (eg 3302) then the function will move the measure to the W position and then move it back: measure formula should be provided in this more “naturalistic” interpretation of a geometry’s ordinates.

The formula may reference the ordinates of the geometry via the columns X, Y, Z and W (the Vertex_Type fields produced by SDO_Util.GetVertices function) keywords. These keywords can be referred to multiple times in a formula (see ‘ROUND ( z / ( z * dbms_random.value(1,10) ), 3 )’ in the example that processes a 3D linestring below). Since the formula are applied via SQL even Oracle intrinsic columns like ROWNUM can be used (see ‘(rownum * w)’ below). One can also use any Oracle function, eg RANDOM: this includes functions in packages such as DBMS_RANDOM eg ‘ROUND ( Y * dbms_random.value ( 1,1000) ,3 )’) as well.

Here are some tests on point and linestring geometries.

``` -- Test a single 2D point
select geom.fix_ordinates(mdsys.SDO_Geometry('POINT(1.25 2.44)'),
'ROUND(:X * 3.141592653,3)',
'ROUND(:Y * dbms_random.value(1,1000),3)',
NULL).Get_WKT() as point2d
from dual;
POINT2D
---------------------
POINT (3.927 707.838)

1 rows selected
-- Test single 3D point
select geom.fix_ordinates(mdsys.SDO_Geometry(3001,null,sdo_point_type(1.25,2.44,3.09),null,null),
'ROUND(:X * 3.141592653,3)',
'ROUND(:Y * dbms_random.value(1,1000),3)',
'ROUND(:Z / 1000,3)') as point3d
from dual;

POINT3D
-----------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3001,null,MDSYS.SDO_POINT_TYPE(3.927,814.987,0.003),null))

1 rows selected
-- Test a 2d linestring
select geom.fix_ordinates(mdsys.SDO_Geometry('LINESTRING(1.12345 1.3445,2.43534 2.03998398)'),
'ROUND(:X * 3.141592653,3)',
'ROUND(:Y * dbms_random.value(1,1000),3)').Get_WKT() as LINE2D
from dual;

LINE2D
------------------------------------------
LINESTRING (3.529 805.689, 7.651 1418.316)

1 rows selected

-- Modify the Z ordinate of the following 3D multilinestring object
select geom.fix_ordinates(SDO_Geometry(3006,null,null,sdo_elem_info_array(1,2,1,10,2,1),
sdo_ordinate_array(1.12345,1.3445,9,2.43534,2.03998398,9,3.43513,3.451245,9,10,10,9,10,20,9)),
NULL,
NULL,
'ROUND(z / (z * dbms_random.value(1,10)),3)',
NULL) as fixed_mutlilinestring3D
from dual;

FIXED_MUTLILINESTRING3D
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,10,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,0.26,2.43534,2.03998398,0.323,3.43513,3.451245,0.116,10,10,0.312,10,20,0.132))

1 rows selected
```

Finally,

``` SELECT geom.fix_ordinates(
SDO_Geometry( 3302, -- line string, 3 dimensions: X,Y,M
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
SDO_ORDINATE_ARRAY(
2,2,0, -- Start point - Exit1; 0 is measure from start.
2,4,2, -- Exit2; 2 is measure from start.
8,4,8, -- Exit3; 8 is measure from start.
12,4,12, -- Exit4; 12 is measure from start.
12,10,NULL, -- Not an exit; measure automatically calculated and filled.
8,10,22, -- Exit5; 22 is measure from start.
5,14,27) -- End point (Exit6); 27 is measure from start.
),
NULL,
NULL,
NULL,
'(rownum * w)')
as measured_geom
FROM dual;

MEASURED_GEOM
-----------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3302,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(2,2,0,2,4,4,8,4,24,12,4,48,12,10,null,8,10,132,5,14,189))

1 rows selected
```

I think this is a pretty flexible, nay “funky” function. I hope you find it useful. If you want it improved or find any problems, please let me know.