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.
* @copyright : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. (http://creativecommons.org/licenses/by-sa/2.5/au/)
**/
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.