PostGIS: How to dissolve vector data

Question

I have a few scenarios where I need to dissolve vector data, and I'm wondering how to do this using PostGIS. What would the queries look like to perform these operations?

Below I'll describe the different cases through some simple examples.

The actual data I'm working with is considerably more massive and complex than these examples, in reality I'm working with data from OpenStreetMap (road networks, landuse polygons et.c.). PostGIS seems to be the foremost alternative for handling heavy data.

Segmented lines

Lines are split up into segments similarly to what is shown in the two images. The first line ("A-D") consists of three segments and has been split at B and C. I want to dissolve these three individual lines to get a line consisting of the vertices A,D. (vertices B,C are entirely redundant)

The second line isn't very different. It has been split at E and F into three smaller lines.

Dissolve polygons

The second issue concerns dissolving polygon data. The geometries can either overlap or be within each other, touch (shared vertices) or nearly touch with some tiny distance separating the polygons.

For all of these cases I want to dissolve the polygons into larger geometries. I guess the last case where the polygons nearly touch is a bit special as the small space in between should be "filled out".

Final note

In the above cases the dissolving is solely based on spatial relationships. Non-geometric attributes aren't relevant.

As stated earlier the actual data I'm working with is pretty massive, and the base queries will likely have to be tweaked and optimized for handling very complex geometries.

If anyone would like to get the data shown in the examples it can be downloaded here as GeoJSON.

Answer 1

If all your overlaps/touchings were in pairs, it would be a simple self-join, which would be ideal for efficiency.

The brute force answer is just something like:

CREATE TABLE merged AS
SELECT (ST_Dump(ST_Union(geom)).geom AS geom
FROM mytable;

That'll take all the geometries into memory, throw them into a topology and extract the largest merged objects as a single geometry collection, then dump each element of the collection out into its own row.

For sufficiently large inputs, it could be terribly slow.

The trouble is, to do a more subtle merge operation you have to know a priori which groups of objects to merge. There's an ST_ClusterIntersecting function in PostGIS 2.2 what gets you some of the way there, but it's still an in-memory algorithm, so it might not be ideal.