cumulatively calculate the distance along a line

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 = + 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, AS rowid,     FROM line a     JOIN line b     ON = + 1 ) UPDATE line a SET m = FLOOR(next.m + next.dist) FROM next WHERE = next.rowid RETURNING a.m, next.dist; 


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

Any ideas?