3

I have a data set consisting of 2D linestrings which are stored in an Oracle database in table my_lines with an id and a geometry column (units are in meters).

In a separate table my_intervals with a line_id, a start and an end column, I have a set of intervals with start and end distances describing parts of the linestrings in my_lines - referenced by id and line_id. The intervals do not overlap.

I would like to split the lines in my_lines using the intervals in my_intervals so I get the line segments representing the intervals and the remaining line segments not represented by intervals.

Example:
In the following example I have a linestring which is 600m long, and two intervals, [0m; 200m] and [400m; 600m]:

Splitting example

So far I have looked at the SDO_LRS.CLIP_GEOM_SEGMENT but I must admit that I am lost on how the spatial parts of Oracle works compared to postgis, FME, OGR and similar, and furthermore how it can be combined into returning the different line segments.

Question:
How can this be done using some more or less magical Oracle SELECT statement?

  • The LRS package is a component of Oracle Spatial. Are you sure you are licensed for Oracle Spatial? – Wilson Aug 31 '17 at 3:17
  • I would be happy to take a stab at this. Unfortunately, while Oracle Spatial is enabled in my database, I am not licensed to use it. – Wilson Aug 31 '17 at 3:20
  • @Wilson: As far as the concept goes the license issue should be irrelevant, but thanks for pointing it out anyway :) We do however have a license for Oracle Spatial. – Chau Aug 31 '17 at 10:21
3

The clear solution for that is the Linear Referencing mechanism that comes with Oracle Spatial, i.e. the SDO_LRS package. Here is an example.

Setup the lines. Here the coordinates are in long/lat, but that is irrelevant: the measures will automatically be in meters.

create table my_lines (id number primary key, geometry sdo_geometry);

insert into my_lines (id, geometry)
values (
  1,
  sdo_geometry(2002, 4326, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(-95.368599, 29.77528, -95.366074, 29.77133, -95.365822, 29.771009, -95.365341, 29.769661))
);
insert into my_lines (id, geometry)
values (
  2,
  SDO_GEOMETRY(2002, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.91713, 41.869625, -87.918503, 41.875614, -87.919487, 41.882412, -87.918793, 41.886154, -87.917976, 41.88892, -87.918007, 41.891277, -87.918205, 41.892559, -87.918793, 41.895317, -87.919586, 41.897678, -87.919907, 41.90377))
);
insert into my_lines (id, geometry)
values (
  3,
  SDO_GEOMETRY(2002, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-93.061897, 45.037601, -93.072807, 45.036789, -93.078575, 45.035954, -93.084892, 45.034962, -93.086441, 45.034683, -93.087669, 45.033966, -93.089127, 45.03249))
);
commit;

Setup the intervals. Each interval is uniquely identified by the identifier of the line it is related to (LINE_ID) and a sequential number (INTERVAL_ID) since there can be multiple intervals on the same line. (Note that START is a reserved word - I used START_M instead).

create table my_intervals (
  interval_id number,
  section_id number,
  start_m number,
  end_m number,
  primary key (line_id, interval_id),
  foreign key (line_id) references my_lines
);
insert into my_intervals (line_id, interval_id, start_m, end_m)
values (1, 1, 0, 200);
insert into my_intervals (line_id, interval_id, start_m, end_m)
values (1, 2, 400, 600);
insert into my_intervals (line_id, interval_id, start_m, end_m)
values (2, 1, 1500, 1800);
insert into my_intervals (line_id, interval_id, start_m, end_m)
values (2, 2, 3200, 3300);
insert into my_intervals (line_id, interval_id, start_m, end_m)
values (2, 3, 3750, 3800);
insert into my_intervals (line_id, interval_id, start_m, end_m)
values (3, 1, 120, 1370);
commit;

The intervals look like this:

SQL> select * from my_intervals;

   LINE_ID INTERVAL_ID    START_M      END_M
---------- ----------- ---------- ----------
         1           1          0        200
         1           2        400        600
         2           1       1500       1800
         2           2       3200       3300
         2           3       3750       3800
         3           1        120       1370

6 rows selected.

Here is how you clip the selected sections

select i.line_id, interval_id, 
  sdo_lrs.clip_geom_segment (
    sdo_lrs.convert_to_lrs_geom (l.geometry), 
    i.start_m, 
    i.end_m
  ) as interval_clip
from my_lines l, my_intervals i
where l.id = i.line_id;

To do so, the lines must be turned into LRS segments, i.e. populated with measures. This is done using the SDO_LRS.CONVERT_TO_LRS_GEOM() function. In the above example, each line is turned into a LRS segment with measures going from 0 to the length of the line in meters.

Then the lines are joined with the intervals on line id, and each LRS geometry is clipped on the start and end measures of each interval, using the SDO_LRS.CLIP_GEOM_SEGMENT() function.

The results of the above query are like this:

LINE_ID INTERVAL_ID INTERVAL_CLIP
------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      1           1 SDO_GEOMETRY(3302, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-95.368599, 29.77528, 0, -95.367592, 29.7737042, 200))
      1           2 SDO_GEOMETRY(3302, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-95.366584, 29.7721284, 400, -95.366074, 29.77133, 501.336381, -95.365822, 29.771009, 544.464272, -95.365651, 29.7705306, 600))
      2           1 SDO_GEOMETRY(3302, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.919378, 41.8829974, 1500, -87.918882, 41.8856728, 1800))
      2           2 SDO_GEOMETRY(3302, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.919608, 41.8980892, 3200, -87.919655, 41.8989888, 3300))
      2           3 SDO_GEOMETRY(3302, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.919868, 41.9030371, 3750, -87.919892, 41.9034869, 3800))
      3           1 SDO_GEOMETRY(3302, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-93.063412, 45.0374883, 120, -93.072807, 45.036789, 864.385524, -93.078575, 45.035954, 1328.26259, -93.079092, 45.0358728, 1370))

6 rows selected.

Notice that you get LRS segments back, i.e. complete with measures. If you just want plain geometries back (without measures), then you need to convert them back using the SDO_LRS.CONVERT_TO_STD_GEOM(). For example:

select i.line_id, i.section_id, 
  sdo_lrs.convert_to_std_geom(
    sdo_lrs.clip_geom_segment (
      sdo_lrs.convert_to_lrs_geom (l.geometry), 
      i.start_m, 
      i.end_m
    )
  ) interval_clip
from my_lines l, my_intervals i
where l.id = i.line_id;

which returns:

LINE_ID INTERVAL_ID INTERVAL_CLIP
------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      1           1 SDO_GEOMETRY(2002, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-95.368599, 29.77528, -95.367592, 29.7737042))
      1           2 SDO_GEOMETRY(2002, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-95.366584, 29.7721284, -95.366074, 29.77133, -95.365822, 29.771009, -95.365651, 29.7705306))
      2           1 SDO_GEOMETRY(2002, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.919378, 41.8829974, -87.918882, 41.8856728))
      2           2 SDO_GEOMETRY(2002, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.919608, 41.8980892, -87.919655, 41.8989888))
      2           3 SDO_GEOMETRY(2002, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.919868, 41.9030371, -87.919892, 41.9034869))
      3           1 SDO_GEOMETRY(2002, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-93.063412, 45.0374883, -93.072807, 45.036789, -93.078575, 45.035954, -93.079092, 45.0358728))

6 rows selected.

And as Wilson pointed out, LRS is a feature of Oracle Spatial. You are only allowed to use any of the above iff you are properly licensed to use Oracle Spatial.

  • 1
    Thanks for pointing me in the right direction, especially the part with the GEOM->LRS->GEOM conversion. But as far as I see, your answer doesn't provide the part of the lines which aren't represented by an interval? – Chau Aug 31 '17 at 10:36
  • 2
    Ah yes. I had missed that part in your question. I can see two possible solutions. One is to complete the intervals table with dummy intervals that represent the "non-clipped" sections. The other is to use the SDO_GEOM.SDO_DIFFERENCE to remove the clipped sections from the original lines. I will complete my example when I get the chance. – Albert Godfrind Sep 1 '17 at 11:35
  • So far I have taken the difference approach, since I don't want to maintain a set of dummy intervals. Thanks for providing a couple of options! – Chau Sep 4 '17 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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