Implementing an Affine/ST_Affine function for Oracle Spatial

Recently, I published three articles on the implementation of a Move/Translate, Rotate and Scale functions for Oracle Spatial.

Given this I thought to myself “Can I roll all these together into a single Affine function”. Of course, 11gR1’s SDO_UTIL package includes an AFFINETRANSFORMS function but there is not one for 10g.

Now, any implementation relies on the ability to do matrix algebra in Oracle. Luckily, at 10g, Oracle released the UTL_NLA package. This package:

The UTL_NLA package exposes a subset of the BLAS (Basic Linear Algebra Subprograms) and LAPACK (Linear Algebra PACKage )(Version 3.0) operations on vectors and matrices represented as VARRAYs.

After reading the documentation, brushing up on my linear algebra, trolling the PostGIS ST_Affine documentation, and reading articles by known Oracle Gurus such as Marcos Campos, I plunged in.

Here are the function headers I have created within my GEOM package.

 /* ----------------------------------------------------------------------------------------
 * function   : Affine
 * precis     : Applies a 3d affine transformation to the geometry to do things like translate, rotate, scale in one step.
 * version    : 1.0
 * description: Applies a 3d affine transformation to the geometry to do things like translate, rotate, scale in one step.
 *               To apply a 2D affine transformation only supply a, b, d, e, xoff, yoff
 * usage      : Function Affine ( p_geom IN MDSYS.SDO_GEOMETRY,
 * param      : p_geom  : The shape to rotate.
 * paramtype  : p_geom  : MDSYS.SDO_GEOMETRY
 * param      : a, b, c, d, e, f, g, h, i, xoff, yoff, zoff :
 *               Represent the transformation matrix
 *                 / a b c xoff \
 *                 | d e f yoff |
 *                 | g h i zoff |
 *                 \ 0 0 0    1 /
 *               and the vertices are transformed as follows:
 *                 x' = a*x + b*y + c*z + xoff
 *                 y' = d*x + e*y + f*z + yoff
 *                 z' = g*x + h*y + i*z + zoff
 * requires   : CODESYS.GEOM.ADD_Coordinate Procedure
 *               SDO_UTIL.GetVertices Funciton
 *               SYS.UTL_NLA Package
 *               SYS.UTL_NLA_ARRAY_DBL Type
 *               SYS.utl_nla_array_int Type
 * return     : newGeom    : Transformed input geometry.
 * rtnType    : newGeom    : MDSYS.SDO_GEOMETRY
 * note       : Cartesian arithmetic only
 * history    : Simon Greener, SpatialDB Advisor - Feb 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/) . Any bugs or improvements to be supplied back to Simon Greener
 **/
 Function Affine(p_geom in mdsys.sdo_geometry,
                  p_a number,
                  p_b number,
                  p_c number,
                  p_d number,
                  p_e number,
                  p_f number,
                  p_g number,
                  p_h number,
                  p_i number,
                  p_xoff number,
                  p_yoff number,
                  p_zoff number)
   return mdsys.sdo_geometry deterministic;
 --
 -- Overload of main Affine function
 --
 FUNCTION ST_Affine( p_geom in mdsys.ST_geometry,
                        p_a number,
                        p_b number,
                        p_c number,
                        p_d number,
                        p_e number,
                        p_f number,
                        p_g number,
                        p_h number,
                        p_i number,
                        p_xoff number,
                        p_yoff number,
                        p_zoff number)
     Return MDSYS.ST_GEOMETRY DETERMINISTIC;

Let’s test what I have done by executing the tests on the PostGIS ST_Affine documentation page

 SELECT GEOM.AsEWKT(
         GEOM.Tolerance( /* Let's round the resultant ordinates */
         GEOM.ST_Affine(foo.the_geom,
                cos(Constants.pi()), -sin(Constants.pi()), 0,
                sin(Constants.pi()), cos(Constants.pi()), -sin(Constants.pi()),
                0, sin(Constants.pi()), cos(Constants.pi()),
                0, 0, 0).Get_Sdo_Geom(),
         0.05)
         ) as ST_AsEWKT
          FROM (SELECT MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(SDO_GEOMETRY(3002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,2,3,1,4,3))) As the_geom
            FROM DUAL) foo;
  
 -- Oracle result
 ST_ASEWKT
 -------------------------------------------------------------
 LINESTRING XYZ ( LINESTRING(-1.0 -2.0 -3.0, -1.0 -4.0 -3.0) )
  
 1 rows selected
  
 -- PostGIS Result
                   st_asewkt
 -------------------------------
 LINESTRING(-1 -2 -3,-1 -4 -3)
 (1 row)

Graphically, this looks like this (before and after):

!http://www.spatialdbadvisor.com/images/43.png (PostGIS Affine example executed in Oracle)!

Finally, if we want to execute a 2D affine transformation, simply set the 3D parameters to NULL as in the following example:

 SELECT GEOM.AsEWKT(
         GEOM.Tolerance( /* Let's round the resultant ordinates */
         GEOM.ST_Affine(foo.the_geom,
                cos(Constants.pi()), -sin(Constants.pi()), NULL,
                sin(Constants.pi()),  cos(Constants.pi()), NULL,
                NULL,                 NULL,                NULL,
                0,                    0,                   NULL).Get_SDO_Geom(),
         0.05)
         ) as geom
          FROM (SELECT MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(MDSYS.SDO_GEOMETRY(2001,null,sdo_point_type(1,2,NULL),null,null)) As the_geom
            FROM DUAL) foo;
  
 GEOM
 ---------------------------------------------------------------------------------------------------------------------------
 POINT XY (-1.0 -2.0) )
  
 1 rows selected

There are lots of things one can use an affine transformation for. As I find and think up new uses I will add to this blog article.

I hope this work is of use to someone.