|
Implementing an ST_SnapToGrid (PostGIS) function for Oracle Spatial
Wednesday February 04 2009 at 18:54
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.
        
|
SpatialDB Advisor
Home
Comment