It is straight forward to compute column value based on values of other columns, e.g.
speed/time AS acceleration where speed and time are columns. Is there a way to compute column's value based on consecutive
rows values in other columns?
My database consists of points location coordinates (consecutive points), and for each point, I want to determine that point's bearing change from reference (true north). Here's more detail about the question:
TABLE: +-----+-----+---------+ | lat | lon | bearing | +-----+-----+---------+ | x0 | y0 | b1 | | x1 | y1 | | | x2 | y2 | | +-----+-----+---------+
Then bearing B of (x1,y1) from (x0,y0) is calculated as:
B = arctan(b,a) .......(1)
b = sin[y1 - y0] * cosx1 .....(2)
a = cosx0* sinx1 - sinx0 * cosx1 * [y1 - y0] ......(3)
Thus, to compute the bearing of
row1 in the above table (
b1), both quantities a and b in equations 2-3 require some value from
row2 in the table i.e.
x1,y1, and addition to
This is the computation I would like to do on the database, I have the latitude and longitude columns for the consecutive geolocations stored. I created this fiddle so need to alter the table by adding a 5th column for
bearing, but I am not sure how I can use values from next
row for the current
row in the bearing column.