3

I have a collection of linestrings, which represent one-way streets. I linemerge these together when they have matching attributes, to get longer linestrings for better rendering. But I want to avoid reversing the direction of any of the linestrings during the join, since the direction of one-way streets is important.

In this first example, there are three streets which can be merged together, since they are all 'nose to tail':

select st_astext(st_linemerge(st_geomfromtext(
  'MULTILINESTRING((0 0, 1 1), (1 1, 2 2), (2 2, 3 3))'
)));
          st_astext          
-----------------------------
 LINESTRING(0 0,1 1,2 2,3 3)
(1 row)

This is fine. But in the second example, the middle linestring points in the opposite direction:

select st_astext(st_linemerge(st_geomfromtext(
  'MULTILINESTRING((0 0, 1 1), (2 2, 1 1), (2 2, 3 3))'
)));
          st_astext          
-----------------------------
 LINESTRING(0 0,1 1,2 2,3 3)

The direction of the middle linestring has been reversed during the merge, from (2 2 -> 1 1) to (1 1 -> 2 2).

Is there a method to merge linestrings in PostGIS that is similar to st_linemerge, but that avoids reversing the direction of linestrings?

4

As you have seen ST_LineMerge actually merges the geometries into a new one, so the reversing of the direction in the middle is inevitable (and if it didn't, you'd have some extreme zigs and zags as a new segment is inserted between the end on one section on the start of the next where the direction reverses). However, ST_Collect simply collects the geometries into a Multi or a Geometry collection (depending on whether their types match or not). In your case they are all linestrings so you will end up with a multilinestring as required with the geometries unchanged.

EDIT: The ST_DumpPoints method does return a single linestring as suggested by R.K.: LINESTRING(0 0,1 1,2 2,1 1,2 2,3 3) but if you trace this line it contains the "zig-zags" I mentioned above. They will muck up any distance or routing calculations etc because your linestring will have an extra segment between (1,1) and (2,2) and then go backwards to (1,1) before the second extra segment also from (1,1) to (2,2) again before finishing the line. In other words you are introducing extra road that doesn't exist. This line will be 5/3 (1.6667) times the length of the original! These extra segments may even muck up your rendering if you have direction arrows on the road because you will end up with direction arrows pointing in both directions where the extra segments overlap the original road in the middle.

0
1

This is a good application for a recursive CTE. The road lines can be thought of as forming a directed graph, with the graph edges indicating that a line endpoint is the same as the start point of another line. A recursive query can traverse the graph identifying groups of lines which are connected end-to-start. The initial non-recursive term (base case) for the recursive query is determined by all lines which do not have another line ending at their start. Once the lines are grouped into connected sets, each set can be line-merged.

Here's the query. A more complex example geometry than provided above is used to prove that it works:

WITH RECURSIVE
data AS (SELECT
'MULTILINESTRING( (0 0, 1 1), (1 1, 2 2), (3 3, 2 2), (4 4, 3 3), (4 4, 5 5), (5 5, 6 6) )'::geometry
 AS geom)
,lines AS (SELECT t.path[1] AS id, t.geom FROM data, LATERAL ST_Dump(data.geom) AS t)
,paths AS (
  SELECT * FROM
    (SELECT l1.id, l1.geom, l1.id AS startid, l2.id AS previd
      FROM lines AS l1 LEFT JOIN lines AS l2 ON ST_EndPoint(l2.geom) = ST_StartPoint(l1.geom)) AS t
    WHERE previd IS NULL
  UNION ALL
  SELECT l1.id, l1.geom, startid, p.id AS previd
    FROM paths p
    INNER JOIN lines l1 ON ST_EndPoint(p.geom) = ST_StartPoint(l1.geom)
)
SELECT ST_AsText( ST_LineMerge(ST_Collect(geom)) ) AS geom 
  FROM paths
  GROUP BY startid;

Here is the output:

          geom           
-------------------------
 LINESTRING(4 4,5 5,6 6)
 LINESTRING(4 4,3 3,2 2)
 LINESTRING(0 0,1 1,2 2)
3
  • This is great! Thank you, very much along the lines of what I was hoping for. I noticed a couple of problems with it though by adding a single extra linestring (1 1, 5 5) to the end (so MULTILINESTRING( (0 0, 1 1), (1 1, 2 2), (3 3, 2 2), (4 4, 3 3), (4 4, 5 5), (5 5, 6 6), (1 1, 5 5) ) in total) gives an output of LINESTRING(4 4,5 5,6 6) LINESTRING(4 4,3 3,2 2) MULTILINESTRING((0 0,1 1),(1 1,2 2),(1 1,5 5,6 6)) The segement (5 5, 6 6) now appears twice, despite only being in the input once, and on the final output line it has (0 0, 1 1) and (1 1, 2 2) unjoined. Mar 18 '20 at 9:40
  • Well yes, this solution only works for sets of lines which do not overlap. You stated that your data was a set of streets, which I assumed had this characteristic (as did the example you provided). If your data is more complex you should describe this in your question, and provide some representative data.
    – dr_jts
    Mar 18 '20 at 16:33
  • The data is from OpenStreetMap, so although it's meant to be a set of streets, you never know what kind of edge cases someone manages to map - either deliberately, or by mistake! So no worries, and thanks again. Mar 19 '20 at 17:18
0

It's possible but convoluted. An example solution would be by dumping the individual points of the Linestring and combining them into a new, single line with ST_MakeLine:

SELECT
ST_AsText(
    ST_MakeLine(
        ARRAY(
            SELECT (
                ST_DumpPoints('MULTILINESTRING((0 0, 1 1), (2 2, 1 1), (2 2, 3 3))')
            ).geom
        )
    )
);

This returns:

LINESTRING(0 0,1 1,2 2,1 1,2 2,3 3)
2
  • 1
    While this looks like an appealing solution, it will result in the extra zig-zag line segments I mentioned in my answer. Mar 11 '20 at 9:47
  • @MappaGnosis True, of course. Not having a zig-zag and forming a single linestring would not be possible, as a linestring is a connected path by default. The solution here is a non-simple linestring (i.e. a linestring that crosses itself), but working with a Collection, a MultiLinestring(which would be the sourcedata) or a topology as highlighted in your answer would likely be more practical.
    – R.K.
    Mar 11 '20 at 10:18

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.