I am trying to calculate the angle of lines to display their direction in a visualisation. However, I'm stuck on how to compute it automatically as a database view by using an SQL select statement.
The calculation is based on several criteria:
Maybe something like this...
SQL> create table angle ( line varchar(10), direction integer, sequence float, y float, x float) ; SQL> select degrees(atan2( x - lag(x) over(order by sequence), y - lag(y) over(order by sequence) ))::integer from angle ;
To invert sequence order when direction is not "1":
SQL> select case when direction = 1 then degrees(atan2(x-lag(x) over(order by sequence), y-lag(y) over(order by sequence)))::integer else degrees(atan2(x-lag(x) over(order by sequence desc), y-lag(y) over(order by sequence desc)))::integer end from angle ;