Top 5 Recent Articles

ARTICLES CATEGORIES

Split Oracle Sdo_Geometry Linestring at a Known Point using SDO_LRS and PL/SQL (2)

Split Linestring at known point

Introduction

Splitting a linestring at a known point is a common spatial tool many practitioners need. (This was initially covered in an earlier post that is effectively deprecated given the contents of this article.)

Some databases, such as PostGIS, make available that functionality (PostGIS ST_Split) as a part of the standard product.

In the past, Oracle has not provided such functionality, so much so that I wrote my own (see article).

But since the free availability of SDO_LRS to all Oracle users (see licensing terms to ensure its availability in your situation), this functionality can be achieved using the SDO_LRS package.

Required SDO_LRS functions

The following function uses the following SDO_LRS functions to break a linestring in two:

  • SDO_LRS.CONVERT_TO_LRS_GEOM – This takes any non-measured input linestring and converts it to measured using length as the measure value (0 at start, SDO_GEOM.SDO_LENGTH at end).
  • SDO_LRS.PROJECT_PT – Snaps the input point to the measured linestring returning a point with a measure value.
  • SDO_LRS.SPLIT_GEOM_SEGMENT – Splits measured linestring at provided measure value (from PROJECT_PT).
  • SDO_LRS.CONVERT_TO_STD_GEOM – If input linestring was not measured, the measures on the split linestrings are removed.

The SDO_LRS SPLIT_GEOM_SEGMENT is a procedure. To use it in SQL SELECT etc statements we will need a function. Such a function will need a PL/SQL object as a return type which we can use to return two SDO_GEOMETRY objects. From 21c onwards we can use the SDO_CLOSEST_POINTS_TYPE (if this type is not available you could create it in the same schema as the ST_Split function and remove it when the MDSYS type becomes available).

This is the definition of SDO_CLOSEST_POINTS_TYPE:

CREATE TYPE sdo_closest_points_type AS OBJECT (
    dist   NUMBER,
    geoma  SDO_GEOMETRY,
    geomb  SDO_GEOMETRY
);

PL/SQL ST_Snap Function

We can now create a function using all the above functions, procedures and types. I call it ST_Split, you can call it what you want.

create or replace 
  function ST_Split (
    p_line      in mdsys.sdo_geometry,
    p_point     in mdsys.sdo_geometry,
    p_tolerance in number := 0.005
)
return sdo_closest_points_type 
as
  v_point     mdsys.sdo_geometry;
  v_line      mdsys.sdo_geometry;
  v_segment_1 mdsys.sdo_geometry;
  v_segment_2 mdsys.sdo_geometry;
  v_tolerance number := 0.005;
  v_measure   number;
  v_offset    number;
begin
  if ( p_line is null or p_point is null ) Then
    return null;
  End if;
  If ( p_line.get_gtype() not in (2,6) ) Then
    return null;
  End If;
  If ( p_point.get_gtype() <> 1 ) Then
    return null;
  End If;
  v_line := p_line;
  -- If p_line is not measured then add measures based on line length...
  IF ( p_line.Get_LRS_Dim() = 0 ) THEN
    v_line := MDSYS.SDO_LRS.CONVERT_TO_LRS_GEOM(
                 standard_geom => p_line
              );
  END IF;
  -- Compute snap point which will include measure and also the offset distance
  v_point   := MDSYS.SDO_LRS.PROJECT_PT(
                  geom_segment => v_line,
                  point        => p_point,
                  tolerance    => v_tolerance,
                  offset       => v_offset
               );
  -- Project_Pt always returned point with ordinates in sdo_ordinates
  -- We need the measure value so extract it
  v_measure := v_point.sdo_ordinates(v_point.get_lrs_dim()) ;
  -- Now split the input linestring at the measure computed by PROJECT_PT
  MDSYS.SDO_LRS.SPLIT_GEOM_SEGMENT(
     geom_segment  => v_line,
     split_measure => v_measure,
     segment_1     => v_segment_1,
     segment_2     => v_segment_2
  );
  -- Return original point offset distance as well.
  IF ( p_line.get_lrs_dim() = 0 ) THEN
    RETURN sdo_closest_points_type (
             dist  => v_offset,
             geoma => MDSYS.SDO_LRS.CONVERT_TO_STD_GEOM(v_segment_1),
             geomb => MDSYS.SDO_LRS.CONVERT_TO_STD_GEOM(v_segment_2)
    ); 
  ELSE
    RETURN sdo_closest_points_type (
             dist  => v_offset,
             geoma => v_segment_1,
             geomb => v_segment_2
    ); 
  END IF;

end ST_Split;
/
show errors

Tests

Here are some examples of the use of the function.

-- Split unmeasured linestring

SELECT ST_Split(sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,1,0,0, 0.74,0.74, 1.0,1.0,2.0,2.0, 3.0,2.5,4.2,3.5)),
                MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(0.74,0.74,NULL),NULL,NULL),
                0.0005) as result
 FROM dual;

RESULT
---------------------------------------------------------------------------------------------------------------------------SDO_CLOSEST_POINTS_TYPE(
0, 
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 1, 0, 0, 0.74, 0.74)), 
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0.74, 0.74, 1, 1, 2, 2, 3, 2.5, 4.2, 3.5))
)

-- Unpivot
with data as (
select ST_Split(sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,1,0,0, 0.74,0.74, 1.0,1.0,2.0,2.0, 3.0,2.5,4.2,3.5)),
                MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(0.74,0.74,NULL),NULL,NULL),
                0.0005) as geoms
 from dual
)
select t.geoms.geoma as geom from data t
union all
select t.geoms.geomb as geom from data t;

GEOM
--------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 1, 0, 0, 0.74, 0.74))
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0.74, 0.74, 1, 1, 2, 2, 3, 2.5, 4.2, 3.5))

-- With measures
select ST_Split(MDSYS.SDO_GEOMETRY(3302,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(0, 1, 0, 0, 0, 1, 0.74, 0.74, 2.04651804, 1, 1, 2.41421356, 2, 2, 3.82842712, 3, 2.5, 4.94646111, 4.2, 3.5, 6.50851105)),
                MDSYS.SDO_GEOMETRY(3301,NULL,MDSYS.SDO_POINT_TYPE(0.74,0.74,0.0),NULL,NULL),
                0.0005) geoms
  from dual;
  
GEOMS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_CLOSEST_POINTS_TYPE(
0, 
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 1, 0, 0, 0, 1, 0.74, 0.74, 2.04651804)),
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0.74, 0.74, 2.04651804, 1, 1, 2.41421356, 2, 2, 3.82842712, 3, 2.5, 4.94646111, 4.2, 3.5, 6.50851105))
)

-- Repeat but only select geometries
SELECT f.geoms.geoma,
       f.geoms.geomb
  FROM (SELECT ST_Split(MDSYS.SDO_GEOMETRY(3302,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(0, 1, 0, 0, 0, 1, 0.74, 0.74, 2.04651804, 1, 1, 2.41421356, 2, 2, 3.82842712, 3, 2.5, 4.94646111, 4.2, 3.5, 6.50851105)),
                        MDSYS.SDO_GEOMETRY(3301,NULL,MDSYS.SDO_POINT_TYPE(0.74,0.74,0.0),NULL,NULL),
                        0.0005) geoms
          FROM dual
      ) f;

GEOMS.GEOMA
--------------------------------------------------------------------------------------------------------------------------
GEOMS.GEOMB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 1, 0, 0, 0, 1, 0.74, 0.74, 2.04651804))
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0.74, 0.74, 2.04651804, 1, 1, 2.41421356, 2, 2, 3.82842712, 3, 2.5, 4.94646111, 4.2, 3.5, 6.50851105))

-- CircularString
SELECT ST_Split(sdo_geometry(2002,NULL,NULL,sdo_elem_info_array(1,2,2),sdo_ordinate_array(0,0, 1,1, 2,0)),
                sdo_geometry(2001,NULL,MDSYS.SDO_POINT_TYPE(0.74,0.74,NULL),NULL,NULL),
                0.0005) as geoms
  FROM dual f;

GEOMS
------------------------------------------------------------------------------------------------------------------------------------------
SDO_CLOSEST_POINTS_TYPE(
-0.2156531, 
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 2), SDO_ORDINATE_ARRAY(0, 0, 0.18406924, 0.57814964, 0.668514, 0.94346014)), 
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 2), SDO_ORDINATE_ARRAY(0.668514, 0.94346014, 1, 1, 2, 0))
)

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 article is useful to someone.