I am continuing my looking at PostGIS and developing new functions for Oracle that plug-gaps identified in PostGIS. Much of this has been wrapping existing functions in my existing GEOM PL/SQL package, but some has involved developing new functions.

Today, because of a comment on ST_Affine by Regina Obe, I added two additional wrappers for my existing TOLERANCE function so that someone used to using PostGIS can easily adapt to Oracle Spatial.

Now, PostGIS’s ST_SnapToGrid function actually has four variants as show in the online documentation. I have chosen only to implement the second and third of these via the following wrappers over an amended Tolerance function.

  Function Tolerance (
    p_geometry in MDSYS.SDO_Geometry,
    p_dimarray in MDSYS.SDO_Dim_Array )
    return MDSYS.SDO_Geometry deterministic;

  /** Overloads of above */
  Function Tolerance (
    p_geometry in MDSYS.SDO_Geometry,
    p_tolerance in Number )
    return MDSYS.SDO_Geometry deterministic;

  Function Tolerance( p_geometry  IN MDSYS.SDO_GEOMETRY,
                      p_X_tolerance IN NUMBER,
                      p_Y_tolerance IN NUMBER,
                      p_Z_tolerance IN NUMBER := NULL)
    RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;

  /** PostGIS Functions Wrappers */
  Function ST_SnapToGrid( p_geometry  IN MDSYS.ST_GEOMETRY,
                          p_size      IN NUMBER)
    RETURN MDSYS.ST_GEOMETRY DETERMINISTIC;

  Function ST_SnapToGrid( p_geometry  IN MDSYS.ST_GEOMETRY,
                          p_sizeX     IN NUMBER,
                          p_sizeY     IN NUMBER )
    RETURN MDSYS.ST_GEOMETRY DETERMINISTIC;

Now, let’s conduct some tests.

Firstly, let’s look at the single tolerance/size versions of the Tolerance/ST_SnapToGrid functions. (Note the use of the mdsys.OGC_LineStringFromText wrapper function to generate an ST_LINESTRING object and note that the output is pure Oracle: in particular note the SDO_GEOMETRY object inside the MDSYS.ST_LINESTRING object output by the ST_SnapToGrid function.)

SELECT GEOM.Tolerance(a.geom,0.005) as geom
  FROM (SELECT mdsys.SDO_Geometry('LINESTRING(1.12345 1.3445,2.43534 2.03998398)') as geom 
          FROM dual) a;

GEOM
-----------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12,1.34,2.44,2.04))

1 rows selected
  
SELECT GEOM.ST_SnapToGrid(a.geom,0.005) as st_geom
  FROM (SELECT mdsys.OGC_LineStringFromText('LINESTRING(1.12345 1.3445,2.43534 2.03998398)') as geom 
          FROM dual) a;

ST_GEOM
--------------------------------------------------------------------------------------------------------------------------------------
MDSYS.ST_LINESTRING(MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12,1.34,2.44,2.04)))

1 rows selected

Secondly, and finally, the two tolerance parameter versions (note, this time we output the data as WKT).

SELECT GEOM.Tolerance(a.geom,0.005,0.05).Get_WKT() as geom
  FROM (SELECT mdsys.SDO_Geometry('LINESTRING(1.12345 1.3445,2.43534 2.03998398)') as geom 
          FROM dual) a;

GEOM
-------------------------------
LINESTRING (1.12 1.3, 2.44 2.0)

1 rows selected

SELECT GEOM.ST_SnapToGrid(a.geom,0.005,0.05).Get_WKT() as st_geom
  FROM (SELECT mdsys.OGC_LineStringFromText('LINESTRING(1.12345 1.3445,2.43534 2.03998398)') as geom 
          FROM dual) a;

ST_GEOM
-------------------------------
LINESTRING (1.12 1.3, 2.44 2.0)

1 rows selected

Note that the different X and Y tolerances were correctly applied and are visible in the output coordinate values.

I hope this is of use to someone.