Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles

    GeoRaptor 4.x Update 2 (Project has Stalled)
    Tip: Drop that Spatial Index!
    Convert Single Geometry to Multi-part Geometry in Oracle Spatial
    Optimized Rectangle to 5 Point Polygon
    Centroid Package now supports Y ordinate seeding
    Convert GeoJSON document to Sdo_Geometry objects
    Implementation Of Travelling Salesman Problem
    Create Polygon From Bearings And Distances
    Function That Returns a Compass Point From a Whole Circle Bearing
    Playing around with Centroids by using different seed values
    Simple Oracle C Sprintf or Java String.format
    Some Oriented Point Functions
    Extracting Inner Rings Changed Ordinate Ordering: A Trap For Players Who Don't Read Documentation!
    PLS-00306: wrong number or types of arguments in call to 'SDO_GEOMETRY'
    Converting Google Earth Formatted Longitude/Latitude points to decimal degrees
    Oracle Business Intelligence Warehousing and Analytics - Spatial Summit
    How far inside, is inside? Measuring actual distance.
    Noding and building a polygon from single, overlapping linestrings
    Analyzing Spatial Query Performance Improvements in Oracle Spatial and Graph 12c Through Cross-Vendor Comparison
    ST_VertexN / ST_PointN - Extracting a specific point from any geometry
    Convert Single Point stored in SDO_ORDINATES to SDO_POINT_TYPE
    Aggregate APPEND Islands and XOR polygons
    Circular Arcs in Geodetic Polygons
    Some SDO_GEOMETRY/DIMINFO handling functions
    Applying And Extending Oracle Spatial - Book Released
    Changing all DIMINFO sdo_tolerance values for all metadata records in one go.
    Building Polygons from Incomplete Linestrings using ST_PolygonBuilder
    Computing Cardinal Directions to nearby geometries
    Intersecting two aggregated polygon layers with SC4O
    Spatial and Oracle 12c
    Update Triggers and SDO_GEOMETRY Equality
    Duplicate Geometry data and Data Models
    CENTROID package update
    How to calculate cumulative length of a linestring
    Useful Package of Wrapper Functions for Sdo_Util.AffineTransforms
    Compute Location from known Lat/Long point using delta easting and northing in miles
    SDO_AGGR_SET_UNION
    Sorting SDO_GEOMETRY data using the ORDER BY clause of a SELECT statement
    Creating linestrings from points
    Rounding Coordinates or Ordinates in SDO_GEOMETRY
    Effects of Sdo_Geometry Ordinate Precision on Performance
    Effects of Sdo_Geometry Ordinate Precision on Storage
    The Spatial filtering of geometries: The effect of tolerances on relationships
    Application of Delaunay Triangulation and Inverse Distance Weighting (IDW) in Oracle for Soils Interpolation
    Selecting all SDO_GTYPE values for all tables/sdo_geometry columns in a schema
    CENTROID package - Tips for Use
    Announcing the Spatial Companion For Oracle (SC4O)
    Filtering Rings (Oracle Spatial)
    Splitting a polygon using one or more linestrings
    isValid, isSimple, Dimension and CoordDim methods for SDO_Geometry
    Line Merging or Collecting lines together: ST_LineMerger
    ST_DeleteVertex for Oracle SDO_Geometry based on Jaspa/JTS
    3D/4D and SRID aware Conversion functions for SDO_Geometry: WKT and EWKT
    Topological vs Non-Topological Simplification/Generalization of Aggregated Area Geometies in Oracle
    Filtering very short linestrings via bitmap function index
    CENTROID For Oracle
    Gridding a sdo_geometry line/polygon object (Oracle)
    Finding centre and radius of a circular geometry
    Constraining geometry type for sdo_geometry column in a table.
    CASE Statements and SDO_GEOMETRY
    The Power of Constraints and Indexes for Spatial Constraints: stopping duplicate points
    Replacement for SDO_GEOM.RELATE - ST_Relate based on JTS
    Changing Oracle Spatial Index Parameters on existing index
    Writing Excel Spreadsheets files from within the Oracle database using Java and PL/SQL
    Writing xSV (eg csv) files from within the Oracle database using Java and PL/SQL
    A simple spike finder for Spatial/Locator
    JTS Java class compilation for 11g and above
    Random Spatial Search Procedure
    Geometry Snapping using JTS in Oracle
    Exposing JTS's MinimumBoundingCircle functionality
    Exposing JTS's Densifier functionality
    Using JTS's Comparison Functions - HausdorffSimilarityMeasure & AreaSimilarityMeasure with SDO_GEOMETRY
    Free JTS-based Area/Length Functions
    Handy way of systematically fixing polygon geometries with 13349 and other errors
    Standalone CENTROID package now available for download
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 4 Processing Geodetic data
    Configurable Buffer: JTS and Oracle
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 3
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 2
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 1
    Building Lines into Polygons in Oracle Locator / Spatial
    Finding Intersection Points between Line and Polygon
    SDO2GeoJSON
    Free version of sdo_length
    Alternative to my SQL based GetNumRings function
    External Tables and SDO_Geometry data.
    layer_gtype keyword issue when indexing linear data on 11g
    String Tokenizer for Oracle
    Free Aggregate Method for Concatenating 2D Lines in Oracle Locator 10g
    Reducing 5 Vertex Polygon to Optimized Rectangle
    Square Buffer
    Converting decimal seconds to string
    SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT - 13356 Issues
    Valid conversion unit values for Oracle sdo_geom.sdo_length()
    Removing Steps in Gridded Vector Data - SmoothGrid for Oracle
    Oracle Spatial DISJOINT search/filtering
    Creating SDO_Geometry from geometric data recorded in the columns of a table
    Concave Hull Geometries in Oracle 11gR2
    Projecting SDO_GEOM_METADATA DIMINFO XY ordinates
    Instantiating MDSYS.VERTEX_TYPE
    New PL/SQL Packages - Rotate oriented point
    GeoRaptor Development Team
    Fast Refreshing Materialized View Containing SDO_GEOMETRY and SDO_GEOM.SDO_AREA function
    Performance of PL/SQL Functions using SQL vs Pure Code
    Implementing the BEST VicGrid Projection in Oracle 10gR2
    Making Sdo Geometry Metadata Update Generic Code
    ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()
    Extract Polygons from Compound Polygon
    Detecting sdo_geometries with compound (3-point Arcs) segments
    GEOMETRY_COLUMNS for Oracle Spatial
    Convert GML to SDO_Geometry in Oracle 10gR2
    Spatial Sorting of Data via Morton Key
    Swapping Ordinates in an SDO_GEOMETRY object
    New To_3D Function
    Extend (Reduce/Contract/Skrink) Function for Oracle
    Loading and Processing GPX 1.1 files using Oracle XMLDB
    Loading Spatial Data from an external CSV file in Oracle
    Calling the Oracle Spatial shapefile loader from within the Oracle database itself
    Implementing SDO_VertexUpdate/ST_VertexUpdate for Oracle
    Implementing SDO_RemovePoint/ST_RemovePoint for Oracle
    Implementing SDO_AddPoint/ST_AddPoint for Oracle
    ESRI ArcSDE Exverted and Inverted Polygons and Oracle Spatial
    Funky Fix Ordinates By Formula
    Implementing a SetPoint/ST_SetPoint function in Oracle
    Implementing an ST_SnapToGrid (PostGIS) function for Oracle Spatial
    Generating random point data
    Implementing an Affine/ST_Affine function for Oracle Spatial
    Implementing a Scale/ST_Scale function for Oracle Spatial
    Implementing a Parallel/ST_Parallel function for linestring data for Oracle Spatial
    Implementing a Rotate/ST_Rotate function for Oracle Spatial
    Limiting table list returned when connecting to Oracle Database using ODBC
    ST_Azimuth for Oracle: AKA Cogo.Bearing
    Implementing a Translate/ST_Translate/Move function for Oracle Spatial
    Elem_Info_Array Processing: An alternative to SDO_UTIL.GetNumRings and querying SDO_ELEM_INFO itself
    Minumum Bounding Rectangle (MBR) Object Type for Oracle
    How to extract elements from the result of an sdo_intersection of two polygons.
    How to restart a database after failed parameter change
    Fixing failed spatial indexes after import using data pump
    generate_series: an Oracle implementation in light of SQL Design Patterns
    Multi-Centroid Shootout
    Oracle Spatial Centroid Shootout
    On the use of ROLLUP in Oracle SELECT statements
    Surrounding Parcels
    Spatial Pipelining
    Using Oracle's SDO_NN Operator - Some examples
    Converting distances and units of measure in Oracle Locator
    Split Sdo_Geometry Linestring at a known point
    Forcing an Sdo_Geometry object to contain only points, lines or areas
    Unpacking USER_SDO_GEOM_METADATA's DIMINFO structure using SQL
    Generating multi-points from single point records in Oracle Spatial
    Object Tables of Sdo_Geometry
    Oracle Locator vs Oracle Spatial: A Reflection on Oracle Licensing of the SDO_GEOM Package
    FAST REFRESHing of Oracle Materialized Views containing Sdo_Geometry columns
    Australian MGA/AMG Zone Calculation from geographic (longitude/latitude) data
    Loading Shapefiles (SHP) into Oracle Spatial
    Oracle Spatial Mapping and Map Rendering Performance Tips
    The significance of sdo_lb/sdo_ub in USER_SDO_GEOM_METDATA: Do I need it?
    Oracle Spatial Forum - Melbourne April 2007
    Layer_GTypes for spatial indexes
    Oracle's SQL/MM Compliant Types
    Tips and Tricks

Search

    Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Useful Package of Wrapper Functions for Sdo_Util.AffineTransforms

Monday July 16 2012 at 08:15

KeywordsSDO_UTIL.AFFINETRANSFORMS,wrapper,move,translate,scale,rotate
Summary

This article presents a package of wrapper functions for the all encompassing Sdo_Util.AffineTransforms function.

UPDATED: I’ve now included my ST_Affine function which wraps Oracle’s matrix packages eg SYS.UTL_NLA_ARRAY_DBL. The implementation makes it easier to compare outputs with those from PostGIS’s ST_Affine.

Before 11g, I created a set of affine functions in my free GEOM package that enabled a user to manipulate an sdo_geometry object as follows:

  • Rotate
  • Move (translate)
  • Scale

These functions do not use matrix algebra.

In a later version (10g onwards), I used the SYS.UTL_NLA_ARRAY_DBL package to expose an Affine function which aligns closely with the PostGIS ST_Affine functions:

  1.   FUNCTION ST_Affine(p_geom IN mdsys.sdo_geometry,
  2.                   p_a NUMBER,
  3.                   p_b NUMBER,
  4.                   p_c NUMBER,
  5.                   p_d NUMBER,
  6.                   p_e NUMBER,
  7.                   p_f NUMBER,
  8.                   p_g NUMBER,
  9.                   p_h NUMBER,
  10.                   p_i NUMBER,
  11.                   p_xoff NUMBER,
  12.                   p_yoff NUMBER,
  13.                   p_zoff NUMBER)
  14.     RETURN mdsys.sdo_geometry
  15.   IS
  16.       -- Transformation matrix is represented by:
  17.       -- / a  b  c  xoff \
  18.       -- | d  e  f  yoff |
  19.       -- | g  h  i  zoff |
  20.       -- \ 0  0  0     1 /
  21.       --
  22.       -- For 2D only need to supply: a, b, d, e, xoff, yoff
  23. ...

However, the Oracle Spatial team, sensibly, decided to release their own functionality for affine transformations in the Sdo_Util.AffineTransforms function.

The problem with this function is that it a veritable kitchen sink of capability which, coupled with some pretty limited error handling, is very hard to use.

So, in this blog I propose a PL/SQL package that wraps this function so that it is more useable.

Here it is.

  1. CREATE OR REPLACE
  2. package affine
  3. AUTHID CURRENT_USER
  4. AS
  5.    FUNCTION PI
  6.      RETURN NUMBER deterministic;
  7.    FUNCTION ST_Rotate(p_geometry      IN mdsys.sdo_geometry,
  8.                       p_angle_rad     IN NUMBER)
  9.      RETURN mdsys.sdo_geometry deterministic;
  10.    FUNCTION ST_Rotate(p_geometry     IN mdsys.sdo_geometry,
  11.                       p_angle_rad    IN NUMBER,
  12.                       p_dir          IN pls_integer,
  13.                       p_rotate_point IN mdsys.sdo_geometry,
  14.                       p_line1        IN mdsys.sdo_geometry)                      
  15.      RETURN mdsys.sdo_geometry deterministic;
  16.    FUNCTION ST_Rotate(p_geometry     IN mdsys.sdo_geometry,
  17.                       p_angle_rad    IN NUMBER,
  18.                       p_rotate_x     IN NUMBER,
  19.                       p_rotate_y     IN NUMBER)
  20.      RETURN mdsys.sdo_geometry deterministic;
  21.    FUNCTION ST_Rotate(p_geometry     IN mdsys.sdo_geometry,
  22.                       p_angle_rad    IN NUMBER,
  23.                       p_rotate_point IN mdsys.sdo_geometry)
  24.      RETURN mdsys.sdo_geometry deterministic;
  25.    FUNCTION ST_Scale(p_geometry IN mdsys.sdo_geometry,
  26.                      p_sx       IN NUMBER,
  27.                      p_sy       IN NUMBER,
  28.                      p_sz       IN NUMBER,
  29.                      p_scale_pt IN mdsys.sdo_geometry)
  30.      RETURN mdsys.sdo_geometry deterministic;
  31.    FUNCTION ST_Scale(p_geometry IN mdsys.sdo_geometry,
  32.                      p_sx       IN NUMBER,
  33.                      p_sy       IN NUMBER,
  34.                      p_sz       IN NUMBER)
  35.      RETURN mdsys.sdo_geometry deterministic;
  36.    FUNCTION ST_Scale(p_geometry IN mdsys.sdo_geometry,
  37.                      p_sx       IN NUMBER,
  38.                      p_sy       IN NUMBER)
  39.      RETURN mdsys.sdo_geometry deterministic;
  40.    FUNCTION ST_Translate(p_geometry IN mdsys.sdo_geometry,
  41.                          p_tx       IN NUMBER,
  42.                          p_ty       IN NUMBER,
  43.                          p_tz       IN NUMBER)
  44.      RETURN mdsys.sdo_geometry deterministic;
  45.    FUNCTION ST_Translate(p_geometry IN mdsys.sdo_geometry,
  46.                          p_tx       IN NUMBER,
  47.                          p_ty       IN NUMBER)
  48.      RETURN mdsys.sdo_geometry deterministic;
  49.    FUNCTION ST_RotateTranslateScale(p_geometry  IN mdsys.sdo_geometry,
  50.                                     p_angle_rad IN NUMBER,
  51.                                     p_rs_point  IN mdsys.sdo_geometry,
  52.                                     p_sx        IN NUMBER,
  53.                                     p_sy        IN NUMBER,
  54.                                     p_sz        IN NUMBER,
  55.                                     p_tx        IN NUMBER,
  56.                                     p_ty        IN NUMBER,
  57.                                     p_tz        IN NUMBER)
  58.      RETURN mdsys.sdo_geometry deterministic;
  59.     /* ----------------------------------------------------------------------------------------
  60.   * @function   : Affine
  61.   * @precis     : Applies a 3d affine transformation to the geometry to do things like translate, rotate, scale in one step.
  62.   * @version    : 1.0
  63.   * @description: Applies a 3d affine transformation to the geometry to do things like translate, rotate, scale in one step.
  64.   *               To apply a 2D affine transformation only supply a, b, d, e, xoff, yoff
  65.   * @usage      : Function Affine ( p_geom IN MDSYS.SDO_GEOMETRY,
  66.   * @param      : p_geom  : MDSYS.SDO_GEOMETRY : The shape to rotate.
  67.   * @param      : a, b, c, d, e, f, g, h, i, xoff, yoff, zoff :
  68.   *               Represent the transformation matrix
  69.   *                 / a  b  c  xoff \
  70.   *                 | d  e  f  yoff |
  71.   *                 | g  h  i  zoff |
  72.   *                 \ 0  0  0     1 /
  73.   *               and the vertices are transformed as follows:
  74.   *                 x' = a*x + b*y + c*z + xoff
  75.   *                 y' = d*x + e*y + f*z + yoff
  76.   *                 z' = g*x + h*y + i*z + zoff
  77.   * @requires   : SDO_UTIL.GetVertices Function
  78.   *               SYS.UTL_NLA Package
  79.   *               SYS.UTL_NLA_ARRAY_DBL Type
  80.   *               SYS.UTL_NLA_ARRAY_INT Type
  81.   * @return     : newGeom  : MDSYS.SDO_GEOMETRY : Transformed input geometry.
  82.   * @note       : Cartesian arithmetic only
  83.   *             : Not for Oracle XE. Only 10g and above.
  84.   * @history    : Simon Greener, SpatialDB Advisor - Feb 2009 - Original coding.
  85.   * @copyright  : Simon Greener, 2011, 2012
  86.   * @license    : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  87.   *               http://creativecommons.org/licenses/by-sa/2.5/au/.
  88.   *               Any bugs or improvements to be supplied back to Simon Greener
  89.   **/
  90.   FUNCTION ST_Affine( p_geometry IN mdsys.sdo_geometry,
  91.                       p_a        IN NUMBER,
  92.                       p_b        IN NUMBER,
  93.                       p_c        IN NUMBER,
  94.                       p_d        IN NUMBER,
  95.                       p_e        IN NUMBER,
  96.                       p_f        IN NUMBER,
  97.                       p_g        IN NUMBER,
  98.                       p_h        IN NUMBER,
  99.                       p_i        IN NUMBER,
  100.                       p_xoff     IN NUMBER,
  101.                       p_yoff     IN NUMBER,
  102.                       p_zoff     IN NUMBER)
  103.     RETURN mdsys.sdo_geometry deterministic;
  104. END Affine;
  105. /
  106. SHOW errors
  107. CREATE OR REPLACE
  108. package body affine
  109. AS
  110.    c_PI               CONSTANT NUMBER(16,14) := 3.14159265358979;
  111.    c_i_null_geometry  CONSTANT INTEGER       := -20120;
  112.    c_s_null_geometry  CONSTANT VARCHAR2(100) := 'Input geometry must not be null';
  113.    c_i_null_parameter CONSTANT INTEGER       := -20127;
  114.    c_s_null_parameter CONSTANT VARCHAR2(100) := 'Input parameters must not be null';
  115.    FUNCTION PI
  116.      RETURN NUMBER
  117.    AS
  118.    BEGIN
  119.      RETURN c_PI;
  120.    END PI;
  121.    FUNCTION ST_Rotate(p_geometry     IN mdsys.sdo_geometry,
  122.                       p_angle_rad    IN NUMBER,
  123.                       p_dir          IN pls_integer,
  124.                       p_rotate_point IN mdsys.sdo_geometry,
  125.                       p_line1        IN mdsys.sdo_geometry)                      
  126.      RETURN mdsys.sdo_geometry  
  127.    AS
  128.       NULL_GEOMETRY EXCEPTION;
  129.    BEGIN
  130.       IF ( p_geometry IS NULL ) THEN
  131.         raise NULL_GEOMETRY;
  132.       END IF;
  133.      -- dir - Rotation parameter for x(0), y(1), or z(2)-axis roll.
  134.      --       You cannot set p_dir => 0, 1 or 2. Only -1, -2, -3. They don't see to affect the result.
  135.      -- For two-dimensional geometries, rotation uses the p1 and angle values.
  136.      IF ( p_geometry.get_dims() = 2 ) THEN
  137.        IF ( p_angle_rad IS NULL AND p_rotate_point IS NULL ) THEN
  138.           raise_application_error(-20001,'For 2D geometry rotation, p_angle_rad and p_rotate_point must not be null',TRUE);
  139.        END IF;
  140.      ELSE  
  141.          -- For three-dimensional geometries, rotation uses either:
  142.          --     1. the angle and dir values or
  143.          --     2. the angle and line1 values.
  144.          IF ( p_angle_rad IS NULL ) THEN
  145.             raise_application_error(-20001,'For 3D geometry rotation, p_angle_rad must not be null',TRUE);
  146.          END IF;
  147.          IF ( p_dir IS NULL AND p_line1 IS NULL ) THEN
  148.             raise_application_error(-20001,'For 3D geometry rotation, both p_dir and p_line1 cannot be null',TRUE);
  149.          END IF;
  150.      END IF;
  151.      RETURN SDO_UTIL.AffineTransforms (
  152.         geometry    => p_geometry,
  153.         rotation    => 'TRUE',
  154.           p1          => p_rotate_point,
  155.           angle       => p_angle_rad,  
  156.           dir         => p_dir,  
  157.           line1       => p_line1,
  158.         translation => 'FALSE', tx => 0.0, ty => 0.0, tz => 0.0,
  159.         scaling     => 'FALSE', psc1 => NULL,    sx => 0.0,    sy => 0.0,   sz => 0.0,
  160.         shearing    => 'FALSE', shxy => 0.0,   shyx => 0.0,  shxz => 0.0, shzx => 0.0, shyz => 0.0, shzy => 0.0,
  161.         reflection  => 'FALSE', pref => NULL, lineR => NULL, dirR => -1,
  162.         planeR      => 'FALSE',    n => NULL,  bigD => NULL
  163.     );
  164.     EXCEPTION
  165.       WHEN NULL_GEOMETRY THEN
  166.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  167.         RETURN p_geometry;
  168.    END ST_Rotate;
  169.    FUNCTION ST_Rotate(p_geometry     IN mdsys.sdo_geometry,
  170.                       p_angle_rad    IN NUMBER,
  171.                       p_rotate_x     IN NUMBER,
  172.                       p_rotate_y     IN NUMBER)
  173.      RETURN mdsys.sdo_geometry  
  174.    AS
  175.       NULL_GEOMETRY EXCEPTION;
  176.    BEGIN
  177.       IF ( p_geometry IS NULL ) THEN
  178.         raise NULL_GEOMETRY;
  179.       END IF;
  180.      -- dir - Rotation parameter for x(0), y(1), or z(2)-axis roll.
  181.      -- For two-dimensional geometries, rotation uses the p1 and angle values.
  182.      IF ( p_geometry.get_dims() = 2 ) THEN
  183.        IF ( p_angle_rad IS NULL AND p_rotate_x IS NULL AND p_rotate_y IS NULL ) THEN
  184.           raise_application_error(-20001,'For 2D geometry rotation, p_angle_rad, p_rotate_x and p_rotate_y must not be null.',TRUE);
  185.        END IF;
  186.      ELSE
  187.        raise_application_error(-20001,'This version of ST_Rotate only supports 2D geometry rotation.',TRUE);
  188.      END IF;
  189.      RETURN ST_Rotate(p_geometry     => p_geometry,
  190.                       p_angle_rad    => p_angle_rad,
  191.                       p_dir          => -1,
  192.                       p_rotate_point => mdsys.sdo_geometry(2001,p_geometry.sdo_srid,mdsys.sdo_point_type(p_rotate_x,p_rotate_y,NULL),NULL,NULL),
  193.                       p_line1        => NULL);
  194.     EXCEPTION
  195.       WHEN NULL_GEOMETRY THEN
  196.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  197.         RETURN p_geometry;
  198.    END ST_Rotate;
  199.    FUNCTION ST_Rotate(p_geometry     IN mdsys.sdo_geometry,
  200.                       p_angle_rad    IN NUMBER,
  201.                       p_rotate_point IN mdsys.sdo_geometry)
  202.      RETURN mdsys.sdo_geometry  
  203.    AS
  204.       NULL_GEOMETRY EXCEPTION;
  205.    BEGIN
  206.       IF ( p_geometry IS NULL ) THEN
  207.         raise NULL_GEOMETRY;
  208.       END IF;
  209.      -- dir - Rotation parameter for x(0), y(1), or z(2)-axis roll.
  210.      -- For two-dimensional geometries, rotation uses the p1 and angle values.
  211.      IF ( p_geometry.get_dims() = 2 ) THEN
  212.         IF ( p_angle_rad IS NULL AND p_rotate_point IS NULL ) THEN
  213.           raise_application_error(-20001,'For 2D geometry rotation, p_angle_rad and p_rotate_point must not be null.',TRUE);
  214.         END IF;
  215.      ELSE
  216.         raise_application_error(-20001,'This version of ST_Rotate only supports 2D geometry rotation.',TRUE);
  217.      END IF;
  218.      RETURN ST_Rotate(p_geometry     => p_geometry,
  219.                       p_angle_rad    => p_angle_rad,
  220.                       p_dir          => -1,
  221.                       p_rotate_point => p_rotate_point,
  222.                       p_line1        => NULL);
  223.     EXCEPTION
  224.       WHEN NULL_GEOMETRY THEN
  225.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  226.         RETURN p_geometry;
  227.    END ST_Rotate;
  228.    FUNCTION ST_Rotate(p_geometry      IN mdsys.sdo_geometry,
  229.                       p_angle_rad     IN NUMBER)
  230.      RETURN mdsys.sdo_geometry
  231.    AS
  232.       NULL_GEOMETRY EXCEPTION;
  233.    BEGIN
  234.       IF ( p_geometry IS NULL ) THEN
  235.         raise NULL_GEOMETRY;
  236.       END IF;
  237.       -- dir - Rotation parameter for x(0), y(1), or z(2)-axis roll.
  238.       -- For two-dimensional geometries, rotation uses the p1 and angle values.
  239.       IF ( p_geometry.get_dims() = 2 ) THEN
  240.         IF ( p_angle_rad IS NULL ) THEN
  241.           raise_application_error(-20001,'For 2D geometry rotation, p_angle_rad must not be null.',TRUE);
  242.         END IF;
  243.       ELSE
  244.         raise_application_error(-20001,'This version of ST_Rotate only supports 2D geometry rotation.',TRUE);
  245.       END IF;
  246.       RETURN ST_Rotate(p_geometry     => p_geometry,
  247.                        p_angle_rad    => p_angle_rad,
  248.                        p_dir          => -1,
  249.                        p_rotate_point => mdsys.sdo_geometry(2001,p_geometry.sdo_srid,mdsys.sdo_point_type(0.0,0.0,0.0),NULL,NULL),
  250.                        p_line1        => NULL);
  251.     EXCEPTION
  252.       WHEN NULL_GEOMETRY THEN
  253.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  254.         RETURN p_geometry;
  255.    END ST_Rotate;
  256.    /** =================================================================== **/
  257.    /**  Scales the geometry to a new size by multiplying the ordinates with the parameters:
  258.    *    ST_Scale(geom, Xfactor, Yfactor, Zfactor, scale Point).
  259.    *    ST_Scale(geom, Xfactor, Yfactor, Zfactor).
  260.    *    ST_Scale(geom, Xfactor, Yfactor).
  261.    * **/
  262.    FUNCTION ST_Scale(p_geometry IN mdsys.sdo_geometry,
  263.                      p_sx       IN NUMBER,
  264.                      p_sy       IN NUMBER,
  265.                      p_sz       IN NUMBER,
  266.                      p_scale_pt IN mdsys.sdo_geometry)
  267.      RETURN mdsys.sdo_geometry  
  268.    AS
  269.      -- psc1 is Point on the input geometry about which to perform the scaling
  270.      v_dims   pls_integer;
  271.      v_gtype  pls_integer;
  272.      v_vertex mdsys.vertex_type;
  273.      v_psc1   mdsys.sdo_geometry;
  274.      v_sx     NUMBER := CASE WHEN p_sx IS NULL THEN 0.0 ELSE p_sx END;
  275.      v_sy     NUMBER := CASE WHEN p_sy IS NULL THEN 0.0 ELSE p_sy END;
  276.      v_sz     NUMBER := CASE WHEN p_sz IS NULL THEN 0.0 ELSE p_sz END;
  277.      NULL_GEOMETRY EXCEPTION;
  278.    BEGIN
  279.       IF ( p_geometry IS NULL ) THEN
  280.         raise NULL_GEOMETRY;
  281.       END IF;
  282.       -- Point on the input geometry about which to perform the scaling.
  283.       -- This geometry should be either a zero point (with 0,0 or 0,0,0
  284.       -- ordinates for scaling about the origin) or a nonzero point (with ordinates
  285.       -- for scaling about a point other than the origin).
  286.       v_dims := p_geometry.get_dims();
  287.       v_gtype := (v_dims * 1000) + 1;
  288.       IF ( p_scale_pt IS NULL ) THEN
  289.         v_psc1 := mdsys.sdo_geometry(v_gtype,p_geometry.sdo_srid,mdsys.sdo_point_type(0.0,0.0,CASE WHEN v_gtype=3001 THEN 0.0 ELSE NULL END),NULL,NULL);
  290.       ELSE
  291.         v_vertex := sdo_util.getVertices(p_geometry)(1);
  292.         v_psc1   := mdsys.sdo_geometry(v_gtype,p_geometry.sdo_srid,mdsys.sdo_point_type(v_vertex.x,v_vertex.y,v_vertex.z),NULL,NULL);
  293.       END IF;
  294.       RETURN SDO_UTIL.AffineTransforms (
  295.         geometry    => p_geometry,
  296.         scaling     => 'TRUE',
  297.           psc1        => v_psc1,
  298.           sx          => v_sx,
  299.           sy          => v_sy,  
  300.           sz          => v_sz,
  301.         rotation    => 'FALSE', p1 => NULL, angle => 0.0, dir => -1, line1 => NULL,
  302.         translation => 'FALSE', tx => 0.0, ty => 0.0, tz => 0.0,
  303.         shearing    => 'FALSE', shxy => 0.0,   shyx => 0.0,  shxz => 0.0, shzx => 0.0, shyz => 0.0, shzy => 0.0,
  304.         reflection  => 'FALSE', pref => NULL, lineR => NULL, dirR => -1,
  305.         planeR      => 'FALSE',    n => NULL,  bigD => NULL
  306.     );
  307.     EXCEPTION
  308.       WHEN NULL_GEOMETRY THEN
  309.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  310.         RETURN p_geometry;
  311.    END ST_Scale;
  312.    FUNCTION ST_Scale(p_geometry IN mdsys.sdo_geometry,
  313.                      p_sx       IN NUMBER,
  314.                      p_sy       IN NUMBER,
  315.                      p_sz       IN NUMBER)
  316.      RETURN mdsys.sdo_geometry  
  317.    AS
  318.       NULL_GEOMETRY EXCEPTION;
  319.    BEGIN
  320.       IF ( p_geometry IS NULL ) THEN
  321.         raise NULL_GEOMETRY;
  322.       END IF;
  323.       RETURN ST_Scale(p_geometry,
  324.                       p_sx,
  325.                       p_sy,
  326.                       p_sz,
  327.                       NULL);
  328.     EXCEPTION
  329.       WHEN NULL_GEOMETRY THEN
  330.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  331.         RETURN p_geometry;
  332.    END ST_Scale;
  333.    FUNCTION ST_Scale(p_geometry IN mdsys.sdo_geometry,
  334.                      p_sx       IN NUMBER,
  335.                      p_sy       IN NUMBER)
  336.      RETURN mdsys.sdo_geometry  
  337.    AS
  338.       NULL_GEOMETRY EXCEPTION;
  339.    BEGIN
  340.       IF ( p_geometry IS NULL ) THEN
  341.         raise NULL_GEOMETRY;
  342.       END IF;
  343.       RETURN ST_Scale(p_geometry,
  344.                       p_sx,
  345.                       p_sy,
  346.                       0.0,
  347.                       NULL);
  348.     EXCEPTION
  349.       WHEN NULL_GEOMETRY THEN
  350.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  351.         RETURN p_geometry;
  352.    END ST_Scale;
  353.    /** =================================================================== **/
  354.    /** Translates the geometry to a new location using the numeric parameters as offsets.
  355.    *   ST_Translate(geom, X, Y) or
  356.    *   ST_Translate(geom, X, Y, Z)
  357.    */
  358.    FUNCTION ST_Translate(p_geometry IN mdsys.sdo_geometry,
  359.                          p_tx       IN NUMBER,
  360.                          p_ty       IN NUMBER,
  361.                          p_tz       IN NUMBER)
  362.      RETURN mdsys.sdo_geometry
  363.    AS
  364.      v_tx NUMBER := CASE WHEN p_tx IS NULL THEN 0.0 ELSE p_tx END;
  365.      v_ty NUMBER := CASE WHEN p_ty IS NULL THEN 0.0 ELSE p_ty END;
  366.      v_tz NUMBER := CASE WHEN p_tz IS NULL THEN 0.0 ELSE p_tz END;
  367.      NULL_GEOMETRY EXCEPTION;
  368.    BEGIN
  369.      IF ( p_geometry IS NULL ) THEN
  370.        raise NULL_GEOMETRY;
  371.      END IF;
  372.      IF ( p_geometry.get_dims() = 2 ) THEN
  373.         v_tz := 0.0;
  374.      END IF;
  375.      RETURN SDO_UTIL.AffineTransforms (
  376.         geometry    => p_geometry,
  377.         translation => 'TRUE',
  378.           tx => v_tx,
  379.           ty => v_ty,
  380.           tz => v_tz,
  381.         scaling    => 'FALSE', psc1 => NULL,    sx => 0.0,    sy => 0.0,   sz => 0.0,
  382.         rotation   => 'FALSE', p1   => NULL, angle => 0.0,   dir => -1, line1 => NULL,
  383.         shearing   => 'FALSE', shxy => 0.0,   shyx => 0.0,  shxz => 0.0, shzx => 0.0, shyz => 0.0, shzy => 0.0,
  384.         reflection => 'FALSE', pref => NULL, lineR => NULL, dirR => -1,
  385.         planeR     => 'FALSE',    n => NULL,  bigD => NULL
  386.     );
  387.     EXCEPTION
  388.       WHEN NULL_GEOMETRY THEN
  389.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  390.         RETURN p_geometry;
  391.   END ST_Translate;
  392.    FUNCTION ST_Translate(p_geometry IN mdsys.sdo_geometry,
  393.                          p_tx       IN NUMBER,
  394.                          p_ty       IN NUMBER)
  395.      RETURN mdsys.sdo_geometry
  396.    AS
  397.      NULL_GEOMETRY EXCEPTION;
  398.    BEGIN
  399.      IF ( p_geometry IS NULL ) THEN
  400.        raise NULL_GEOMETRY;
  401.      END IF;
  402.      RETURN AFFINE.ST_Translate(p_geometry,
  403.                                 p_tx,
  404.                                 p_ty,
  405.                                 0.0);
  406.     EXCEPTION
  407.       WHEN NULL_GEOMETRY THEN
  408.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  409.         RETURN p_geometry;
  410.   END ST_Translate;
  411.    FUNCTION ST_RotateTranslateScale(p_geometry  IN mdsys.sdo_geometry,
  412.                                     p_angle_rad IN NUMBER,
  413.                                     p_rs_point  IN mdsys.sdo_geometry,
  414.                                     p_sx        IN NUMBER,
  415.                                     p_sy        IN NUMBER,
  416.                                     p_sz        IN NUMBER,
  417.                                     p_tx        IN NUMBER,
  418.                                     p_ty        IN NUMBER,
  419.                                     p_tz        IN NUMBER)
  420.      RETURN mdsys.sdo_geometry
  421.    AS
  422.      v_dims   pls_integer;
  423.      v_gtype  pls_integer;
  424.      v_psc1   mdsys.sdo_geometry;
  425.      v_vertex mdsys.vertex_type;
  426.      v_sx     NUMBER := CASE WHEN p_sx IS NULL THEN 0.0 ELSE p_sx END;
  427.      v_sy     NUMBER := CASE WHEN p_sy IS NULL THEN 0.0 ELSE p_sy END;
  428.      v_sz     NUMBER := CASE WHEN p_sz IS NULL THEN 0.0 ELSE p_sz END;
  429.      v_tx     NUMBER := CASE WHEN p_tx IS NULL THEN 0.0 ELSE p_tx END;
  430.      v_ty     NUMBER := CASE WHEN p_ty IS NULL THEN 0.0 ELSE p_ty END;
  431.      v_tz     NUMBER := CASE WHEN p_tz IS NULL THEN 0.0 ELSE p_tz END;
  432.      NULL_GEOMETRY EXCEPTION;
  433.    BEGIN
  434.      IF ( p_geometry IS NULL ) THEN
  435.        raise NULL_GEOMETRY;
  436.      END IF;
  437.      IF ( p_geometry.get_dims() = 2 ) THEN
  438.         v_tz := 0.0;
  439.      END IF;
  440.      -- dir - Rotation parameter for x(0), y(1), or z(2)-axis roll.
  441.      --       You cannot set p_dir => 0, 1 or 2. Only -1, -2, -3. They don't see to affect the result.
  442.      -- For two-dimensional geometries, rotation uses the p1 and angle values.
  443.      IF ( p_geometry.get_dims() = 2 ) THEN
  444.        IF ( p_angle_rad IS NULL AND p_rs_point IS NULL ) THEN
  445.           raise_application_error(-20001,'For 2D geometry rotation, p_angle_rad and p_rs_point must not be null.',TRUE);
  446.        END IF;
  447.      ELSE  
  448.        raise_application_error(-20001,'This function does not support 3D geometry rotation - Use other functions.',TRUE);
  449.      END IF;
  450.      v_dims := p_geometry.get_dims();
  451.      v_gtype := (v_dims * 1000) + 1;
  452.      IF ( p_rs_point IS NULL ) THEN
  453.         v_psc1 := mdsys.sdo_geometry(v_gtype,p_geometry.sdo_srid,mdsys.sdo_point_type(0.0,0.0,CASE WHEN v_gtype=3001 THEN 0.0 ELSE NULL END),NULL,NULL);
  454.      ELSE
  455.         v_vertex := sdo_util.getVertices(p_rs_point)(1);
  456.         v_psc1   := mdsys.sdo_geometry(v_gtype,p_geometry.sdo_srid,mdsys.sdo_point_type(v_vertex.x,v_vertex.y,CASE WHEN v_gtype=3001 THEN v_vertex.z ELSE NULL END),NULL,NULL);
  457.      END IF;
  458.      RETURN SDO_UTIL.AffineTransforms (
  459.         geometry    => p_geometry,
  460.            rotation => 'TRUE',
  461.                  p1 => p_rs_point,
  462.               angle => p_angle_rad,  
  463.                 dir => -1,  
  464.               line1 => NULL,
  465.         translation => 'TRUE',
  466.                  tx => v_tx,
  467.                  ty => v_ty,
  468.                  tz => v_tz,
  469.             scaling => 'TRUE',
  470.                psc1 => v_psc1,    
  471.                  sx => v_sx,    
  472.                  sy => v_sy,  
  473.                  sz => v_sz,
  474.         shearing    => 'FALSE', shxy => 0.0,   shyx => 0.0,  shxz => 0.0, shzx => 0.0, shyz => 0.0, shzy => 0.0,
  475.         reflection  => 'FALSE', pref => NULL, lineR => NULL, dirR => -1,
  476.         planeR      => 'FALSE',    n => NULL,  bigD => NULL
  477.     );
  478.     EXCEPTION
  479.       WHEN NULL_GEOMETRY THEN
  480.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  481.         RETURN p_geometry;
  482.   END ST_RotateTranslateScale;
  483.    /** =================================================================== **/
  484.   FUNCTION ST_Affine( p_geometry IN mdsys.sdo_geometry,
  485.                       p_a        IN NUMBER,
  486.                       p_b        IN NUMBER,
  487.                       p_c        IN NUMBER,
  488.                       p_d        IN NUMBER,
  489.                       p_e        IN NUMBER,
  490.                       p_f        IN NUMBER,
  491.                       p_g        IN NUMBER,
  492.                       p_h        IN NUMBER,
  493.                       p_i        IN NUMBER,
  494.                       p_xoff     IN NUMBER,
  495.                       p_yoff     IN NUMBER,
  496.                       p_zoff     IN NUMBER)
  497.     RETURN mdsys.sdo_geometry
  498.   IS
  499.       -- Transformation matrix is represented by:
  500.       -- / a  b  c  xoff \
  501.       -- | d  e  f  yoff |
  502.       -- | g  h  i  zoff |
  503.       -- \ 0  0  0     1 /
  504.       --
  505.       -- For 2D only need to supply: a, b, d, e, xoff, yoff
  506.       v_A          SYS.UTL_NLA_ARRAY_DBL :=
  507.                    SYS.UTL_NLA_ARRAY_DBL(
  508.                        p_a,            p_d,    NVL(p_g,0),    0,
  509.                        p_b,            p_e,    NVL(p_h,0),    0,
  510.                        NVL(p_c,0), NVL(p_f,0), NVL(p_i,1),    0,
  511.                        p_xoff,         p_yoff, NVL(p_zoff,0), 1 );
  512.       v_C           SYS.UTL_NLA_ARRAY_DBL;  -- Coordinates to be transformed
  513.       v_ipiv        SYS.utl_nla_array_int := SYS.utl_nla_array_int(0,0,0,0);
  514.       -- Geometry variables
  515.       v_dims        PLS_Integer;
  516.       v_measure_dim PLS_Integer;
  517.       v_ord         PLS_Integer;
  518.       v_sdo_point   mdsys.sdo_point_type := NULL;
  519.       v_trans_point mdsys.sdo_point_type;
  520.       v_ordinates   mdsys.sdo_ordinate_array := NULL;
  521.       -- EXCEPTION HANDLING
  522.       NULL_GEOMETRY EXCEPTION;
  523.       NULL_PARAMETER EXCEPTION;
  524.       -- Cursor over vertices
  525.       CURSOR c_coordinates( p_geometry IN mdsys.sdo_geometry) IS
  526.       SELECT v.*
  527.         FROM TABLE(mdsys.sdo_util.GetVertices(p_geometry)) v;
  528.       FUNCTION TransformPoint(p_x IN NUMBER,
  529.                               p_y IN NUMBER,
  530.                               p_z IN NUMBER)
  531.         RETURN mdsys.sdo_point_type
  532.       IS
  533.         v_info        INTEGER;
  534.         v_point       mdsys.sdo_point_type := mdsys.sdo_point_type(p_x,p_y,p_z);
  535.       BEGIN
  536.         v_C := SYS.UTL_NLA_ARRAY_DBL(p_x,
  537.                                      p_y,
  538.                                      CASE WHEN p_z IS NULL THEN 0 ELSE p_z END,  -- Supply 0 instead of NULL as this will crash LAPACK_GESV
  539.                                      0);
  540.        --  Vertices are transformed as follows:
  541.        --  x' = a*x + b*y + c*z + xoff
  542.        --  y' = d*x + e*y + f*z + yoff
  543.        --  z' = g*x + h*y + i*z + zoff
  544.        --
  545.        SYS.UTL_NLA.LAPACK_GESV (
  546.           n      => 4,      -- A number of rows and columns
  547.           nrhs   => 1,      -- B number of columns
  548.           a      => v_A,    -- matrix A
  549.           lda    => 4,      -- max(1, n)
  550.           ipiv   => v_ipiv, -- pivot indices (set to zeros)
  551.           b      => v_C,    -- matrix Result
  552.           ldb    => 4,      -- ldb >= max(1,n)
  553.           info   => v_info, -- operation status (0=sucess)
  554.           pack   => 'C'     -- how the matrices are stored
  555.                             -- (C=column-wise)
  556.         );
  557.         IF (v_info = 0) THEN
  558.           v_point.x := v_C(1);
  559.           v_point.y := v_C(2);
  560.           v_point.z := CASE WHEN p_z IS NULL THEN NULL ELSE v_C(3) END;  -- Return correct value only if one supplied.
  561.         ELSE
  562.           raise_application_error( -20001,
  563.                                    'Matrix transformation by LAPACK_GESV failed with error ' || v_info,
  564.                                    FALSE );
  565.         END IF;
  566.         RETURN v_point;
  567.       END TransformPoint;
  568.   BEGIN
  569.       IF ( p_geometry IS NULL ) THEN
  570.         raise NULL_GEOMETRY;
  571.       END IF;
  572.       IF ( p_a IS NULL OR
  573.            p_b IS NULL OR
  574.            p_d IS NULL OR
  575.            p_e IS NULL OR
  576.            p_xoff IS NULL OR
  577.            p_yoff IS NULL ) THEN
  578.           raise NULL_PARAMETER;
  579.      END IF;
  580.     v_dims        := TRUNC(p_geometry.sdo_gtype/1000,0);
  581.     v_measure_dim := MOD(trunc(p_geometry.sdo_gtype/100),10);
  582.     -- Transform any point in the geometry object
  583.     IF ( p_geometry.sdo_point IS NOT NULL ) THEN
  584.       v_sdo_point := TransformPoint(p_geometry.sdo_point.x,
  585.                                     p_geometry.sdo_point.y,
  586.                                     p_geometry.sdo_point.z);
  587.     END IF;
  588.     IF ( p_geometry.sdo_ordinates IS NOT NULL ) THEN
  589.       v_ordinates := NEW mdsys.sdo_ordinate_array(1);
  590.       v_ordinates.DELETE;
  591.       v_ordinates.EXTEND(p_geometry.sdo_ordinates.COUNT);
  592.       v_ord    := 1;
  593.       -- Loop around coordinates and apply matrix to them.
  594.       <<for_all_coords>>
  595.       FOR coord IN c_coordinates( p_geometry ) loop
  596.         v_trans_point := TransformPoint(coord.x,
  597.                                         coord.y,
  598.                                         CASE WHEN v_measure_dim=3 THEN NULL ELSE coord.z END);
  599.         v_ordinates(v_ord) := v_trans_point.x; v_ord := v_ord + 1;
  600.         v_ordinates(v_ord) := v_trans_point.y; v_ord := v_ord + 1;
  601.         IF ( v_dims >= 3 ) THEN
  602.            v_ordinates(v_ord) := v_trans_point.z; v_ord := v_ord + 1;
  603.         END IF;
  604.         IF ( v_dims >= 4 ) THEN
  605.            v_ordinates(v_ord) := coord.w; v_ord := v_ord + 1;
  606.         END IF;
  607.       END LOOP for_all_coords;
  608.     END IF;
  609.     RETURN mdsys.sdo_geometry(p_geometry.sdo_gtype,
  610.                               p_geometry.sdo_srid,
  611.                               v_sdo_point,
  612.                               p_geometry.sdo_elem_info,
  613.                               v_ordinates
  614.                               );
  615.     EXCEPTION
  616.       WHEN NULL_GEOMETRY THEN
  617.          raise_application_error(c_i_null_geometry,c_s_null_geometry,TRUE);
  618.         RETURN p_geometry;
  619.       WHEN NULL_PARAMETER THEN
  620.         raise_application_error( c_i_null_parameter,c_s_null_parameter,TRUE );
  621.         RETURN p_geometry;
  622.   END ST_Affine;
  623. END Affine;
  624. /
  625. SHOW errors

To test this package I created the following:

  1. WITH testGeom AS (
  2.   SELECT mdsys.sdo_geometry(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.0,2.0,2.0,4.0,8.0,4.0,12.0,4.0,12.0,10.0,8.0,10.0,5.0,14.0)) AS geom,
  3.          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.0,2.0,0.0, 2.0,4.0,22.22, 8.0,4.0,37.04, 12.0,4.0,59.26, 12.0,10.0,74.07, 8.0,10.0,92.59, 5.0,14.0,100.0)) AS geom3D,
  4.          mdsys.sdo_geometry(2001,NULL,sdo_point_type(2.0,2.0,NULL),NULL,NULL) AS rotatePoint
  5.     FROM dual
  6. )
  7. SELECT Affine.ST_Rotate(a.geom, cogo.radians(45), -1, rotatePoint, NULL ) AS geom FROM testGeom a UNION ALL
  8. SELECT Affine.ST_Rotate(a.geom, cogo.radians(90)) AS geom FROM testGeom a UNION ALL
  9. SELECT Affine.ST_Rotate(a.geom, cogo.radians(135), 2.0, 2.0) AS geom FROM testGeom a UNION ALL
  10. SELECT Affine.ST_Rotate(a.geom, cogo.radians(225), rotatePoint) AS geom FROM testGeom a UNION ALL
  11. SELECT Affine.ST_Scale(a.geom,2,2,0) AS geom FROM testGeom a UNION ALL
  12. SELECT Affine.ST_Scale(a.geom,3,3) AS geom FROM testGeom a UNION ALL
  13. SELECT Affine.ST_Translate(a.geom,10,10) AS geom FROM testGeom a UNION ALL
  14. SELECT Affine.ST_Translate(a.geom,10,10,10) AS geom FROM testGeom a UNION ALL
  15. SELECT Affine.ST_RotateTranslateScale(a.geom,cogo.radians(45),rotatePoint,2,2,0,10,10,0) AS geom FROM testGeom a;
  16. -- Results
  17. --
  18. GEOM
  19. -------------------------------------------------------------------------------
  20. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.0,2.0, 0.585786437626906,3.4142135623731, 4.8284271247462,7.65685424949238, 7.65685424949239,10.4852813742386, 3.41421356237311,14.7279220613579, 0.585786437626913,11.8994949366117, -4.36396103067892,12.6066017177982))
  21. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-2.0,2.0, -4.0,2.00000000000001, -3.99999999999999,8.00000000000001, -3.99999999999998,12.0, -9.99999999999998,12.0, -9.99999999999999,8.00000000000002, -14.0,5.00000000000002))
  22. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.0,2.0, 0.585786437626901,0.585786437626908, -3.65685424949237,4.8284271247462, -6.48528137423856,7.6568542494924, -10.7279220613579,3.41421356237312, -7.89949493661167,0.585786437626927, -8.60660171779823,-4.3639610306789))
  23. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.0,2.0, 3.41421356237309,0.585786437626899, -0.828427124746214,-3.65685424949237, -3.65685424949242,-6.48528137423855, 0.585786437626852,-10.7279220613578, 3.41421356237305,-7.89949493661167, 8.36396103067888,-8.60660171779824))
  24. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(4.0,4.0, 4.0,8.0, 16.0,8.0, 24.0,8.0, 24.0,20.0, 16.0,20.0, 10.0,28.0))
  25. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(6.0,6.0, 6.0,12.0, 24.0,12.0, 36.0,12.0, 36.0,30.0, 24.0,30.0, 15.0,42.0))
  26. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(12.0,12.0, 12.0,14.0, 18.0,14.0, 22.0,14.0, 22.0,20.0, 18.0,20.0, 15.0,24.0))
  27. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(12.0,12.0, 12.0,14.0, 18.0,14.0, 22.0,14.0, 22.0,20.0, 18.0,20.0, 15.0,24.0))
  28. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(12.0,12.0, 9.17157287525381,14.8284271247462, 17.6568542494924,23.3137084989848, 23.3137084989848,28.9705627484771, 14.8284271247462,37.4558441227157, 9.17157287525383,31.7989898732233, -0.727922061357837,33.2132034355964))

I have compared the output of some of these to those in my GEOM package and they appear to give the correct results.

Now, comparing my ST_Affine function against this example from PostGIS 2.0, I get the same result.

PostGIS (edited)

  1. --Rotate a 3d line 180 degrees about the z axis.  
  2. SELECT ST_AsEWKT(ST_Affine(the_geom,  cos(pi()), -sin(pi()), 0,  sin(pi()), cos(pi()), 0,  0, 0, 1,  0, 0, 0)) AS using_affine
  3.   FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 1 4 3)') AS the_geom) AS foo;
  4.         using_affine
  5. -----------------------------
  6.  LINESTRING(-1 -2 3,-1 -4 3)
  7. (1 ROW)

My ST_Affine Function

  1. -- Documented test for PostGIS
  2. --
  3. WITH geom3d AS (
  4. SELECT sdo_geometry(3002,NULL,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,2,3,1,4,3))
  5.           AS the_geom
  6.   FROM dual
  7. )
  8. SELECT AFFINE.ST_Affine(p_geometry=>the_geom,
  9.                         p_a=>cos(CONSTANTS.pi()),
  10.                         p_b=>0-sin(CONSTANTS.pi()),
  11.                         p_c=>0,  
  12.                         p_d=>sin(CONSTANTS.pi()),
  13.                         p_e=>cos(CONSTANTS.pi()),
  14.                         p_f=>0,  
  15.                         p_g=>0,
  16.                         p_h=>0,
  17.                         p_i=>1,
  18.                         p_xoff=>0,
  19.                         p_yoff=>0,
  20.                         p_zoff=>0) AS using_affine
  21.   FROM geom3d a;
  22. -- Results
  23. --
  24. USING_AFFINE
  25. -----------------
  26. SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-0.999999999999994,-2.0,3.0, -0.999999999999987,-4.0,3.0))

As you can see they are the same.

I think this AFFINE package is a start to something more helpful than the current one-size-fits-all approach.

Any fixes, corrections, improvements, email me and I will modify the package.

I hope this is of help to somone.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment