2

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?

1

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

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.