Creating new lines from existing lines based on points lying along the lines

Question

I am pretty new to PostGIS and I have a problem I just can't figure how to start. I have a table of lines (id, name, the_geom) and a table of points (id, name, the_geom). Each point is located on a line and a line might have one or more points etc. The points can be located anywhere along the line, not just at vertices.

How can I create new lines from each line using points along the line given just the lines and points tables. For example, line ABCDEFG is a single line with its unique name and unique id in the lines table. How can I create new lines AB, BC, CD, DE, EF, FG such that they all have the same name as line ABCDEFG but different id, and load their new geometry into a new table of lines using PostGIS? Note that A and G are the start and end of line ABCDEFG and not actual points in the points table.

Example 1:

---

This is not an answer, but an attempt to explain the question better, following Chris' comment.

@Chris, the links you gave are nice but quite different from what I want to achieve. I am not trying to get the individual segments of lines based on the vertices of the lines themselves. What I am trying to achieve is to make sublines from lines based on where points from a different table intersect the lines. I think my diagram is a bit confusing, I have uploaded something better.

Answer 1

First you find closest line for your points (ST_Distance) , then you can use ST_LineLocatePoint(line,point) to find 0-1 values for your point on line. Group them by closest lines and create new one.

Now if i understood question right you want just draw line in "correct" order.

someting like

SELECT ST_MakeLine(g.point) as newline from 
   ( select point ,ST_LineLocatePoint(l.lines, point) as dif -- this finds order of points 
   from XXX , (SELECT lines from Y where id =1 ) as l
   WHERE ST_Within(l.lines, point) < 2 -- find all point in 2 meters from line
   GROUP BY line -- create group by line 
   ORDER BY ST_LineLocatePoint(linestring, point) ASC -- this finds order of points 
) as g

(code probably does not work , but you get the idea )

http://postgis.net/docs/reference.html#Linear_Referencing

If you just want get subline from line between two points then use geometry ST_LineSubstring(geometry a_linestring, float startfraction, float endfraction); you need to st_linelocatepoint() to find those fractions first