I am using Postgis to:

- calculate the cumulative distance in metres along the line
- store the cumulative distance in "m"

I have a table with points on this line and each row holds lat and lon coordinates. The "m" value is 0 for all rows.

The following code nicely gets me the distance between the first point and the next.

`SELECT a.geom FLOOR( ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521)) AS dist FROM line a LEFT JOIN line b ON a.id = b.id + 1 `

however, it does not add up to a total nor does it update the "m" value yet.

I tried a set of permutations of the following, but none of them store a cumulative sum of the previous row’s value for "m" plus the calculation of the distance between this point and the previous.

`WITH next AS ( SELECT ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521) AS dist, a.id AS rowid, FROM line a JOIN line b ON a.id = b.id + 1 ) UPDATE line a SET m = FLOOR(next.m + next.dist) FROM next WHERE a.id = next.rowid RETURNING a.m, next.dist; `

or

`update line a set m = FLOOR(a.m + prev.dist) from (select l.*, ST_Distance(ST_Transform( lag(geom) over (order by l.id asc) , 3857), ST_Transform(geom, 3857)) * cosd(42.3521) as dist from line l ) AS prev where prev.id = a.id - 1 returning prev.m, prev.dist, a.m `

Any ideas?