How to split overlapping linestrings?

Question

I'm new to PostGIS and a bit lost...
I work with linestrings that sometimes overlap, for example:

I'd like to distinguish these linestrings by splitting them like Google Maps do:

I was thinking about using ST_Intersection() and ST_OffsetCurve(), but maybe the problem is more complex than i thought ?
Or maybe there is a simpler solution i did not see ?

Answer 1

You will need:

1) A table with LineString geometries:

CREATE TABLE lin (
  id serial PRIMARY KEY,
  geom geometry(LineString, 31370)
);

CREATE INDEX ON lin (id);
CREATE INDEX ON lin USING gist (geom);

2) A table with Point geometries where you want to split your overlapping lines. They can represent train/metro stations, intersections, bifurcations etc. (edit: if you do not have a table with intersection points, use the query at the end of this answer to generate them)

CREATE TABLE pt (
  id serial PRIMARY KEY,
  geom geometry(Point, 31370)
);

CREATE INDEX ON pt (id);
CREATE INDEX ON pt USING gist (geom);

For this example I've drawn some lines with overlapping sections and some points. The goal is to cut our lines in segments between the points, and then to count how many segments exists between pt_from and pt_to.

Here is the query:

WITH 
a AS
(
-- Calculate the linear position (loc) of points on the line
  SELECT
    lin.id lin_id,
    pt.id pt_id,
    ST_LineLocatePoint(lin.geom, pt.geom) loc
  FROM
    lin
  JOIN
    pt ON ST_DWithin(lin.geom, pt.geom, 100.0)
),
b AS
(
-- Calculate from/to segments on lines by retrieving the next segment 
-- on the line with the LEAD window function, ordered by location (loc)
  SELECT
    lin_id,
    pt_id pt_from,
    loc loc_from,
    LEAD(pt_id) OVER w_b pt_to,
    LEAD(loc) OVER w_b loc_to
  FROM
    a
  WINDOW w_b AS (PARTITION BY lin_id ORDER BY loc)
)
-- Use row_number to count the number of the overlapping lines between pt_from and pt_to
-- It can be used to parametrize the offset to be displayed
SELECT
  row_number() OVER() fid, -- generate unique fid
  pt_from,
  loc_from,
  pt_to,
  loc_to,
  lin_id,
  row_number() OVER w_c offset_nr, -- iterate on overlapping lines
  ST_LineSubstring(lin.geom, loc_from, loc_to)::geometry(LineString, 31370) geom  -- cut lines to make segments
FROM
  b
JOIN
  lin ON b.lin_id = lin.id
WHERE
  pt_to IS NOT NULL
WINDOW w_c AS (PARTITION BY pt_from, pt_to ORDER BY lin_id, loc_from)
;

You can load this query in QGIS via DB Manager or by creating a VIEW in PostgreSQL.

Then you simply need to use offset_nr as a data-defined offset in QGIS.

The result looks like that:

Please note that the lines may not always connect correctly at intersection points. In depends on the order used to calculate offset_nr.

Edit: to generate the points to be used for intersections, use a query like that:

WITH ix AS
(
  SELECT DISTINCT ST_Intersection(ST_SnapToGrid(a.geom, 50.0), ST_SnapToGrid(b.geom, 50.0)) geom 
  FROM lin a JOIN lin b ON ST_Intersects(a.geom,b.geom)
),
ix_simple_lines AS
(
  SELECT
    (ST_Dump(ST_LineMerge(ST_CollectionExtract(geom, 2)))).geom geom
  FROM
    ix
),
ix_points AS
(
  SELECT
    (ST_Dump(ST_CollectionExtract(geom, 1))).geom geom
  FROM
    ix
)
SELECT
  row_number() OVER() id,
  geom
FROM
(
  SELECT ST_StartPoint(geom) geom FROM ix_simple_lines
  UNION
  SELECT ST_EndPoint(geom) FROM ix_simple_lines
  UNION
  SELECT geom FROM ix_points
) points_union