|
Implementing an Affine/ST_Affine function for Oracle Spatial
Tuesday February 03 2009 at 20:28
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):
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.
        
|
SpatialDB Advisor
Home
Comment [2]
Simon,
Is Geom.Tolerance equivalent to PostGIS ST_SnapToGrid? I’m assuming it is.
This looks pretty good.
I don’t know enough yet to make any constructive criticism about your actual implementation code.
Keep up the good work!
— Regina · 4 February 2009, 04:35 · #
Regina,
Simply put, the short answer is, Yes. My Tolerance function is the equivalent of:
geometry ST_SnapToGrid(geometry geomA, float size);
To make it clear for PostGIS users that might dip their toe into Oracle via XE and come across my packages, I have added two additional wrappers to my Tolerance functions in my GEOM PL/SQL package. One that is the equivalent of the above, and the other the equivalent of the following PostGIS function:
geometry ST_SnapToGrid(geometry geomA, float sizeX, float sizeY);
Now the long answer. The notion of tolerance, while simple, is mis-understand by new users of Oracle Spatial. From the documentation :
My tolerance function simply rounds an ordinate value to the supplied (Oracle) tolerance value.
Thus, if an x ordinate has value 100,000.123456789 and we supply 0.005 (ie 1cm) as the tolerance, the ordinate value will be rounded to 100,000.12.
(Note: Oracle tolerances are declared as one would to a "round" function in a C program. So, if your actual tolerance is 0.01 ie 1cm, you supply half this value to Oracle ie 0.005.)
Why do I provide Tolerance? Two reasons:
1. Most neophyte Oracle Spatial users don’t understand that Oracle Spatial will let a GIS package or user load any sort of data (with any precision) and even incorrect data.
In short, Oracle provides no “out of the box” checks on the loaded data.
A GIS loader can create ordinates with a ridiculous number of double precision digits to the right of the decimal point and Oracle won’t check it even though its SDO_GEOM_METADATA – a non-standard metadata registration table (the more standard GEOMETRY_COLUMNS table maintained by PostGIS, called OGIS_GEOMETRY_COLUMNS in Oracle, is not maintained by Oracle Spatial functionality: it must be populated directly my users) – might have a correct stored tolerance for this data. Thus, even if the data is supposedly accurate to 1cm or 1mm, the loader can create geometry objects with values like the above: 100,000.123456789 and Oracle won't check it or care.
Another example. PostGIS creates the enforce_geotype_geom check constraint when a geometry column is added via the AddGeometryColumn function viz:
CONSTRAINT enforce_geotype_geom CHECK ((geometrytype(geom) = ANY (ARRAY[‘MULTIPOLYGON’::text, ‘POLYGON’::text])) OR geom IS NULL)Oracle has no automatic, equivalent though a user can do this:
ALTER TABLE parcel ADD CONSTRAINT enforce_geotype_geom CHECK (geom.sdo_gtype in (2003,2007) OR geom IS NULL);A user can, alternatively, add a layer type restriction to an RTree index on creation that will only allow geometry data of the right type to be inserted.
2. Since Oracle does not enforce a tolerance value, naked examination of an SDO_GEOMETRY object’s ordinates will show all digits in the number even if irrelevant. This confuses neophyte users (and often makes data visualisation messy). Rounding the actual stored ordinate values to the supplied tolerance, aids user comprehension of their own data.
I hope this explanation helps.
— Simon Greener · 4 February 2009, 12:36 · #