Jake Jake - 2 months ago 8
SQL Question

SQL: calculate a field based on a sequence

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:


  1. Angle formula is INT(DEGREES(ATAN2(x2-x1, y2-y1))), where x2 = x of same row, and x1 = x of previous row according to the sequence field. The first of the sequence will be NULL as there is no x,y before it.

  2. Calculation is done within each unique "Line" value

  3. when Direction = 1, the sequence starts from the smallest to largest value. However, when Direction = 2 the sequence starts from the largest to the smallest.



enter image description here

Here is the sample data:

Line,Direction,Sequence,Y,X
Line 1,1,1,1.37707996,103.9108531
Line 1,1,1.2,1.37765002,103.9118526
Line 1,1,4.5,1.37849998,103.912852
Line 1,1,6.2,1.37914002,103.9138515
Line 1,1,8,1.37959003,103.9148509
Line 1,1,11.1,1.37978005,103.9148509
Line 1,1,13.1,1.38031006,103.9158504
Line 2,2,1.1,1.29593003,103.8761495
Line 2,2,2.4,1.29744005,103.8771489
Line 2,2,5.5,1.29849005,103.8771489
Line 2,2,8.1,1.29954004,103.8781484
Line 2,2,10,1.30094004,103.8781484
Line 2,2,11.1,1.30155003,103.8781484
Line 2,2,13.1,1.30234003,103.8781484
Line 2,2,15.1,1.30338001,103.8781484
Line 2,2,16.6,1.30410004,103.8781484


Any pointers? Thank you.

Answer

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 ;

EDIT

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 ;