3

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: line with points lying along


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

enter image description here

@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 – qgisgirl Dec 2 '14 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 – Chris W Dec 2 '14 at 18:35
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

  • 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! – qgisgirl Dec 3 '14 at 23:40
  • 1
    @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. – Chris W Dec 4 '14 at 21:22

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.