1

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 at 22:12
  • sorry dr_jts, I edited my question, maybe it is clearer now? – liap307 Aug 22 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 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 at 13:38
1

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

| improve this answer | |
  • 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 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 at 23:27
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(
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:

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)

| improve this answer | |

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.