Implementing an ST_SnapToGrid (PostGIS) function for Oracle Spatial

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.