Top 5 Recent Articles

ARTICLES CATEGORIES

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.