0

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: enter image description here

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.

  • 1
    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. – Akina Jan 21 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. – Akina Jan 21 at 19:12
  • Anycase you need in something like dbfiddle.uk/… – Akina Jan 21 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. – arilwan Jan 21 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. – arilwan Jan 21 at 19:35
0

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,
       lon,
       lag(lat) OVER (ORDER BY tstamp) AS prev_lat,
       lag(lon) OVER (ORDER BY tstamp) AS prev_lon
FROM atable;

Applying the formula of your choice is left as an exercise for the reader.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.