Overlapping line aggregation+trim in PostGIS, while summing values

Question

I'm sorry for the vague title, I didn't really know how to name this. I am working on a project that visualizes and simulates 17th century trade. For this project we also use so called Minard lines to show how certain goods are distributed. To clarify I made this screenshot:

However, as you can see every route is a seperate line (Amsterdam <> Jakarta and Rotterdam <> Jakarta for instance) and they all overlap with each other. This does give a nice Tron effect, but we actually want to merge them. We want to merge the lines that overlap (while adding their values), and segmentize those that don't (while remaining their values).

I've been thinking a lot about this on how to do this in postgres and postgis, but I'm having real troubles with it. Functions like ST_union aggregates everything, Could anyone point me in the right direction? Here's a screenshot of the database:

Answer 1

I'm actually working on a very similar problem, here is my approach below

Lines first need to be split where they overlap to then be aggregated into segments that are overlapping.

In short we want to separate each user-drawn line into the following three different types of segments:

1. Segments which overlap other lines.
2. Segments which do not overlap lines on lines which do at some point overlap
3. Lines that do not overlap other lines.

1. Overlapping segments

This query takes the intersection between any line a and line b which overlap, where a & b are not the same line. Note that since we care about all submitted content, and not just the shapes, we want the intersection of a & b AND the intersection of b & a, which is why the WHERE clause has the not equals <> operator rather than the less than <, which would halve the number of comparisons to make.

SELECT a.id, ST_INTERSECTION(a.geom, b.geom) as segment
from your_data a
INNER JOIN your_data b ON ST_OVERLAPS(a.geom,b.geom)
WHERE a.id <> b.id

A note that ST_Intersection produces MultiLineStrings (multiple lines) and GeometryCollections (a collection of points and lines), more on this later.

2. Disjointed segments of overlapping lines

For segments that do not overlap other lines but are parts of lines that do overlap, we can use the ST_Difference operation.

SELECT a.id, ST_Difference(a.geom, b.geom) AS segment
from your_data a
INNER JOIN your_data b ON ST_OVERLAPS(a.geom,b.geom)
WHERE a.id <> b.id

3. Fully disjointed lines

From this answer, we can use a LEFT OUTER JOIN to see which lines do not overlap with any other.

SELECT a.id, a.geom AS segment
FROM your_data a
LEFT OUTER JOIN your_data b ON ST_OVERLAPS(a.geom,b.geom)
WHERE b.id IS NULL

Aggregating

Following these three separate operations, we have three different types of geometries:

• GeometryCollections: a collection of points and lines
• MultiLineStrings: multiple lines
• LineStrings: single, contiguous lines

Given that the input are lines, we are not interested in intersection points between them. Further, most GIS software has some difficulty in displaying mixed geometries. We can select only LineStrings from the GeometryCollection with ST_CollectionExtract(geom, 2).

Next we disaggregate the MultiLineStrings in order to group together identical segments using ST_Dump before aggregating twice: 1. By identical segments, using GROUP BY geom while aggregating the drawn segment ids with array_agg(id ORDER BY id) 2. By merging together lines which share the same id[] array.

Putting it all together:

WITH segments as(
    SELECT id
    ,CASE   WHEN geometrytype(segment) = 'MULTILINESTRING' THEN ST_LineMerge(segment)
        WHEN geometrytype(segment) = 'GEOMETRYCOLLECTION' THEN ST_LineMerge(ST_CollectionExtract(segment, 2))
        else segment
    END AS segment
    FROM(
    SELECT a.id, ST_INTERSECTION(a.geom, b.geom) as segment
    from your_data a
    INNER JOIN your_data b ON ST_OVERLAPS(a.geom,b.geom)
    WHERE a.id <> b.id
    ) overlapping
    UNION 
    SELECT id
    ,CASE   WHEN geometrytype(segment) = 'MULTILINESTRING' THEN ST_LineMerge(segment)
        WHEN geometrytype(segment) = 'GEOMETRYCOLLECTION' THEN ST_LineMerge(ST_CollectionExtract(segment, 2))
        ELSE segment
    END AS segment
    FROM(
    SELECT a.id, ST_Difference(a.geom, b.geom) AS segment
    from your_data a
    INNER JOIN your_data b ON ST_OVERLAPS(a.geom,b.geom)
    WHERE a.id <> b.id
    ) disjointed
    UNION 
    SELECT a.id, a.geom AS segment
    FROM your_data a
    LEFT OUTER JOIN your_data b ON ST_OVERLAPS(a.geom,b.geom)
    WHERE b.id IS NULL
)
, dump AS(
    SELECT id
    ,CASE   WHEN geometrytype(segment) = 'MULTILINESTRING' THEN(ST_DUMP(segment)).geom
        WHEN geometrytype(segment) = 'GEOMETRYCOLLECTION' THEN ST_LineMerge(ST_CollectionExtract(segment, 2))
        ELSE segment
    END AS segment
    FROM segments 
)
, agg1 AS(
    SELECT array_agg(DISTINCT id ORDER BY id) as ids, segment
    FROM dump
    GROUP BY segment
)

SELECT ids, ST_LineMerge(ST_Multi(ST_Collect(segment))) as line, array_length(ids, 1) as cnt
FROM agg1
GROUP BY ids