Posted on

ST_Translate (ie move)

ST_Translate is a member function of T_GEOMETRY which translates the underlying geometry to a new location.

Function Specification.

Member Function ST_Translate (p_tx in number,
                              p_ty in number,
                              p_tz in number default null )
         Return &&INSTALL_SCHEMA..T_GEOMETRY deterministic


The ST_Translate Member Function translates the underlying geometry to a new location by applying the translation values (deltas) to its ordinates.

The function MOVES the geometry to a new location.


    p_tx (number) - Translation factor for X ordinates.
    p_ty (number) - Translation factor for Y ordinates.
    p_tz (number) - Translation factor for Z ordinates (if null, the Z ordinate is not changed).


The function translates the supplied geometry using supplied ordinate values (deltas).


The function is a wrapper over mdsys.SDO_UTIL.AffineTransforms.

The corresponding function in SQL Server Spatial is STMove and in MySQL Spatial, ST_Move.


With testGeom as (
  select T_GEOMETRY(mdsys.sdo_geometry(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2,2,2,4,8,4,12,4,12,10,8,10,5,14)),0.005,2,1) as tgeom
    from dual
   Union All
  select T_GEOMETRY(mdsys.sdo_geometry(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2,2,1, 2,4,2, 8,4,3, 12,4,4, 12,10,5, 8,10,6, 5,14,7)),0.005,2,1) as tgeom
    from dual
select a.tgeom.ST_CoordDimension() as coordDimension,
       'ST_Translate(p_tx,p_ty,p_tz)' as TranslateTest,
       a.tgeom.ST_Translate(p_tx=>10.0,p_ty=>10.0,p_tz=>case when a.tgeom.ST_CoordDimension()=2 then null else 5.0 end).geom as geom
  from testGeom a;

-------------- ---------------------------- --------------------------------------------------------------------------------------------------------------------------------------
             2 ST_Translate(p_tx,p_ty,p_tz) SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(12,12,12,14,18,14,22,14,22,20,18,20,15,24))
             3 ST_Translate(p_tx,p_ty,p_tz) SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(12,12,6,12,14,7,18,14,8,22,14,9,22,20,10,18,20,11,15,24,12))
Posted on

Change Log

2019-07-19: SQL Server Spatial.

    Corrected installation errors
    Fixed STPointFromText null Z with M handling
    Moved all PostGIS LRS functions into a single script.
    Added STLocateBetweenElevations, STLineSubstring, and STLineInterpolatePoint
    Modified STExtract to extract polygon ring sub elements where ring defined using CircularCurve or CompoundCurve.
    Modified STIsMeasureDecreasing
    Modified STCogo2Line
    Improved function documentation.
    Updated html documentation.

2019-05-17: Created SQL Server Spatial STDensify and STGeometryTypes functions.

2019-01-12: Created Linux bash script for Oracle PLSQL Object code.