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

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.

• 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. Thanks Dec 2, 2014 at 7:24
• The second image does better explain your goal. As John mentioned, you should use the edit button at the lower left of the question to provide additional information or revisions rather than posting as an answer. ArcGIS has a tool for this called Split Line at Points, but I'm afraid I'm not familiar enough with PostGIS to offer a solution. Maybe this one: gis.stackexchange.com/questions/112282 Dec 2, 2014 at 18:35

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

• Thanks a million guys, I have solved this problem using your wonderful suggestions. The link Chris gave actually related to the exact problem I was trying to solve. I must apologize that I had to rephrase my question using the "Add Answer" button because I needed to add an image which I could not do through the "Add comment" button. Once again thanks, I really appreciate! Dec 3, 2014 at 23:40
• @qgisgirl As stated in my other comment on the question, you can edit your original question to update or replace information. You might want to delete the 'not an answer' using the link at the lower left of it. Dec 4, 2014 at 21:22