I have a line geometry that partially overlaps itself (think about a line going A -> B -> C -> B -> D) and a set of points (not on the line but close to it) that I want to use to split the line in segments.

I tried to project the points on the line but all the solutions here involving ST_ClosestPoint and ST_Snap don't work, so I created new lines by "extending" the lines between the points and ClosestPoints (considering sin and cos for correct heading) and collected these lines to use them in ST_Split.

When I use ST_Split(line, geom_collection), the line is correctly cut into segments based on the points but only where it doesn't self-overlap; where it does self-overlap, it is splitted into segments at every single vertex, not only at the points of my geometry_collection.

Do you have any idea?


If you use ST_LineLocatePoint you will have the closest projection of your point on the line as a number between 0 and 1 corresponding to the distance on the line. You can use it to cut the line using ST_LineSubstring. For exemple, using points like your exemple:

with data as (
        ST_MakeLine(ARRAY[ST_MakePoint(0,0), ST_MakePoint(1,1), ST_MakePoint(2,2), ST_MakePoint(1,1), ST_MakePoint(2,1)]) as line,
        ST_MakePoint(1.5,2) as point
), nearest_frac as (
            ST_LineLocatePoint(line, point) as frac
        from data
        st_astext(ST_LineSubstring(line, 0, frac)) as start_line,
        st_astext(ST_LineSubstring(line, frac, 1)) as end_line
    from data, nearest_frac

Like this, there will be only one point used to cut your line when you have self-overlapping, and it possibly (I'm not sure) be the closest point to the start of the line (along the line) so you will only have 2 substrings.

I'm not sure if this is what you want, can you tell what is the result you expect in case of self-overlapping line ?

  • I will be more specific to better explain: my problem is about a public transport line, coming from a larger town through a main road, taking a side road going to a "dead end" village and going back (so it overlaps) to the main road and continuing to another town along the main road. I need to split the route to have the single segments between the stops. I will try your solution but I think it only works if the stop is at the exact end of the self-overlapping part and there are no other stops in that road.
    – Simgiov
    Jan 20 '20 at 8:39

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.