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
   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))”

! (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;
 1 rows selected

And this looks like:

! (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(
        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 )
 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;
 1 rows selected

Which looks like:

! (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;
oid descptn                                                                          geom
1 degrees = 1 MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(-0.175,9.998,null),null,null)
2 degrees = 2 MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(-0.349,9.994,null),null,null)
359 degrees = 359 MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(0.175,9.998,null),null,null)


! (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.

! (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;
oid descptn         geom
1 Sector = 1 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.349,9.993,-0.523,9.986,-0.697,9.975,0,0,-0.349,9.993))
3 Sector = 3 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.698,9.975,-0.872,9.962,-1.045,9.945,0,0,-0.698,9.975))
359 Sector = 359 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,10,-0.175,9.999,-0.349,9.994,0,0,0,10))


! (Single piece of pie rotated around circle)!

I hope this is of interest.