Removing longest segment of linestring and rejoining segments

Question

I am trying to remove the segment that has the longest length from a linestring and join the two group of segments that remain by the shortest segment between them.

That was easy, using ST_Difference (ST_Split works too) between the linestring and its longest segment, and then I used ST_ShorestLine to join both resulting group of segments.

But I found the biggest problem when the major segment crosses many segments of the linestring. the result of ST_Difference or ST_Split returns more than two groups of segments (because point-type intersections are also taken into account).

This makes the subsequent union of the groups difficult.

What could help me, I think, is that the ST_Difference or ST_Split (or another function, IDK) only takes into account the intersections of the linestring type but I cannot create the correct query.

Or maybe there is an easier way to remove the longest segment and get the two adjacent linestring to rejoin?

Any ideas?

sorry dr_jts, I edited my question, maybe it is clearer now? — liap307, Aug 22 '20 at 01:21
https://gis.stackexchange.com/questions/174472/in-postgis-how-to-split-linestrings-into-their-individual-segments — BERA, Aug 22 '20 at 13:19
ST_ShortestLine may return a line which does not end on a vertex. In this case the linestrings cannot be rejoined by that line. In fact,, they can only be rejoined by a line segment between a pair of their endpoint. Is this what you want? — dr_jts, Aug 22 '20 at 21:40
yes, dr_jts. It's that what I want. But if I have the two geometries resulting from removing the longest segment, I can use ST_ShortestLine because it automatically calculates the endpoints for the new segment, right? In other words, the new segment to rejoin does not necessarily have to have the endpoints of the removed segment. I explain? — liap307, Aug 24 '20 at 13:38
Based on your problematic drawing, it turns out 1) you need to remove a long segment and 2) connect the broken line at the nodes of the line, is that correct? because ShortestLine() will join the line itself and will need to bite off the remaining segment, what is doubtful? — Cyril Mikhalchenko, Aug 24 '20 at 14:21

Cyril Mikhalchenko · Answer 1 · 2020-08-26T19:10:25.953

I publish my original and fun solution on Postgre/PostGIS SQL for those who will encounter a situation where the lines have self-intersections,

So, the initial situation is shown in the figure, the initial table named line (LineString EPSG: 4326):

Create a new original function and launch a request to execute it:

CREATE OR REPLACE FUNCTION ST_SelfIntersectingLineRectifier(
geom GEOMETRY
)
RETURNS GEOMETRY AS
$BODY$
WITH
    tbla AS (SELECT (ST_Dump(geom)).geom geom),
    tblb AS (SELECT ST_MakeLine(pt1, pt2) geom FROM (SELECT ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) pt1,
      ST_PointN(geom, generate_series(2, ST_NPoints(geom))) pt2 FROM tbla) AS geom),
    tblc AS (SELECT DISTINCT ST_Union(a.geom) geom FROM tblb a, tblb b WHERE ST_Length(a.geom)<ST_Length(b.geom)),
    tbld AS (SELECT DISTINCT (ST_Dump(ST_Difference(a.geom, b.geom))).geom geom FROM tblb a JOIN tblc b ON ST_Intersects(a.geom, b.geom)),
    tble AS (SELECT ST_MakeLine(ST_LineSubstring(geom, 0.1, 0.9)) geom FROM tbld),
    tblf AS (SELECT (a.geom) geom FROM tblb a JOIN tble b ON ST_Intersects(a.geom, b.geom)), 
    tblg AS (SELECT (a.geom) geom FROM tblf a WHERE NOT EXISTS (SELECT 1 FROM tbld b WHERE ST_Intersects(a.geom, ST_StartPoint(b.geom)))),
    tblh AS (SELECT (a.geom) geom FROM tblb a JOIN tbld b ON ST_Touches(a.geom, b.geom) LIMIT 1),
    tbli AS (SELECT ST_MakeLine(ST_EndPoint(a.geom), ST_EndPoint(b.geom)) geom FROM tblg a, tblh b)
    SELECT ST_Union(geom) geom FROM (SELECT * FROM tblc UNION SELECT * FROM tbli) foo
$BODY$
LANGUAGE SQL
SELECT ST_SelfIntersectingLineRectifier(geom) geom FROM line

You can see the result in the picture below:

The script is called ST_SelfIntersectingLineRectifier

It's actually really hard to solve this problem analytically, but it's quite easy to solve it geometrically...

Remember, it's always important to define a nodal point or line that will help you find a solution...

"At first I run to your meeting, but then you force me to run from you..."

Translated with www.DeepL.com/Translator (free version)

dr_jts · Accepted Answer · 2020-08-24T04:27:43.540

Here is the first part of a solution - SQL to find the parts of the line either side of the longest segment.

WITH data(id, geom) AS (VALUES
    ( 1, 'LINESTRING (0 0, 1 1, 2.1 2, 3 3, 4 4)'::geometry )
),
longest AS (SELECT i AS iLongest, geom,
    ST_Distance(  ST_PointN( data.geom, s.i ),
                  ST_PointN( data.geom, s.i+1 ) ) AS dist
   FROM data JOIN LATERAL (
        SELECT i FROM generate_series(1, ST_NumPoints( data.geom )-1) AS gs(i)
     ) AS s(i) ON true
   ORDER BY dist LIMIT 1
)
SELECT 
  CASE WHEN iLongest > 2 THEN ST_AsText( ST_MakeLine(
    (ARRAY( SELECT (ST_DumpPoints(geom)).geom FROM longest))[1 : iLongest - 1]
  )) ELSE null END AS line1,
  CASE WHEN iLongest < ST_NumPoints(geom) - 1 THEN ST_AsText( ST_MakeLine(
    (ARRAY( SELECT (ST_DumpPoints(geom)).geom FROM longest))[iLongest + 1: ST_NumPoints(geom)]
  )) ELSE null END AS line2
FROM longest;

Useful patterns in this code:

JOIN LATERAL generate_series to extract the line segments
array slicing to extract a subline containing a section of the original line

Since the strategy for joining the (potential) two lines is unclear, that is not implemented.

It would be clearer if parts of this SQL were provided by functions (e.g. an ST_LineSlice function, and a ST_DumpSegments function - which perhaps one day will be part of PostGIS).

Thank you dr_jts! this solution was very helpful. I just add a DESC in the ORDER BY dist LIMIT 1. Then the union of the two resulting linestring would be: select ST_Union (array [ line1, ST_shortestLIne (line1, line2), line2] ) Now I only have to contemplate the case in which the longest segment is the first or the last one, since with this solution I do not get the correct result. Thank you! — liap307, Aug 26 '20 at 13:35
Glad that helped. If the longest segment starts or ends the line perhaps you can use a CASE statement to provide the behaviour you want. I — dr_jts, Aug 26 '20 at 23:27

Removing longest segment of linestring and rejoining segments

2 Answers2