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.

enter image description here

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.

enter image description here

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?

  • What is a "graph"? A single LineString? A MultiLineString?
    – dr_jts
    Aug 21, 2020 at 22:12
  • sorry dr_jts, I edited my question, maybe it is clearer now?
    – liap307
    Aug 22, 2020 at 1:21
  • 1
  • 2
    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, 2020 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, 2020 at 13:38

2 Answers 2


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):

enter image description here

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

CREATE OR REPLACE FUNCTION ST_SelfIntersectingLineRectifier(
    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

SELECT ST_SelfIntersectingLineRectifier(geom) geom FROM line

You can see the result in the picture below:

enter image description here

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)


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
        SELECT i FROM generate_series(1, ST_NumPoints( data.geom )-1) AS gs(i)
     ) AS s(i) ON true
   ORDER BY dist LIMIT 1
  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, 2020 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, 2020 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.