Object Implementation of Oracle SDO_LRS
Introduction
The Oracle SDO_LRS package contains a powerful set of Linear Referencing functions.
With the opening up of access to the SDO_LRS package, these functions can be of use outside of an LRS context. For example, selecting a sub-section of an unmeasured linestring, snapping points to ordinary linestrings, or splitting ordinary
linestrings at an arbitrary point.
From my perspective, there are a few challenges with the SDO_LRS package (and SDO_GEOM, SDO_UTIL) and its functions:
1. It is a package and not an object (see examples);
2. The naming of the functions within the package is not to my taste.
I personally don’t like CONVERT_TO_LRS_GEOM when it could be, more pointedly, Add_Measure and,
related, why GEOM_SEGMENT_START_MEASURE when it could be just Start_Measure?
Again, why VALIDATE_LRS_GEOMETRY when it could be just Validate?
3. It does not support implied (non-measured) linestrings natively.
Though CONVERT_TO_LRS_GEOM with no parameters returns a “measured” geometry whose measures are based on length (here unmeasured and measured are logically equivalent). However, one has to invoke the conversion every time one wants to use SDO_LRS functions on unmeasured linestrings.
One of the great things about Oracle (or most databases) is that the developer can abstract, or wrap not just tables into views but code packages into objects.
What I am going to demonstrate is how to create a complementary API that “wrappers” the existing SDO_LRS package to implement the following benefits:
1. Consistent, more natural, naming convention (beauty is though in the eye of the beholder);
2. Modern “dot notation” object referencing removing the need for (ugly) function nesting;
3. Native support for non-measured linestrings;
4. Naming that supports cross-database implementations eg PostGIS, SQL Server Spatial (see here);
5. Hide licensing issues eg GEOM_SEGMENT_END_PT/GEOM_SEGMENT_END_PT_3D by having a single wrapper for both.
The object implementation is called T_LRS (T{ype}_LRS) and is documented here.
Examples
Here’s a bunch of calls to the object LRS methods. The first uses an unmeasured linestring the second a measured linestring.
-- Unmeasured
with data as (
select t_lrs(sdo_geometry(2002,28355,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(252230.478,5526918.373, 252400.08,5526918.373, 252230.478,5527000.0)),0.005) as geom,
sdo_geometry(2001,28355,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(252410.0,5526920.3)) as point
from dual
)
select d.geom.ST_LRS_Add_Measure().geom as geom,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Valid_Measure(100) as vMeasure1,
d.geom.ST_LRS_Valid_Measure(100) as vMeasure2,
d.geom.ST_LRS_Valid_Measure(9999999) as vMeasure3,
d.geom.ST_LRS_Add_Measure()
.ST_Length() as endM3,
d.geom.ST_Length('unit=FOOT') as endM4,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Is_Shape_Pt_Measure(0) as isShpPtM1,
d.geom.ST_LRS_Is_Shape_Pt_Measure(0) as isShpPtM2,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Translate_Measure(100).geom as tGeom1,
d.geom.ST_LRS_Translate_Measure(100).geom as tGeom2,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Scale_Measure(10,d.geom.ST_Length()+11,100).geom as sGeom,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Reset_Measure().geom as geomR,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Start_Measure() as startM1,
d.geom.ST_LRS_Start_Measure() as startM2,
d.geom.ST_LRS_Start_Point() as spoint,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_End_Measure() as endM1,
d.geom.ST_LRS_End_Measure() as endM2,
d.geom.ST_LRS_End_Point() as epoint,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Measure_Range() as mRange1,
d.geom.ST_LRS_Measure_Range() as mRange2,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Is_Measure_Increasing() as mIncreasing1,
d.geom.ST_LRS_Is_Measure_Increasing() as mIncreasing2,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Is_Measure_Decreasing() as mDecreasing1,
d.geom.ST_LRS_Is_Measure_Decreasing() as mIncreasing2,
d.geom.ST_LRS_Percentage_To_Measure(50.0) as mFromPct,
d.geom.ST_LRS_Measure_To_Percentage(20.0) as pFromM,
d.geom.ST_LRS_Find_Measure(point) fm,
d.geom.ST_LRS_Find_Offset(point) as oGeom,
d.geom.ST_LRS_Interpolate_Point(point) as ip,
case when d.geom.ST_LRS_Find_Measure(point) = d.geom.ST_LRS_Interpolate_Point(point)
then 'EQUAL'
else 'UNEQUAL'
end as find_interp,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Reverse_Geometry().geom as rGeom,
d.geom.ST_LRS_Reverse_Measure().geom as rMGeom,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Locate_Between(20,200).geom as bGeom1,
d.geom.ST_LRS_Locate_Between(20,200).geom as bGeom2,
d.geom.ST_LRS_Locate_Between(20,200).ST_Round(3,3,2,3).ST_Length() as lbLength,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Locate_Along(200,0) as lGeom1,
d.geom.ST_LRS_Locate_Along(200,0) as lGeom2,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Locate_Along(200,10) as lOGeom,
d.geom.ST_LRS_Add_Measure()
.ST_LRS_Project_Point(point) as pPoint, /* Same as ST_Snap */
d.geom.ST_LRS_Snap(point) as sPoint,
d.geom.ST_LRS_Add_Measure(1.0,d.geom.ST_LRS_End_Measure()+1.999)
.ST_LRS_Reverse_Measure()
.ST_LRS_Locate_Between(20,200)
.ST_LRS_Translate_Measure(0.999)
.ST_LRS_Drop_Measure().geom as rGeom
from data d;
-- Results not shown except last column
RGEOM
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2002,28355,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,2),MDSYS.SDO_ORDINATE_ARRAY(252393.862,5527010.972,252318.449,5527053.244,252240.362,5527016.146))
-- Measured
WITH data As (
SELECT t_LRS(SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(2.0,2.0,0.0,2.0,4.0,3.218,8.0,4.0,12.872,12.0,4.0,19.308,12.0,10.0,28.962,8.0,10.0,35.398,5.0,14.0,43.443)),0.0005) as geom
FROM dual
)
select d.geom.ST_LRS_Locate_Between(20,100)
.ST_LRS_Translate_Measure(-20)
.ST_LRS_Reverse_Measure()
.geom as mGeom
from data d
Union all
select d.geom.ST_LRS_Locate_Along(20,-10) as oGeom
from data d
Union all
select d.geom.ST_LRS_Line_Substring(0.1,0.5).geom as oGeom
from data d;
mGeom
-------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3302,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(12,4.43,23.443,12,10,14.481,8,10,8.045,5,14,0))
MDSYS.SDO_GEOMETRY(3301,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1),MDSYS.SDO_ORDINATE_ARRAY(22,4.43,20))
MDSYS.SDO_GEOMETRY(3302,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(2,2,0,2,2.001,.002))
Nesting and Complexity
One benefit I claim for an object approach is:
2. Modern “dot notation” object referencing removing the need for (ugly) function nesting;
This can be best demonstrated in the following example.
-- Nested calls using T_LRS Object
with data as (
select t_lrs(sdo_geometry(2002,28355,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(252230.478,5526918.373, 252400.08,5526918.373, 252230.478,5527000.0)),0.005) as geom,
sdo_geometry(2001,28355,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(252410.0,5526920.3)) as point
from dual
)
SELECT d.geom.ST_LRS_Add_Measure(1.0,d.geom.ST_LRS_End_Measure()+1.999)
.ST_LRS_Reverse_Measure()
.ST_LRS_Locate_Between(20,200)
.ST_LRS_Translate_Measure(0.999)
.ST_LRS_Drop_Measure()
.geom as Geom
FROM data d;
GEOM
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2002, 28355, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 2), SDO_ORDINATE_ARRAY(252393.86157927, 5527010.97191897, 252318.44919985, 5527053.24446732, 252240.36162692, 5527016.1455958))
-- Nested calls using SDO_LRS package
with data as (
select sdo_geometry(2002,28355,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(252230.478,5526918.373, 252400.08,5526918.373, 252230.478,5527000.0)) as geom,
sdo_geometry(2001,28355,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(252410.0,5526920.3)) as point
from dual
)
select MDSYS.SDO_LRS.CONVERT_TO_STD_GEOM (
lrs_geom => MDSYS.SDO_LRS.TRANSLATE_MEASURE(
geom_segment => MDSYS.SDO_LRS.OFFSET_GEOM_SEGMENT(
geom_segment => MDSYS.SDO_LRS.REVERSE_MEASURE(
geom_segment => MDSYS.SDO_LRS.CONVERT_TO_LRS_GEOM(
standard_geom => a.geom,
start_measure => 1.0,
end_measure => MDSYS.SDO_GEOM.SDO_LENGTH(
geom => a.geom,
tol => 0.005
)
)
),
start_measure => 20,
end_measure => 200,
offset => 0,
tolerance => 0.005
),
translate_m => 0.999
)
) as geom
from data a;
GEOM
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2002, 28355, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 2), SDO_ORDINATE_ARRAY(252394.2906799, 5527010.31482701, 252318.87878307, 5527053.22900606, 252240.40700061, 5527016.20522579))
I think it self-evident (a dangerous claim?) that the object based version is more readable, but it is also easier and faster to construct.
Download
The T_LRS package and its test code and documentation is available for download for free (please make a donation) from the shop interface on this website. Note that the implementation is complete for 2D (2002) and measured 2D (eg 3302) linestrings, but does not fully support the *_3D versions of functions (to do this would take little less than a day to complete).
Related Discussion
Oracle Spatial is arguably one of the most powerful implementations of spatial in the marketplace. Oracle Spatial came about with 8i and its Object extensions (cf O-RDBMS), yet its implementation does not make extensive use of those object extensions with most of the power of Spatial being in SDO_GEOM and SDO_UTIL packages. For example, SDO_GEOMETRY has only a handful of methods (8 at 21c) e.g., get_dims, get_wkt, but not things like SDO_LENGTH, SDO_AREA, SDO_BUFFER etc.
Why?
Well, not many of these functions can be moved into the SDO_GEOMETRY object. The placement of the some functions in the SDO_GEOM package was probably done for licensing purposes (when SDO_GEOM.SDO_LENGTH/SDO_AREA etc required a Spatial license). But SDO_LENGTH/SDO_AREA have been free for many, many years and are still not methods of SDO_GEOMETRY. This is possibly because SDO_GEOMETRY is a singly inherited object and as such it could be argued that it makes no sense to add, for example, a length method when the object could be a point (this is also a problem with the SDO_GEOM.SDO_LENGTH method). (Single inheritance issues affect other database spatial implementations as well eg PostGIS, SQL Server Spatial.)
One thing I find myself doing with SDO_GEOM and SDO_UTIL is that I often mistake where I think a function resides, looking in one when I should have looked in the other. Would object methods help this? My view is that it could. For example, I think SDO_BUFFER could be moved, as could many of the object property methods in SDO_UTIL eg GetNumVertices, GetNumElems, GetFirstVertex, GetLastVertex etc.
The question of single inheritance and method location is addressed in Oracle Spatial’s SQL Multimedia Type Interface (ST_GEOMETRY) implementation. It is just a pity that it is not better documented, more widely known, and used.
Conclusion
My little effort here shows what can/could be done to provide alternate access to underlying Oracle Spatial SDO_LRS functionality where that functionality is quite homogeneous. As such I hope this is of interest to someone.