# Swapping Ordinates in an SDO_GEOMETRY object

I was chatting with a friend and colleague on the telephone today when he mentioned that his project was being delayed because they had been provided some SDO_GEOMETRY data in which the latitude and longitude ordinates had been swapped out of their normal positions. For those who don’t know, latitude/longitude data (known as geodetic data) is stored in Oracle as follows:

X = Longitude
Y = Latitude

But the supplied data was:

X = Latitude
Y = Longitude

Of course, my friend was quite capable of writing a PL/SQL procedure to swap the order but I thought I would create one and add it to my free PL/SQL packages for the use of anyone else who has had this problem.

Here is the function:

```   /** ----------------------------------------------------------------------------------------
* @function   : SwapOrdinates
* @precis      : Allows for swapping ordinate pairs in a geometry.
* @description : Sometimes the ordinates of a geometry can be swapped such as latitude for X
*                 and Longitude for Y when it should be reversed. This function allows for the
*                 swapping of pairs of ordinates controlled by the p_pair parameter.
* @version     : 1.0
* @usage       : Function SwapOrdinates( p_geom in mdsys.sdo_geometry,
*                                          p_pair in varchar default 'XY' )
*                   RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
* @param      : p_geom   : Geometry
* @paramtype  : p_geom   : MDSY.SDO_GEOMETRY
* @param      : p_pair   : The ordinate pair to swap = XY, XZ, XM, YZ, YM or ZM
* @paramtype  : p_pair   : varchar2
* @history    : Simon Greener - Aug 2009 - Original coding.
**/
Function SwapOrdinates( p_geom in mdsys.sdo_geometry,
p_pair in varchar2 default 'XY' /* Can be XY, XZ, XM, YZ, YM, ZM */ )
return mdsys.sdo_geometry
Is
v_geom        mdsys.sdo_geometry := p_geom;
v_pair        VARCHAR2(2)        := UPPER(SUBSTR(p_pair,1,2));
v_dim         NUMBER;
v_gtype       NUMBER;
v_vertices    CODESYS.ST_PointSet;
v_i           PLS_INTEGER;
v_j           PLS_INTEGER;
v_isMeasured  Boolean;
NULL_GEOMETRY EXCEPTION;
INVALID_PAIR  EXCEPTION;
INVALID_SWAP  EXCEPTION;
Begin
IF ( v_pair is NULL
Or
v_pair not in ('XY', 'XZ', 'XM', 'YZ', 'YM', 'ZM' ) ) Then
RAISE INVALID_PAIR;
End If;

IF ( v_geom is NULL ) THEN
RAISE NULL_GEOMETRY;
END IF;

v_isMeasured := isMeasured(p_geom.sdo_gtype);
v_dim := GetDimensions(p_geom.sdo_gtype);
If ( ( v_dim = 2
And
v_pair in ('XZ', 'XM', 'YZ', 'YM', 'ZM' )
)
Or
( v_dim = 3
And
v_isMeasured
And
v_pair in ('XZ', 'YZ' )
)
Or
( v_dim = 3
And
Not v_isMeasured
And
v_pair in ('XM', 'YM', 'ZM' )
)
) Then
RAISE INVALID_SWAP;
End If;

-- Get basic geometry type (point, line, polygon)
v_gtype := GetShortGType(p_geom.sdo_gtype);

If ( v_geom.sdo_point is not null
And
v_pair in ('XY','XZ','YZ') ) Then
v_geom.sdo_point.x := CASE v_pair WHEN 'XY' THEN p_geom.sdo_point.y WHEN 'XZ' THEN p_geom.sdo_point.z ELSE p_geom.sdo_point.x END;
v_geom.sdo_point.y := CASE v_pair WHEN 'XY' THEN p_geom.sdo_point.x WHEN 'YZ' THEN p_geom.sdo_point.z ELSE p_geom.sdo_point.y END;
v_geom.sdo_point.z := CASE v_pair WHEN 'XZ' THEN p_geom.sdo_point.x WHEN 'YZ' THEN p_geom.sdo_point.y ELSE p_geom.sdo_point.z END;
End If;

-- If Single Point, all done, else...
IF ( v_gtype != 1
And
p_geom.sdo_ordinates is not null ) Then
-- It's not a single point ...
-- Process the geometry's ordinate array

-- Copy the ordinates array
If ( v_version < 10 ) Then
SELECT CODESYS.ST_Point(a.x,a.y,a.z,a.m)
Bulk Collect into v_vertices
FROM TABLE(CODESYS.GEOM.GetPointSet(p_geom)) a;
Else
EXECUTE IMMEDIATE '
SELECT CODESYS.ST_Point(
v.x,
v.y,
CASE WHEN :1 <> 3 THEN v.z ELSE NULL END,
CASE WHEN :2 =  3 THEN v.z ELSE v.w END )
FROM TABLE(sdo_util.GetVertices(:3)) v'
BULK COLLECT INTO v_vertices
USING MOD(trunc(p_geom.sdo_gtype/100),10),
MOD(trunc(p_geom.sdo_gtype/100),10),
p_geom;
End If;

v_i := v_vertices.FIRST;           -- index into input coordinate array
v_j := v_geom.sdo_ordinates.FIRST; -- index into output ordinate array
FOR i IN 1 .. v_vertices.COUNT LOOP
v_geom.sdo_ordinates (v_j)     := CASE WHEN ( v_pair = 'XY' )
THEN v_vertices(v_i).y
WHEN ( v_pair = 'XZ' ) And ( ( v_dim = 4 ) Or ( v_dim = 3 And Not v_isMeasured ) )
THEN v_vertices(v_i).z
WHEN ( v_pair = 'XM' ) And ( v_dim = 4 )
THEN v_vertices(v_i).m
WHEN ( v_pair = 'XM' ) And ( v_dim = 3 And v_isMeasured )
THEN v_vertices(v_i).z
ELSE v_vertices(v_i).x
END;
v_geom.sdo_ordinates (v_j + 1) := CASE WHEN ( v_pair = 'XY' )
THEN v_vertices(v_i).x
WHEN ( v_pair = 'YZ' ) And ( ( v_dim = 4 ) Or ( v_dim = 3 And Not v_isMeasured ) )
THEN v_vertices(v_i).z
WHEN ( v_pair = 'YM' ) And ( v_dim = 4 )
THEN v_vertices(v_i).m
WHEN ( v_pair = 'YM' ) And ( v_dim = 3 And v_isMeasured )
THEN v_vertices(v_i).z
ELSE v_vertices(v_i).y
END;
-- Do Z only if exists
If ( ( v_dim = 4 ) OR ( ( v_dim = 3 ) And Not v_isMeasured ) And v_pair in ('XZ', 'YZ', 'ZM' ) ) Then
v_geom.sdo_ordinates (v_j + 2) := CASE WHEN ( v_pair = 'XZ' )
THEN v_vertices(v_i).x
WHEN ( v_pair = 'YZ' ) And ( ( v_dim = 4 ) Or ( v_dim = 3 And Not v_isMeasured ) )
THEN v_vertices(v_i).y
WHEN ( v_pair = 'ZM' ) And ( v_dim = 4 )
THEN v_vertices(v_i).m
ELSE v_vertices(v_i).z
END;
End If;
-- Do M only if exists
If ( ( v_dim = 4 ) OR ( ( v_dim = 3 ) And v_isMeasured ) And v_pair in ('XM', 'YM', 'ZM' ) ) Then
v_geom.sdo_ordinates (v_j + ( v_dim - 1)) := CASE WHEN ( v_pair = 'XM' )
THEN v_vertices(v_i).x
WHEN ( v_pair = 'YM' ) And ( ( v_dim = 4 ) Or ( v_dim = 3 And v_isMeasured ) )
THEN v_vertices(v_i).y
WHEN ( v_pair = 'ZM' ) And ( v_dim = 4 )
THEN v_vertices(v_i).z
ELSE v_vertices(v_i).m
END;
End If;
v_i := v_i + 1;
v_j := v_j + v_dim;
END LOOP;

End If;

RETURN v_geom;
EXCEPTION
WHEN NULL_GEOMETRY THEN
raise_application_error(-20001,'p_geom may not be NULL.');
WHEN INVALID_PAIR THEN
raise_application_error(-20001,'p_pair (' || v_pair || ') must be one of  XY, XZ, XM, YZ, YM, ZM only.');
WHEN INVALID_SWAP THEN
raise_application_error(-20001,'Requested swap (' || v_dim || ',' || v_pair ||')cannot occur as sdo_geometry dimensionality does not support it.' );
End SwapOrdinates;
```

This function uses some functions in my PL/SQL packages which can be removed for a standalone function fairly simply. If anyone wants such a function let me know (remember to tell me which version of Oracle you are running).

Now some tests:

``` select codesys.Geom.SwapOrdinates(mdsys.sdo_geometry(3001,null,mdsys.sdo_point_type(1,20,30),null,null),'XY') as Geom
from dual;

GEOM
---------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3001,null,MDSYS.SDO_POINT_TYPE(20,1,30),null,null)

1 rows selected

select codesys.Geom.SwapOrdinates(mdsys.sdo_geometry(3001,null,mdsys.sdo_point_type(1,20,30),null,null),'XZ') as Geom
from dual;

GEOM
---------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3001,null,MDSYS.SDO_POINT_TYPE(30,20,1),null,null)

1 rows selected

select codesys.Geom.SwapOrdinates(mdsys.sdo_geometry(3001,null,mdsys.sdo_point_type(1,20,30),null,null),'YZ') as Geom
from dual;

GEOM
---------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3001,null,MDSYS.SDO_POINT_TYPE(1,30,20),null,null)

1 rows selected

select codesys.Geom.SwapOrdinates(mdsys.sdo_geometry('LINESTRING (-32 147, -33 180)'),'XY') as Geom
from dual;

GEOM
-------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(147,-32,180,-33))

1 rows selected

select codesys.Geom.SwapOrdinates(mdsys.sdo_geometry('LINESTRING (0 50, 10 50, 10 55, 10 60, 20 50)'),'XY') as Geom
from dual;

GEOM
--------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(50,0,50,10,55,10,60,10,50,20))

1 rows selected

select codesys.Geom.SwapOrdinates(mdsys.sdo_geometry(3002,null,null,
mdsys.sdo_elem_info_array(1,2,1),
mdsys.sdo_ordinate_array(0,50,105,
10,50,110,
10,55,115,
10,60,120,
20,50,125)),'XZ') as Geom
from dual;

GEOM
----------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(105,50,0,110,50,10,115,55,10,120,60,10,125,50,20))

1 rows selected

select codesys.Geom.SwapOrdinates(mdsys.sdo_geometry(3002,null,
mdsys.sdo_point_type(1,20,30),
mdsys.sdo_elem_info_array(1,2,1),
mdsys.sdo_ordinate_array(0,50,105,
10,50,110,
10,55,115,
10,60,120,
20,50,125)),'YZ') as Geom
from dual;

GEOM
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3002,null,MDSYS.SDO_POINT_TYPE(1,30,20),MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(0,105,50,10,110,50,10,115,55,10,120,60,20,125,50))

1 rows selected

select codesys.Geom.SwapOrdinates(MDSYS.SDO_GEOMETRY(3302, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),
MDSYS.SDO_ORDINATE_ARRAY(5,10,0,
20,5,NULL,
35,10,NULL,
55,10,100)),'XM') as Geom
from dual;

GEOM
---------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3302,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(null,10,5,null,5,20,null,10,35,null,10,55))

1 rows selected

select codesys.Geom.SwapOrdinates(MDSYS.SDO_GEOMETRY(4402, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),
MDSYS.SDO_ORDINATE_ARRAY(5,10,500,0,
20,5,501,NULL,
35,10,502,NULL,
55,10,503,100)),'ZM') as Geom
from dual;

GEOM
---------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(4402,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(5,10,0,500,20,5,null,501,35,10,null,502,55,10,100,503))

1 rows selected
```

I hope this function is useful to someone out there.