Snapping a Point to an Oracle Spatial SDO_GEOMETRY LineString
Introduction
A common function in geoprocessing is the need to snap a point to a linear (line or polygon) geometry.
Up until the release of the MDSYS.SDO_GEOM.SDO_CLOSEST_POINTS() procedures there were no “out of the box” solution to this problem for Oracle Locator users other than custom or open source (eg JTS) implementations. Just such custom implementations are available in the database stored procedures for TSQL and PL/SQL available from my shop.
However the making available of all of the Spatial functionality for all users (cf the demise of Locator), the LRS function SDO_LRS.PROJECT_PT can be used to snap a point to a line.
This article shows how to use MDSYS.SDO_GEOM.SDO_CLOSEST_POINTS(), and SDO_LRS.PROJECT_PT, and a little wrapper code, to achieve snapping.
MDSYS.SDO_GEOM.SDO_CLOSEST_POINTS
The power of SDO_CLOSEST_POINTS is that it actually computes the point on each geometry which is nearest to the other. If one of the geometries is a point and the other a linestring then the resultant closest point on the linestring is the snap point. (The nearest point on the point is the point, by definition!) While this article is only interested in the point snapping case, it is useful that this function can compute the nearest point a linestring (say) comes close to another linestring or polygon (say).
Earlier versions up to and including 19c of Oracle declared MDSYS.SDO_GEOM.SDO_CLOSEST_POINTS to be a procedure.
SDO_GEOM.SDO_CLOSEST_POINTS( geom1 IN SDO_GEOMETRY, geom2 IN SDO_GEOMETRY, tolerance IN NUMBER, unit IN VARCHAR2, dist OUT NUMBER, geoma OUT SDO_GEOMETRY, geomb OUT SDO_GEOMETRY);
Note that is returns the computed data via three output parameters.
Calling the procedure requires an anonymous PL/SQL block:
set serveroutput on size unlimited
DECLARE
v_out_geom1 MDSYS.SDO_GEOMETRY;
v_out_geom2 MDSYS.SDO_GEOMETRY;
v_out_distance number;
BEGIN
SDO_GEOM.SDO_CLOSEST_POINTS (
geom1 => sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,2),sdo_ordinate_array(0,0,1.0,1.0,2,0)),
geom2 => sdo_geometry(2001,NULL,SDO_POINT_TYPE(1.1,1.1,NULL),NULL,NULL),
tolerance => 0.0005,
unit => null,
dist => v_out_distance,
geoma => v_out_geom1,
geomb => v_out_geom2
);
dbms_output.put_line(v_out_distance);
dbms_output.put_line(v_out_geom1.get_wkt());
dbms_output.put_line(v_out_geom2.get_wkt());
END;
/
show errors
.104536101718726
POINT (1.09053574604252 0.995893206467704)
POINT (1.1 1.1)
PL/SQL procedure successfully completed.
Function instead of Procedure
Using the procedure it is easy to program a function (with a more meaningful name) that snaps a point to a linestring:
Create or Replace
Function ST_Snap (
p_line in sdo_geometry,
p_point in sdo_geometry,
p_tolerance in number := 0.005
)
return mdsys.sdo_geometry
As
v_tolerance number := NVL(p_tolerance,0.05);
v_distance number;
v_point_on_line mdsys.sdo_geometry;
v_point_on_point mdsys.sdo_geometry;
Begin
if ( p_line is null or p_point is null ) Then
return p_point;
End if;
If ( p_point.get_gtype() <> 1 ) Then
return p_point;
End If;
MDSYS.SDO_GEOM.SDO_CLOSEST_POINTS(
geom1 => p_line,
geom2 => p_point,
tolerance => v_tolerance,
unit => NULL,
dist => v_distance,
geoma => v_point_on_line,
geomb => v_point_on_point
);
IF ( v_point_on_line is not null ) THEN
RETURN v_point_on_line;
ELSE
RETURN p_point;
END IF;
End ST_Snap;
/
Tests
The ST_Snap function can be called as follows.
select ST_Snap(sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,0.9999,1.001)),
sdo_geometry(2001,null,sdo_point_type(0.9998,1.0011,null),null,null),
0.0005) as geom
from dual;
GEOM
---------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(0.9998, 1.0011, NULL), NULL, NULL)
select ST_Snap(sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,1.0,0.0)),
sdo_geometry(2001,null,sdo_point_type(0.98,0.005,null),null,null),
0.0005) as point
from dual;
POINT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(0.98, 0, NULL), NULL, NULL)
select ST_Snap(sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,1.0,0.0)),
sdo_geometry(2001,NULL,SDO_POINT_TYPE(1.1,0.0,NULL),NULL,NULL),
0.0005) as point
from dual;
POINT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(1, 0, NULL), NULL, NULL)
select ST_Snap(sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,2),sdo_ordinate_array(0,0,1.0,1.0,2,0)),
sdo_geometry(2001,NULL,SDO_POINT_TYPE(1.1,1.1,NULL),NULL,NULL),
0.0005) as point
from dual;
POINT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(1.09053575, 0.99589321, NULL), NULL, NULL)
SDO_CLOSEST_POINTS as function
From version 21c onwards SDO_CLOSEST_POINTS is now a function.
SDO_GEOM.SDO_CLOSEST_POINTS( geom1 IN SDO_GEOMETRY, geom2 IN SDO_GEOMETRY, tolerance IN NUMBER, unit IN VARCHAR2 [, ellipsoidal IN VARCHAR2 DEFAULT NULL] ) RETURN SDO_CLOSEST_POINTS_TYPE;
Where SDO_CLOSEST_POINTS_TYPE is:
CREATE TYPE sdo_closest_points_type AS OBJECT (
dist NUMBER,
geoma SDO_GEOMETRY,
geomb SDO_GEOMETRY
);
Two examples
-- 1. Raw use
SELECT sdo_geom.sdo_closest_points (
geom1 => sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,2),sdo_ordinate_array(0,0,1.0,1.0,2,0)),
geom2 => sdo_geometry(2001,NULL,SDO_POINT_TYPE(1.1,1.1,NULL),NULL,NULL),
tolerance => 0.005,
unit => null) cp
FROM dual;
CP(DIST, GEOMA(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES), GEOMB(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_CLOSEST_POINTS_TYPE(
.104536101718726,
SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(1.09053574604252 0.995893206467704,NULL),NULL,NULL),
SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(1.1 1.1, NULL),NULL,NULL)
)
-- 2.0 Selecting just the snap point
SELECT f.cp.geoma as snap_point
FROM (SELECT sdo_geom.sdo_closest_points (
geom1 => sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,2),sdo_ordinate_array(0,0,1.0,1.0,2,0)),
geom2 => sdo_geometry(2001,NULL,SDO_POINT_TYPE(1.1,1.1,NULL),NULL,NULL),
tolerance => 0.005,
unit => null) cp
FROM dual
) f;
SNAP_POINT
-----------------------------------------------------------------------------------------SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(1.09053574604252 0.995893206467704,NULL),NULL,NULL)
SDO_LRS.PROJECT_PT
SDO_LRS.PROJECT_PT is defined as follows:
SDO_LRS.PROJECT_PT(
geom_segment IN SDO_GEOMETRY,
point IN SDO_GEOMETRY,
tolerance IN NUMBER DEFAULT 1.0e-8
[, offset OUT NUMBER]
) RETURN SDO_GEOMETRY;
SDO_LRS.PROJECT_PT’s geom_segment must be measured (it cannot be 2D), so the ancillary function, SDO_LRS.CONVERT_TO_LRS_GEOM must be called on any non-LRS inputs. So, it can be used as follows:
SELECT SDO_LRS.PROJECT_PT(
SDO_LRS.CONVERT_TO_LRS_GEOM(
sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,2),sdo_ordinate_array(0,0,1.0,1.0,2,0))
),
sdo_geometry(2001,NULL,SDO_POINT_TYPE(1.1,1.1,NULL),NULL,NULL)) as point
FROM dual;
POINT
--------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(3301, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(1.09053575, 0.99589321, 1.66145621))
Wrapper function to hide use of multiple SDO_LRS calls
If you want to hide the CONVERT_TO_LRS_GEOM call, encapsulating the call inside another function will be needed:
create or replace
Function ST_Snap (
p_line in sdo_geometry,
p_point in sdo_geometry,
p_tolerance in number := 0.005
)
return mdsys.sdo_geometry
As
v_tolerance number := NVL(p_tolerance,0.05);
v_line mdsys.sdo_geometry;
v_point mdsys.sdo_geometry;
Begin
if ( p_line is null or p_point is null ) Then
return p_point;
End if;
If ( p_line.get_gtype() not in (2,6) ) Then
return p_point;
End If;
If ( p_point.get_gtype() <> 1 ) Then
return p_point;
End If;
v_line := p_line;
IF ( p_line.Get_LRS_Dim() = 0 ) THEN
v_line := MDSYS.SDO_LRS.CONVERT_TO_LRS_GEOM(p_line);
END IF;
v_point := MDSYS.SDO_LRS.PROJECT_PT(v_line,p_point,v_tolerance);
IF ( p_line.get_lrs_dim() = 0 ) THEN
RETURN MDSYS.SDO_LRS.CONVERT_TO_STD_GEOM(v_point);
ELSE
RETURN v_point;
END IF;
End ST_Snap;
/
ST_Snap Example
SELECT ST_Snap(sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,2),sdo_ordinate_array(0,0,1.0,1.0,2,0)),
sdo_geometry(2001,NULL,SDO_POINT_TYPE(1.1,1.1,NULL),NULL,NULL),
0.005) as point
FROM dual;
POINT
----------------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(1.09053575, 0.99589321, NULL), NULL, NULL)
NOTE: This function returns a point with the same dimensionality of the input line.
Conclusion
There is much powerful functionality buried inside the Oracle Spatial API which, with a little but of coding can be repackaged for purposes other than originally intended.
I hope this little article is of use to someone.