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.