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.

The Spatial Database Advisor

Top 5 Recent Articles

OptimoRoute

Functions to Convert Native PostgreSQL geometric types to PostGIS

Converting PostgreSQL Native Geometric Types to PostGIS

Fitting Bezier Polygon to Points

Fixing Geography Ring Orientation

ARTICLES CATEGORIES

Swapping Ordinates in an SDO_GEOMETRY object

Documentation