# 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;
```
 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)

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;
```
 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))

Visually…

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

I hope this is of interest.