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.