Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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.
Documentation
- MySQL Spatial General Functions
- Oracle LRS Objects
- Oracle Spatial Exporter (Java + pl/SQL)
- Oracle Spatial Object Functions
- Oracle Spatial Object Functions (Multi Page)
- PostGIS pl/pgSQL Functions
- SC4O Oracle Java Topology Suite (Java + pl/SQL)
- SQL Server Spatial General TSQL Functions
- SQL Server Spatial LRS TSQL Functions