Given the line between points 1 and 2, what is the change in angle to point 3?

Question

I have polylines in an Oracle 18c table.

  ASSET_ID VERTEX_NUM          X          Y ANGLE_CHANGE
---------- ---------- ---------- ---------- ------------
        10          1     118.56        3.8         null
        10          2     118.62       1.03         null
        10          3     121.93       1.03            ?

        20          1     123.59       1.19         null
        20          2     124.21       1.02         null
        20          3     124.85        .96            ?
        20          4     125.49       1.01            ?
        20          5     126.11       1.16            ?
        20          6      126.7       1.41            ?
        20          7     127.24       1.75            ?
        20          8     127.26       2.16            ? --I chose to put this point in the screenshot just because the change in angle is large. So it was easy to illustrate what I'm looking for (lots of room for markup).
        20          9     127.36       2.56            ?
        20         10     127.52       2.94            ?
        20         11     127.75       3.29            ?
        20         12     128.03       3.59            ?

        30          1     129.84       1.26         null
        30          2     133.26       2.88         null

Using SQL, I want to determine what the "change in angle" is from point to point.

---

Question:

How can I calculate the angles between points?

In other words, given a line between points 1 and 2, what is the change in angle to point 3?

db<>fiddle

Answer 1

This is what I came up with. Minimally tested.

select
    asset_id
from
    (
    select
        c.*,
        case
            when abs(angle  -  lag (angle,1) over (partition by asset_id, part_num order by asset_id, part_num, vertex_num)  )   between 8.5 and 11.5 then 'Y'
        end as densified_angle    
    from
        (
        select
            b.*,
            atan2(y-y_prev, x-x_prev)*180/3.14159 as angle
        from
            (
            select
                asset_id,
                part_num,
                vertex_num,
                x,
                y,
                lag (x,1) over (partition by asset_id, part_num order by asset_id, part_num, vertex_num) as x_prev,
                lag (y,1) over (partition by asset_id, part_num order by asset_id, part_num, vertex_num) as y_prev
            from
                infrastr.bc_test_vw a
            ) b    
        ) c  
    ) d    
match_recognize (
         order by asset_id, part_num, vertex_num
         all rows per match
         pattern(y{3,})
         define 
         y as (densified_angle='Y')
       ) mr
group by
    asset_id

---

The query has some extra logic in the outer query — that finds lines that have at least three vertices in a row — where the angle between vertices is between 8.5 and 11.5. That's a specific use case I was dealing with where I was trying to find lines that had been densified.

Related: Determine if line has true curves via SQL (SDE.ST_GEOMETRY in Oracle)

---

Inspired by answers in a similar post on superuser.com, but for Excel.