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.