Select encompassing rows

Question

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?

Answer 1

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;

Answer 2

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 ;