# Calculating the value of a column based on consecutive values of rows in another column

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  |         |
+-----+-----+---------+
``````

Now I want to compute the bearing column(e.g. `b1`). From my research, bearing between two points is determined this way:

Then bearing B of (x1,y1) from (x0,y0) is calculated as:

``````B = arctan(b,a)          .......(1)
``````

where

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 `x0, y0`

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.

• Please add desured result for your fiddle, with all calculations for at least one record. how I can use values from next row for the current row LEAD/LAG. Recursive CTE, if nothing helps. Jan 21, 2020 at 19:08
• Theory is excess (interesting, but ewxcess nevertheless). Whereas the formula `bearing = some_expression(lon, lat, LAG(lon), LAG(lat))` would be very useful. Jan 21, 2020 at 19:12
• Anycase you need in something like dbfiddle.uk/… Jan 21, 2020 at 19:27
• in the fiddle I didn't create the bearing column. My objective is to alter the table adding bearing column from the computation, but no idea how to. Jan 21, 2020 at 19:32
• Ah, thank you so for your help. I have been on this for two days. That's why good to talk in research process. Jan 21, 2020 at 19:35

The main thing you need is an ordering of the rows, since rows are unordered per se. Once you have an ordering, you can use the window function `lag` to access the previous row in that ordering.
Assuming that your rows are ordered by `tstamp`, that would look like
``````SELECT lat,