4

I have a ROAD table that has a row for each vertex of each line:

+---------+--------------+-------------+
| ROAD_ID | VERTEX_INDEX | MEASURE_VAL |
+---------+--------------+-------------+
|       1 |            1 |           0 |
|       1 |            2 |         1.2 |
|       1 |            3 |         2.8 |
|       1 |            4 |         4.0 |
|       1 |            5 |         4.3 |
|       1 |            6 |         6.7 |
|       1 |            7 |         7.1 |
+---------+--------------+-------------+
|       2 |            1 |           0 |
|       2 |            2 |         5.2 |
|       2 |            3 |         7.7 |
|       2 |            4 |         9.6 |
|       2 |            5 |        11.8 |
+---------+--------------+-------------+
|       3 |            1 |           0 |
|       3 |            2 |         2.4 |
|       3 |            3 |         4.9 |
+---------+--------------+-------------+

And an EVENT table with rows that pertain to portions of roads:

+----------+---------+--------------+------------+
| EVENT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+----------+---------+--------------+------------+
|       01 |       1 |          2.8 |        5.9 |
|       02 |       2 |          3.0 |        5.6 |
|       03 |       3 |            0 |        2.6 |
+----------+---------+--------------+------------+

I want to select the vertices from ROAD where the MEASURE_VAL encompasses/contains the EVENT table's FROM_MEASURE and TO_MEASURE (including the vertices in between):

+---------+--------------+-------------+
| ROAD_ID | VERTEX_INDEX | MEASURE_VAL |
+---------+--------------+-------------+
|       1 |            3 |         2.8 |
|       1 |            4 |         4.0 |
|       1 |            5 |         4.3 |
|       1 |            6 |         6.7 |
+---------+--------------+-------------+
|       2 |            1 |           0 |
|       2 |            2 |         5.2 |
|       2 |            3 |         7.7 |
+---------+--------------+-------------+
|       3 |            1 |           0 |
|       3 |            2 |         2.4 |
|       3 |            3 |         4.9 |
+---------+--------------+-------------+

In other words, I want to select vertices from ROADS where the MEASURE_VAL is between the event FROM_MEASURE and TO_MEASURE, and if the MEASURE_VAL does not exactly equal the FROM_MEASURE or TO_MEASURE, then also include the preceding and/or following vertices (where applicable).

Said yet differently again, I want to select the portion of the line that contains the event.

How can I do this?

3
  • 1
    What do you want to happen if the measure_val is exactly equal to a from _measure (eg 2.8 and 2.8)? Should the previous value (1.2) be included in the result as well? Nov 20, 2016 at 14:16
  • That's a good point. No, when the measure_val is exactly equal to a from_measure (eg 2.8 and 2.8), the previous value (1.2) should not be included in the result. This would apply to the to_measue as well. I hadn't thought of that.
    – User1974
    Nov 20, 2016 at 14:30
  • I have changed the measure_val for road #1, vertex #3 from 2.7 to 2.8 to reflect this scenario. I will make note of this requirement in the question shortly.
    – User1974
    Nov 20, 2016 at 14:34

2 Answers 2

6

You could just simply get the previous and next measure values with LAG and LEAD, and filter based on them:

select road_id, vertex_index, measure_val from (
  select
    r.road_id, r.vertex_index, r.measure_val, e.from_measure, e.to_measure,
    lag(r.measure_val, 1, r.measure_val) over 
      (partition by e.road_id order by r.vertex_index) as prev_measure_val,
    lead(r.measure_val, 1, r.measure_val) over 
      (partition by e.road_id order by r.vertex_index) as next_measure_val
  from
    road r join event e on (r.road_id = e.road_id)
) where prev_measure_val <= to_measure and next_measure_val >= from_measure;
0
3

Another way, similar to @Balasz's answer but doing the calculation in reverse order. First calculating the "to" value in the road table and then joining to event.

This query will give us for every row of the road the next ("to") value:

select road_id, vertex_id, measure_val,
       lead(measure_val) over (partition by road_id 
                               order by measure_val) 
           as to_measure_val 
from road ;

+---------+--------------+-------------+----------------+
| ROAD_ID | VERTEX_INDEX | MEASURE_VAL | TO_MEASURE_VAL |
+---------+--------------+-------------+----------------+
|       1 |            1 |           0 |            1.2 |
|       1 |            2 |         1.2 |            2.8 |
|       1 |            3 |         2.8 |            4.0 |
|       1 |            4 |         4.0 |            4.3 |  
|       1 |            5 |         4.3 |            6.7 |
|       1 |            6 |         6.7 |            7.1 |
|       1 |            7 |         7.1 |           NULL | 
+---------+--------------+-------------+----------------+
|       2 |            1 |           0 |            5.2 |
|       2 |            2 |         5.2 |            7.7 |
|       2 |            3 |         7.7 |            9.6 |
|       2 |            4 |         9.6 |           11.8 |
|       2 |            5 |        11.8 |           NULL |
+---------+--------------+-------------+----------------+
|       3 |            1 |           0 |            2.4 |
|       3 |            2 |         2.4 |            4.9 |
|       3 |            3 |         4.9 |           NULL |
+---------+--------------+-------------+----------------+

Then we can join this to the event table so the ON condition basically checks whether the two "(from, to)" intervals overlap or not:

with r as 
  ( select road_id, vertex_id, measure_val,
           lead(measure_val) over (partition by road_id 
                                   order by measure_val) 
               as to_measure_val
    from road
  ) 
select
   r.road_id,
   r.vertex_id, 
   greatest(r.measure_val, e.from_measure) as from_val,
   case when r.to_measure_val is null 
            then e.to_measure 
            else least(r.to_measure_val, e.to_measure)
   end as to_val
from r
  join event e
    on  r.road_id = e.road_id
    and r.measure_val < e.to_measure  
    and (e.from_measure < r.to_measure_val or r.to_measure_val is null)
order by r.road_id, r.measure_val ;
0

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.