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;
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