6

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

I'd like to distinguish these linestrings by splitting them like Google Maps do: enter image description here

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 ?

  • Do the features always overlap or are they sometimes very close, yet do not intersect anywhere? If so, you might have to consider using ST_Dwithin() at some point. OffsetCurve might be a good ideafor thisbut be carefull to use it only on the parts you're interested in every linestring. – Moreau Colin Jun 7 '16 at 10:05
  • Indeed they can be very close and most of the time do not intersect at all, just like on a subway map. I'll try to use ST_DWithin(), but the problem is that the linestring is often overlapping itself so I probably can't do something like ST_DWithin(alinestring, alinestring) since it will always return true I guess. – Kef Jun 7 '16 at 10:34
  • Unless you had more conditions on top of it, that'll help you resolve the hardest part of your issue: Selecting the parts of the linestring whom are overlapping, or so close you can't tell them appart. You'll either need to cut further your lines before runing your Query or usest_intersection() or st_difference() – Moreau Colin Jun 7 '16 at 11:52
  • 2
    To show lines side by side, they are displayed them with an offset (in pixels). In QGIS, you must add a data-based offset. But how do you know how many lines are in parallel? In PostGIS, you can work with points along the line (subway stations for example) and segment the lines between those points. Each segment has a station_from and a station_to. Then you group the lines by (station_from, station_to) and you count how many lines there are. You can also use window functions like SUM() OVER() to give each line an offset. Then you apply this offset in QGIS. – thibautg Jun 7 '16 at 12:01
7

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.

Lines and points

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.

Set data-defined offset

The result looks like that:

Overlapping lines with offset

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
  • Thank you for this very detailed answer, it helps me a lot ! I will try to implement this. – Kef Jun 13 '16 at 7:59
  • @Kef did it work? – thibautg Jun 13 '16 at 21:47
  • Yes, I used postgis topology to split my linestrings into multiple segments instead of generating points along the lines. It's working perfectly, thanks again ! – Kef Jun 14 '16 at 10:35

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.