Splitting self-overlapping lines with points using PostGIS

Question

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?

robin loche · Answer

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 (
    select
        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 (
    select
            ST_LineLocatePoint(line, point) as frac
        from data
)
select 
        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 ?

Splitting self-overlapping lines with points using PostGIS

One Answer

Add your own answers!

Ask a Question