More

Overlapping line aggregation+trim in PostGIS, while summing values


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:


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 lineaand linebwhich overlap, wherea&bare not the same line. Note that since we care about all submitted content, and not just the shapes, we want the intersection ofa&bAND the intersection ofb&a, which is why theWHEREclause 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 thatST_IntersectionproducesMultiLineStrings(multiple lines) andGeometryCollections(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 aLEFT OUTER JOINto 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 onlyLineStrings from theGeometryCollectionwith ST_CollectionExtract(geom, 2).

Next we disaggregate theMultiLineStringsin order to group together identical segments using ST_Dump before aggregating twice: 1. By identical segments, usingGROUP BY geomwhile aggregating the drawn segmentids witharray_agg(id ORDER BY id)2. By merging together lines which share the sameid[]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