# How to compute the distance from the beginning of a line for every points using postgis

I have a line, for which I need to know, for every point that compose it, what is the distance from the beginning. The data is stored in PostGis 2.0

For example, for the line with the point a, b, c, d :

a -> b -> c -> d

The distance I need for each point is :

• a should be 0,
• b should be 5m (distance between a and b)
• c should be 12m (b and c are separated by 7m, + 5m for a and b)
• d should be 18m (c and d are 6m apart, and 12m previously)

That distance need to be written as double in another table (where I then mix additional metadata on the point)

Right now I use

``````INSERT INTO myschema.points
(point_id, position, offset, distance)
SELECT
points.id as point_id
points.position as position,
points.offset as offset,
-- Very slow!
ST_Line_Locate_Point(line.full_path, points.position) * line.length AS distance
FROM points_temp AS points
INNER JOIN
temp_line AS line ON points.line_id = line.id
ORDER BY
points.offset;
``````

But it is terribly slow! It seem to recompute the length of the line for every points and that is very inefficient.

Points along the line are ordered by offset

What would be a better and (possibly) much faster way?

Another approach is to use a window function.

A window function performs a calculation across a set of table rows that are somehow related to the current row.

and more precisely the lag function.

[lag] returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null

For example if you have a (basic) table contains only id and the geom column:

``````CREATE SEQUENCE mseq;
CREATE TABLE FOO ( gid integer primary key default nextval('mseq'));
AddGeometryColum('public','foo','geom',2100,'POINT',2); -- SRID 2100, is a projected CRS
``````

you can calculate the distance for each node based on gid :

``````select
gid,
st_distance(geom,lag(geom,1) over (order by gid asc))  distance
from
foo;
``````

which it will result the distance for each node (start at (n-1), end at n):

``````gid |     distance
----+------------------
1 |
2 | 27746.1563439608
3 | 57361.8216245281
4 | 34563.3607734946
``````
• distance(1) is null
• distance(2) is ~28km, line starts at id(1), line ends at id(2)
• etc..

As I found out you cannot combine sum() and lag() in one query so the solution is to create a sub-querry :

``````SELECT id, sum(distance) OVER (ORDER BY gid) AS cum_dist
FROM  (
SELECT gid, st_distance(geom, lag(geom, 1) OVER (ORDER BY gid)) AS distance
FROM   foo
) sub
ORDER  BY gid;
``````

which will result to the desired result:

`````` gid  |     cum_dist
-------+------------------
1 |
2 | 252273.279036097
3 | 712169.420783514
4 | 1172185.90656572
5 | 1630478.82109417
6 | 2069029.98992036
7 | 2552840.89215418
``````

For a test table containing 10000 points, the query returned in 70ms in my maschine.

For you case, since your nodes are ordered based on offset column you can replace gid with offset and try it.

• It work, it's fast and it's very elegant and easy to maintain/expand. My boss is happy, I am happy : Thank you so much! – Laurent Bourgault-Roy Jul 25 '13 at 15:53