Implementing a Rotate/ST_Rotate function for Oracle Spatial

One very useful function that is missing in Oracle Spatial/Locator is the ability to rotate an sdo_geometry object.

A few years ago I wrote a function in my free PL/SQL packages but recent work with PostGIS occasioned my going back to that function and updating it.

The function, and its overloads, are:

 create or replace package Geom
 AUTHID CURRENT_USER
 Is
 ...
 
   Function Rotate (
      p_geometry in MDSYS.SDO_Geometry,
      p_dimarray in MDSYS.SDO_Dim_Array,
      p_X        in number,
      p_Y        in number,
      p_rotation in number := 0)
      return MDSYS.SDO_Geometry deterministic;
 
   /**
   * Overloads of main Rotate function
   **/
   Function Rotate( p_geometry  IN MDSYS.SDO_GEOMETRY,
                     p_tolerance IN number,
                     p_rotation  IN number := 0) -- 0 to 360 degrees
     Return MDSYS.SDO_GEOMETRY Deterministic;
 
   Function Rotate( p_geometry  IN MDSYS.SDO_GEOMETRY,
                     p_tolerance IN number,
                     p_X         IN number,
                     p_Y         IN number,
                     p_rotation  IN number := 0) -- 0 to 360 degrees
     Return MDSYS.SDO_GEOMETRY Deterministic;
 
   Function Rotate( p_geometry IN MDSYS.SDO_GEOMETRY,
                     p_dimarray IN MDSYS.SDO_DIM_ARRAY,
                     p_rotatePt IN MDSYS.SDO_Point_Type,
                     p_rotation IN number := 0) -- 0 to 360 degrees
     Return MDSYS.SDO_Geometry Deterministic;

Let’s explore rotation.

Firstly, let’s define a rectangular polygon whose WKT is:

“POLYGON ((2 2, 2 7, 12 7, 12 2, 2 2))”

!http://www.spatialdbadvisor.com/images/28.png (Original Rectangle Polygon before Rotation)!

Then, let’s rotate it about itself, 45 degrees. In the following call, the false orig rotation parameters (p_x and p_y) are set to NULL. The Rotate function then gets the Minumum Bounding Rectangle (MBR) of the rectangle, extracts its centre, and then rotates about that point.

 select Geom.Rotate(mdsys.sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL),0.05,45)
   from dual;
 
 GEOM45
 -------------------------------------------------------------------------------------------------------------------------------------------
 MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(5.2,-0.8,1.7,2.7,8.8,9.8,12.3,6.3,5.2,-0.8))
 
 1 rows selected

And this looks like:

!http://www.spatialdbadvisor.com/images/29.png (Original Rectangle after Rotation 45 degrees)!

(Now, it appears that the Rotate function in PostGIS only rotates around the origin of the co-ordinate system. Thus to achieve the above one must translate the geometry to the origin, rotate it, then translate it back. Here is a blog on how to do this in PostGIS. Even though my Rotate function has all this built in, this is how one would do what my PostGIS friends did:

 SELECT geom.st_translate(
        geom.rotate(
        geom.st_translate(the_geom,-x,-y),
        0.005,null,null,45),
        x,y) as GEOM45
   FROM ( SELECT the_geom,
                 geom.sdo_centroid(the_geom,0.005).sdo_point.x as x,
                 geom.sdo_centroid(the_geom,0.005).sdo_point.y as y
            FROM (SELECT mdsys.sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',null) as the_geom
                    FROM dual )
         );
 
 GEOM45
 --------------------------------------------------------------------------------------------------------------------------------------
 MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(5.23,-0.8,1.7,2.73,8.77,9.8,12.3,6.27,5.23,-0.8))
 
 1 rows selected

I think having it all inside the Rotate function is neater but less “orthogonal”.)

p. Now, let’s rotate about the 0,0 axis.

 select Geom.Rotate(b.the_geom,0.05,0,0,45 ) as geom00
   from (select mdsys.sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL) as the_geom
          from dual
        ) b;
 
 GEOM00
 --------------------------------------------------------------------------------------------------------------------------------------
 MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(0,2.8,-3.5,6.4,3.5,13.4,7.1,9.9,0,2.8))
 
 1 rows selected

Which looks like:

!http://www.spatialdbadvisor.com/images/30.png (Original rectangle rotated 45 degrees around origin 0,0)!

We can do some cool things.

First, let’s take a single point (0,10) and rotate it around 359 degrees.

 select a.column_value as oid,
        CONCAT('degrees = ',to_char(a.column_value,'999')) as descptn,
        geom.rotate(mdsys.sdo_geometry(2001,null,sdo_point_type(0,10,Null),null,null),0.0005,0,0,a.column_value) as geom
   from table(geom.generate_series(1,359,1)) a;

Visually…

!http://www.spatialdbadvisor.com/images/31.png (Point 0,10 rotated 359 times)!

Finally, let’s take a single slice of a 2 degree pie and rotate it fully around the circle.

!http://www.spatialdbadvisor.com/images/32.png (Single 2 degree sector of a pie)!

 select a.column_value as oid,
        CONCAT('Sector = ',to_char(a.column_value,'999')) as descptn,
        geom.rotate(MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(-0.175,9.998,-0.349,9.994,-0.523,9.986,0,0,-0.175,9.998)),0.0005,0,0,a.column_value) as geom
   from table(geom.generate_series(1,360,2)) a;

Visually…

!http://www.spatialdbadvisor.com/images/33.png (Single piece of pie rotated around circle)!

I hope this is of interest.