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.7 |
| 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.8 |
+----------+---------+--------------+------------+
I want to select the vertices from ROAD where the MEASURE_VAL encompasses/contains the event FROM_MEASURE and TO_MEASURE (including the vertices in between):
+---------+--------------+-------------+
| ROAD_ID | VERTEX_INDEX | MEASURE_VAL |
+---------+--------------+-------------+
| 1 | 3 | 2.7 |
| 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 |
+---------+--------------+-------------+
It's kind of like I want to select vertices from ROADS where the MEASURE_VAL is between the event FROM_MEASURE and TO_MEASURE, but also include the preceding and following vertices. In other words, I want to select the portion of the line (which is composed of vertices) that contains the event.
How can I do this?