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?