1

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
Improve this question

1 Answer 1

Reset to default
2

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.

Comparing offsets at tolerances 0.05m to 5m

At tolerance 0.05m (5cm): Tolerance 0.05

At tolerance 0.5m (50cm) Tolerance 0.5m

At tolerance 1m Tolerance 1m

At tolerance 2m Tolerance 2m

At tolerance 3m Tolerance 3m

At tolerance 4m Tolerance 4m

At tolerance 5m Tolerance 5m

Improve this answer
10
  • Thanks, I'd actually got pretty much all this figured out and was planning to answer my own question. I've found that just putting an arbitrary large number for end_measure works fine, but good to know how to do it properly. I'm currently trying to figure out how to ensure my offset line ends up as elem_type=[1,2,1]. The trick seems to be setting a value of tolerance that is not too small
    – Simon Notley
    Commented Dec 11, 2021 at 16:03
  • You mean you are getting something like [1,2,1],[n,2,2],[n,2,1],[n,2,2]... ? The [n,2,2] sections represent circular arcs. If you can share one example of a line, then I'm happy to take a look ...
    – Albert Godfrind
    Commented Dec 11, 2021 at 18:13
  • I'll see if I can find an easy way to post some raw data, but for one particular line when I use a tolerance of 1 I get SDO_ELEM_INFO=[1,4,5,1,2,1,5,2,2,9,2,1,251,2,2,255,2,1], but for tolerance 5 I get SDO_ELEM_INFO=[1,2,1] which is what the application expects.
    – Simon Notley
    Commented Dec 11, 2021 at 18:41
  • Ah yes: [1,4,5] is a sort of introducer: it says the line is formed of 5 sections. [1,2,1] is the first one, a straight-line section. Then [5,2,2] is a circular arc, then [9,2,1] is again a straight line section etc. The little circular arcs match spikes in the original line. Using a more coarse tolerance (5m in your case) should avoid those, but may not avoid all of them.
    – Albert Godfrind
    Commented Dec 11, 2021 at 18:47
  • There are more aspects to this question: is your data projected or lat/long ? Circular arcs are only allows in projected data. If we generate some in long/lat, then that would be a bug. Another one is that there is also a function that can densify arcs = turn them into straight lines, and turn the result into [1,2,1]
    – Albert Godfrind
    Commented Dec 11, 2021 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.