2

I am working on PostGIS 2.5.3 and PostgreSQL 11.2

As seen in the example below, I have 3 LineString that I would like to Merge to form one single Linestring. The squares represent the vertices, so we see there isn't any vertex at the intersection of the green and the blue LineString. Currently the only option I found is to use ST_Union(), which produce a MultiLineString since it simply merges the geometries without any special consideration.

Do you know a way to have the proper result, here a single LineString which crosses itself (as I would draw if I were to trace it manually) ?

Example

EDIT : Improving from obchardon's answer, here is an example to work with :

SELECT ST_AsText(ST_LineMerge(ST_Union(geom))) 
FROM 
-- Three dummy LineString for the example
(SELECT (ST_Dump(ST_GeomFromText('MULTILINESTRING((0 0,3 3),(3 3,1 3), (1 3, 3 2))'))).geom) AS T
4
  • 1
    ST_LineMerge is what you're looking for.
    – obchardon
    May 6, 2020 at 16:03
  • @obchardon ST_LineMerge by itself just returns 3 LineString, and not the merged result. In order to get the merged result I would need to use a GROUP BY and I have to use it on the geometry column since ST_LineMerge is not an aggregating function. Can you go a bit further in your proposition? May 7, 2020 at 6:15
  • You can use ST_Collect (which is an aggregate function) to collect the records into a single MultiLineString to process with ST_LineMerge.
    – dr_jts
    May 7, 2020 at 17:29
  • @dr_jts That indeed seems to work! Thank you. Can you make it as an answer so I can accept it? May 7, 2020 at 17:45

2 Answers 2

1

You can use the ST_Collect aggregate function to collect lines from multiple rows into a single MultiLineString so that they can be merged with ST_LineMerge:

WITH data(geom) AS (VALUES
( 'LINESTRING (50 50, 150 100, 250 75)'::geometry )
,( 'LINESTRING (250 75, 200 0, 130 30, 100 150)'::geometry )
,( 'LINESTRING (100 150, 130 170, 220 190, 290 190)'::geometry )
)
SELECT ST_AsText(ST_LineMerge(ST_Collect(geom))) AS line 
FROM data;
1
  • Perfect! That does the trick. Thanks May 12, 2020 at 8:12
1

You can use ST_LineMerge:

-- Use ST_LineMerge on a MultiLineString not on several LineString, so we use ST_Union
SELECT ST_AsText(ST_LineMerge(ST_Union(geom))) 
FROM 
-- Two dummy LineString for the example
(SELECT (ST_Dump(ST_GeomFromText('MULTILINESTRING((0 1,0 2),(0 3,0 2))'))).geom) AS T

The above query output:

"LINESTRING(0 1,0 2,0 3)"

As expected.

1
  • It works fine when the original linestrings do not cross each other. From what I understand the core of my problem here is that the ST_Union creates a new vertex at the crossing point while dissolving the preexisting geometries, and the result is a MultiLineString the type ST_LineMerge doesn't simplify. May 7, 2020 at 13:50

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.