Jake - 1 year ago 78
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.

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.

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 ;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download