Oracle Spatial – How to Use SDO_LRS.OFFSET_GEOM_SEGMENT to Offset a Line

lineoracle-spatialroad

I am trying to offset a line perpendicularly by a few metres (it follows a road and I'd like to move it to one side of the road). The line is an SDO_GEOMETRY object of SDO_GTYPE = 2002 (LINE/CURVE).

I have two issues, possibly different symptoms of the same thing. My query is as follows:

SELECT sdo_lrs.offset_geom_segment(GEOMETRY, 0, 100, 5) 
FROM NWKGEOMETRY n 
WHERE GEOMETRYID = '59bf525cc14d431b8129676f97a023c0'

The result of this is:

SQL Error [13331] [72000]: ORA-13331: invalid LRS segment
ORA-06512: at "MDSYS.SDO_LRS", line 7558
ORA-06512: at "MDSYS.SDO_LRS", line 7536
ORA-06512: at "MDSYS.SDO_LRS", line 7514
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_LRS", line 7347
ORA-06512: at "MDSYS.SDO_LRS", line 7532
ORA-06512: at "MDSYS.SDO_LRS", line 7554

This suggests to me that my line is not a valid argument for LRS but my research suggests that lines are valid, although apparently they have to have a 'measure' dimension (although the docs suggest this is automatically calculated). Which brings me on to issue 2… how do I know the end_measure argument? I just want to offset the whole line regardless of its length.

EDIT: Here's a working query including some raw data that gives very different results with a tolerance of 1 vs 5.

WITH G AS 
(
SELECT
        SDO_LRS.CONVERT_TO_STD_GEOM(
                sdo_lrs.offset_geom_segment(
                    SDO_LRS.CONVERT_TO_LRS_GEOM(
                        SDO_CS.TRANSFORM(
                            SDO_GEOMETRY(
                                2002,
                                4326,
                                NULL,
                                SDO_ELEM_INFO_ARRAY(1,2,1),
                                SDO_ORDINATE_ARRAY(-1.451529, 53.252595, -1.451461, 53.252583, -1.451331, 53.25257, -1.45137, 53.252466, -1.451406, 53.252404, -1.45142, 53.252383, -1.451553, 53.252314, -1.451647, 53.252165, -1.451769, 53.251988, -1.451824, 53.25192, -1.45197, 53.251773, -1.452165, 53.251572, -1.452594, 53.251216, -1.452816, 53.251032, -1.453442, 53.250513, -1.453727, 53.250277, -1.454054, 53.250001, -1.454499, 53.249644, -1.45471, 53.249461, -1.455238, 53.249032, -1.455583, 53.24874, -1.455843, 53.248522, -1.455986, 53.248396, -1.456547, 53.247956, -1.457157, 53.247563, -1.457676, 53.247291, -1.457768, 53.24724, -1.458206, 53.247034, -1.458517, 53.2469, -1.45905, 53.2467, -1.459664, 53.246507, -1.460069, 53.246387, -1.46025, 53.246298, -1.460357, 53.246229, -1.460361, 53.246185, -1.46038, 53.246143, -1.460413, 53.246104, -1.460454, 53.246074, -1.460502, 53.246049, -1.460558, 53.246029, -1.460619, 53.246016, -1.460683, 53.24601, -1.460746, 53.246011, -1.460807, 53.246018, -1.460866, 53.246032, -1.461038, 53.246043, -1.461273, 53.246022, -1.462071, 53.245747, -1.462436, 53.245593, -1.463174, 53.245262, -1.464062, 53.244731, -1.464974, 53.243976, -1.465114, 53.243819, -1.465265, 53.24363, -1.465534, 53.243297, -1.465784, 53.242917, -1.465909, 53.242681, -1.465986, 53.242524, -1.46611, 53.242182, -1.466205, 53.241834, -1.46624, 53.241623, -1.466274, 53.241144, -1.466286, 53.240826, -1.466288, 53.239846, -1.466288, 53.239786, -1.46621, 53.239666, -1.466177, 53.239621, -1.4661, 53.239598, -1.466035, 53.239565, -1.465985, 53.239523, -1.465955, 53.239475, -1.465945, 53.239428, -1.465953, 53.239382, -1.465978, 53.239337, -1.466025, 53.239293, -1.46609, 53.239257, -1.466165, 53.23914, -1.466175, 53.239123, -1.466236, 53.239012, -1.466181, 53.238488, -1.466163, 53.238152, -1.466213, 53.237829, -1.466282, 53.237527, -1.466358, 53.237123, -1.466319, 53.237033, -1.466317, 53.236942, -1.466303, 53.236817, -1.466329, 53.236776, -1.46547, 53.236295, -1.465408, 53.236239, -1.465476, 53.236169, -1.46552, 53.236116, -1.465677, 53.235901, -1.466034, 53.235327, -1.466237, 53.23495, -1.466383, 53.234679, -1.467068, 53.233294, -1.467126, 53.233222, -1.467231, 53.233058, -1.4673, 53.232847, -1.467339, 53.232678, -1.467476, 53.23207, -1.467569, 53.231582, -1.467576, 53.231499, -1.467591, 53.231418, -1.467623, 53.231175, -1.467624, 53.231141, -1.467616, 53.230964, -1.467608, 53.230932, -1.467602, 53.230903, -1.467575, 53.230791, -1.467833, 53.230769)
                            ),
                            81989
                        )
                    ),
            0,
            1000000,
            5,
            5
                )) AS GEOMETRY
            
        FROM DUAL
 )
SELECT SDO_UTIL.GETNUMVERTICES(GEOMETRY), GEOMETRY FROM G

Best Answer

Indeed, the LRS functions only operate on LRS segments, and your input is a regular line (type 2002). That line is geometrically valid, but it is not a valid LRS line.

LRS lines are lines where each point is equipped with a measure that represent the distance of that point from the start of the line. And yes, you do not have to supply a measure for each and every point: all missing measures will be automatically computed. At the minimum, you need a measure for the first point and one for the last point.

So in order to use the function sdo_lrs.offset_geom_segment() you need to first convert your input geometry to LRS, then apply the function, then convert the result back to a regular geometry (by stripping out the measures).

Here is an example on how to do that.

1. Create some sample data

create table t1 (id number, geometry sdo_geometry);
insert into t1 values (
  1,
  sdo_geometry(2002, 4326, null, 
    sdo_elem_info_array(1, 2, 1),
    sdo_ordinate_array(
      -82.983894, 39.972126, 
      -82.983841, 39.972687, 
      -82.983788, 39.973175, 
      -82.983650, 39.973949, 
      -82.983856, 39.975281, 
      -82.983986, 39.975979, 
      -82.984154, 39.976837
    )
  )
);
commit;

2. Convert to LRS:

select sdo_lrs.convert_to_lrs_geom(geometry) from t1;

SDO_GEOMETRY(
  3302, 
  4326, 
  NULL, 
  SDO_ELEM_INFO_ARRAY(1, 2, 1), 
  SDO_ORDINATE_ARRAY(
    -82.983894, 39.972126, 0, 
    -82.983841, 39.972687, 62.4544989, 
    -82.983788, 39.973175, 116.828, 
    -82.983650, 39.973949, 203.573214, 
    -82.983856, 39.975281, 352.513934, 
    -82.983986, 39.975979, 430.807387, 
    -82.984154, 39.976837, 527.149598
  )
)

Notice the geometry type is now 3302:

The first 3 says we now have three ordinates per point: x,y,m
The second 3 says which ordinate has the measure (the last one).

This is so as to distinguish a 2D line with measures (3302) from a 3D line (3002) where each point has three ordinates: x, y and z. Note that for 3D lines (3002), the equivalent LRS line will be a type 4402:

First 4 says that we have now four ordinates per point: x,y,z,m
Second 4 says that the measure is in the last ordinate.

Also notice the values of the measures. They range from 0 to 527.149598. This is the length of the geometry in meters. The measure of the intermediate points was automatically computed by interpolation. This the default behavior when no measures are passed: start is 0, end is the length of the line. I could have passed specific values for start to end: for example the actual measured length of the line ...

3. Apply the offset function

select 
  sdo_lrs.offset_geom_segment(
    sdo_lrs.convert_to_lrs_geom(geometry), 
    0, 100, 
    5
  ) 
from t1;

SDO_GEOMETRY(
  3302, 
  4326, 
  NULL, 
  SDO_ELEM_INFO_ARRAY(1, 2, 1), 
  SDO_ORDINATE_ARRAY(
    -82.983952, 39.9721293, 0, 
    -82.983899, 39.9726903, 62.4207306, 
    -82.983899, 39.9726907, 62.474808, 
    -82.983863, 39.9730277, 100
  )
)

The result is a new LRS line that is offset 5m to the left side of the line (left/right are relative to the orientation of the line, from start to end).

The line is also clipped between measures 0 and 100. In other words, your offset line is only for the first 100 meters of the input line. To take an offset of the entire line, you need to pass its length, like this:

select 
  sdo_lrs.offset_geom_segment(
    sdo_lrs.convert_to_lrs_geom(geometry), 
    0, sdo_geom.sdo_length(geometry), 
    5
  ) 
from t1;

SDO_GEOMETRY(
  3302, 
  4326, 
  NULL, 
  SDO_ELEM_INFO_ARRAY(1, 2, 1), 
  SDO_ORDINATE_ARRAY(
    -82.983952, 39.9721293, 0, 
    -82.983899, 39.9726903, 62.58711, 
    -82.983899, 39.9726907, 62.6413316, 
    -82.983846, 39.9731787, 117.130286, 
    -82.983846, 39.9731811, 117.395974, 
    -82.983709, 39.9739494, 203.68367, 
    -82.983914, 39.9752751, 352.239086, 
    -82.984044, 39.9759725, 430.620887, 
    -82.984212, 39.9768303, 527.149598
  )
)

Notice the shape has 9 points, whereas the original input had 7.

4. Convert the result to a regular geometry:

This combines all three steps in a single operation.

select 
  sdo_lrs.convert_to_std_geom(
    sdo_lrs.offset_geom_segment(
      sdo_lrs.convert_to_lrs_geom(geometry), 
      0, sdo_geom.sdo_length(geometry), 
      5
    )
  )
from t1;

SDO_GEOMETRY(
  2002, 
  4326, 
  NULL, 
  SDO_ELEM_INFO_ARRAY(1, 2, 1), 
  SDO_ORDINATE_ARRAY(
    -82.983952, 39.9721293, 
    -82.983899, 39.9726903, 
    -82.983899, 39.9726907, 
    -82.983846, 39.9731787, 
    -82.983846, 39.9731811, 
    -82.983709, 39.9739494, 
    -82.983914, 39.9752751, 
    -82.984044, 39.9759725, 
    -82.984212, 39.9768303
  )
)

Now I can also write the result back to the original table:

insert into t1 (id, geometry)
select 
  id+1000, 
  sdo_lrs.convert_to_std_geom(
    sdo_lrs.offset_geom_segment(
      sdo_lrs.convert_to_lrs_geom(geometry), 
      0, sdo_geom.sdo_length(geometry), 
      5
    )
  )
from t1;

EDIT Here is a more complete example using your data and showing the impact of the tolerance setting:

Let's create a table with your sample geometry:

create table t1 (
  id number primary key,
  geometry sdo_geometry,
  offset number,
  tolerance number
);
insert into t1 (id, geometry)
values (
  0,
  SDO_GEOMETRY(
    2002,
    4326,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(
      -1.451529, 53.252595, -1.451461, 53.252583, -1.451331, 53.25257, -1.45137, 53.252466, -1.451406, 53.252404, -1.45142, 53.252383, -1.451553, 53.252314, -1.451647, 53.252165, -1.451769, 53.251988, -1.451824, 53.25192, -1.45197, 53.251773, -1.452165, 53.251572, -1.452594, 53.251216, -1.452816, 53.251032, -1.453442, 53.250513, -1.453727, 53.250277, -1.454054, 53.250001, -1.454499, 53.249644, -1.45471, 53.249461, -1.455238, 53.249032, -1.455583, 53.24874, -1.455843, 53.248522, -1.455986, 53.248396, -1.456547, 53.247956, -1.457157, 53.247563, -1.457676, 53.247291, -1.457768, 53.24724, -1.458206, 53.247034, -1.458517, 53.2469, -1.45905, 53.2467, -1.459664, 53.246507, -1.460069, 53.246387, -1.46025, 53.246298, -1.460357, 53.246229, -1.460361, 53.246185, -1.46038, 53.246143, -1.460413, 53.246104, -1.460454, 53.246074, -1.460502, 53.246049, -1.460558, 53.246029, -1.460619, 53.246016, -1.460683, 53.24601, -1.460746, 53.246011, -1.460807, 53.246018, -1.460866, 53.246032, -1.461038, 53.246043, -1.461273, 53.246022, -1.462071, 53.245747, -1.462436, 53.245593, -1.463174, 53.245262, -1.464062, 53.244731, -1.464974, 53.243976, -1.465114, 53.243819, -1.465265, 53.24363, -1.465534, 53.243297, -1.465784, 53.242917, -1.465909, 53.242681, -1.465986, 53.242524, -1.46611, 53.242182, -1.466205, 53.241834, -1.46624, 53.241623, -1.466274, 53.241144, -1.466286, 53.240826, -1.466288, 53.239846, -1.466288, 53.239786, -1.46621, 53.239666, -1.466177, 53.239621, -1.4661, 53.239598, -1.466035, 53.239565, -1.465985, 53.239523, -1.465955, 53.239475, -1.465945, 53.239428, -1.465953, 53.239382, -1.465978, 53.239337, -1.466025, 53.239293, -1.46609, 53.239257, -1.466165, 53.23914, -1.466175, 53.239123, -1.466236, 53.239012, -1.466181, 53.238488, -1.466163, 53.238152, -1.466213, 53.237829, -1.466282, 53.237527, -1.466358, 53.237123, -1.466319, 53.237033, -1.466317, 53.236942, -1.466303, 53.236817, -1.466329, 53.236776, -1.46547, 53.236295, -1.465408, 53.236239, -1.465476, 53.236169, -1.46552, 53.236116, -1.465677, 53.235901, -1.466034, 53.235327, -1.466237, 53.23495, -1.466383, 53.234679, -1.467068, 53.233294, -1.467126, 53.233222, -1.467231, 53.233058, -1.4673, 53.232847, -1.467339, 53.232678, -1.467476, 53.23207, -1.467569, 53.231582, -1.467576, 53.231499, -1.467591, 53.231418, -1.467623, 53.231175, -1.467624, 53.231141, -1.467616, 53.230964, -1.467608, 53.230932, -1.467602, 53.230903, -1.467575, 53.230791, -1.467833, 53.230769
    )
  )
);
commit;

Now, let's add some additional rows with a variety of tolerances:

insert into t1 (id, offset, tolerance) values (1, 5, 0.05);
insert into t1 (id, offset, tolerance) values (2, 5, 0.5);
insert into t1 (id, offset, tolerance) values (3, 5, 1);
insert into t1 (id, offset, tolerance) values (4, 5, 2);
insert into t1 (id, offset, tolerance) values (5, 5, 3);
insert into t1 (id, offset, tolerance) values (6, 5, 4);
insert into t1 (id, offset, tolerance) values (7, 5, 5);
commit;

And compute the offset for each of the tolerances:

update t1 set geometry =
  sdo_lrs.convert_to_std_geom(
    sdo_lrs.offset_geom_segment(
      sdo_lrs.convert_to_lrs_geom(
        (select geometry from t1 where id=0)
      ),
      0,
      1000000,
      offset,
      tolerance
    )
  )
where id > 0;
commit;

The results:

select id, sdo_util.getnumvertices(geometry) points, sdo_geom.sdo_length(geometry) length, g.geometry.sdo_elem_info elem_info
from t1 g
order by id;

        ID     POINTS     LENGTH ELEM_INFO
---------- ---------- ---------- ----------------------------------------
         0        112 2938.45919 SDO_ELEM_INFO_ARRAY(1, 2, 1)
         1        161 2949.91775 SDO_ELEM_INFO_ARRAY(1, 2, 1)
         2        139 2944.26491 SDO_ELEM_INFO_ARRAY(1, 2, 1)
         3        124 2944.20602 SDO_ELEM_INFO_ARRAY(1, 2, 1)
         4        113 2943.16652 SDO_ELEM_INFO_ARRAY(1, 2, 1)
         5        111 2942.08011 SDO_ELEM_INFO_ARRAY(1, 2, 1)
         6        100 2940.66025 SDO_ELEM_INFO_ARRAY(1, 2, 1)
         7         91 2934.78718 SDO_ELEM_INFO_ARRAY(1, 2, 1)

8 rows selected.

You can see clearly that the finer the tolerance the more vertices are generated for the offset. Notice also the difference in length that reflects the accuracy of the result. You would naturally expect the offset line to be longer, but for tolerance 5 it is actually shorter

The difference between the various results is better illustrated visually. Here is the data overlaid on a Goggle road map, zoomed on a roundabout.

The red line is your original output. The others are the offsets at various tolerances. The pink line is the one at tolerance 5m. You can see it just cuts across the roundabout. The other ones are all better. After that I show the individual results at each tolerance. It looks like tolerances at 2 or even 3m are giving good results.

But of course, everything depends on what those results will be used for.