I was thinking about using
ST_OffsetCurve(), but maybe the problem is more complex than i thought ?
Or maybe there is a simpler solution i did not see ?
You will need:
1) A table with
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
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
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