Combining multiple PostGIS geometry collections into one

Question

I'm having an issue with combining multiple geometry collections. My sample data consists of 2 records in a trips table, each with a track. There could be many more records, tho.

Here's the first track:

SRID=4326;GEOMETRYCOLLECTION(
    MULTILINESTRING(
        (
            18.71961 43.27301 1517 1542872460,
            18.72108 43.27021 1468 1542873904,
            18.72025 43.27044 1457 1542874216,
            18.72065 43.26784 1415 1542875477,
            18.72196 43.26709 1398 1542876061,
            18.72201 43.26576 1383 1542876703,
            18.72119 43.26576 1378 1542876990,
            18.72308 43.26059 1452 1542879569,
            18.72279 43.25933 1475 1542880184,
            18.72008 43.25659 1533 1542881812
        ),(
            18.72011 43.25444 1548 1542882848,
            18.72266 43.2492 1538 1542885527,
            18.72816 43.24982 1550 1542887481,
            18.73495 43.25345 1781 1542890437,
            18.73683 43.26157 2175 1542894406,
            18.73888 43.2652 2148 1542896297,
            18.74799 43.26395 2024 1542899551,
            18.74816 43.25745 2022 1542902684
        )
    )
)

And here the second:

SRID=4326;GEOMETRYCOLLECTION(
    MULTILINESTRING(
        (
            18.76791 43.26094 1440 1542911533,
            18.77323 43.26245 1273 1542913536,
            18.77347 43.2652 1309 1542914864,
            18.77974 43.26583 1125 1542917086,
            18.77792 43.2689 1140 1542918697,
            18.77782 43.27117 1134 1542919792,
            18.78194 43.27238 1077 1542921351,
            18.78533 43.27164 1032 1542922592,
            18.78559 43.26976 977 1542923503,
            18.78761 43.26969 949 1542924213,
            18.78819 43.26914 935 1542924547,
            18.787 43.26848 927 1542925072,
            18.78757 43.26718 928 1542925729,
            18.79291 43.26829 892 1542927678
        )
    ),
    MULTILINESTRING(
        (
            18.79533 43.26709 891 1542928706,
            18.79908 43.26738 867 1542930029,
            18.80567 43.26617 778 1542932414
        )
    )
)

What I want is to simply combine these collections, run the result through ST_AsGeoJSON , so I then end up with something like this:

{
    "type": "GeometryCollection",
    "geometries": [
        {
            "type": "MultiLineString",
            "coordinates": [
                [
                    [
                        18.71961,
                        43.27301,
                        1517
                    ],
                    [
                        18.72108,
                        43.27021,
                        1468
                    ],
                    [
                        18.72025,
                        43.27044,
                        1457
                    ],
                    [
                        18.72065,
                        43.26784,
                        1415
                    ],
                    [
                        18.72196,
                        43.26709,
                        1398
                    ],
                    [
                        18.72201,
                        43.26576,
                        1383
                    ],
                    [
                        18.72119,
                        43.26576,
                        1378
                    ],
                    [
                        18.72308,
                        43.26059,
                        1452
                    ],
                    [
                        18.72279,
                        43.25933,
                        1475
                    ],
                    [
                        18.72008,
                        43.25659,
                        1533
                    ]
                ],
                [
                    [
                        18.72011,
                        43.25444,
                        1548
                    ],
                    [
                        18.72266,
                        43.2492,
                        1538
                    ],
                    [
                        18.72816,
                        43.24982,
                        1550
                    ],
                    [
                        18.73495,
                        43.25345,
                        1781
                    ],
                    [
                        18.73683,
                        43.26157,
                        2175
                    ],
                    [
                        18.73888,
                        43.2652,
                        2148
                    ],
                    [
                        18.74799,
                        43.26395,
                        2024
                    ],
                    [
                        18.74816,
                        43.25745,
                        2022
                    ]
                ]
            ]
        },
        {
            "type": "MultiLineString",
            "coordinates": [
                [
                    [
                        18.76791,
                        43.26094,
                        1440
                    ],
                    [
                        18.77323,
                        43.26245,
                        1273
                    ],
                    [
                        18.77347,
                        43.2652,
                        1309
                    ],
                    [
                        18.77974,
                        43.26583,
                        1125
                    ],
                    [
                        18.77792,
                        43.2689,
                        1140
                    ],
                    [
                        18.77782,
                        43.27117,
                        1134
                    ],
                    [
                        18.78194,
                        43.27238,
                        1077
                    ],
                    [
                        18.78533,
                        43.27164,
                        1032
                    ],
                    [
                        18.78559,
                        43.26976,
                        977
                    ],
                    [
                        18.78761,
                        43.26969,
                        949
                    ],
                    [
                        18.78819,
                        43.26914,
                        935
                    ],
                    [
                        18.787,
                        43.26848,
                        927
                    ],
                    [
                        18.78757,
                        43.26718,
                        928
                    ],
                    [
                        18.79291,
                        43.26829,
                        892
                    ]
                ]
            ]
        },
        {
            "type": "MultiLineString",
            "coordinates": [
                [
                    [
                        18.79533,
                        43.26709,
                        891
                    ],
                    [
                        18.79908,
                        43.26738,
                        867
                    ],
                    [
                        18.80567,
                        43.26617,
                        778
                    ]
                ]
            ]
        }
    ]
}

The closest I got so far was the following query:

select ST_AsGeoJSON(ST_Union(trips.track), 6) as json from trips

This yields a single MultiLineString with 4 lines in it, tho, so the data's all there, but not as a geometry collection.

Made some progress. The following query gives me a geometry collection, but for some reason it combines the last 2 MultiLineStrings into one, giving me 2 MultiLineStrings instead of the required 3:

select ST_AsGeoJSON(ST_Collect(ST_CollectionExtract(trips.track)), 6) from trips 

Answer 1

I ended up creating a custom function explode_geometry_collection that does some EWKT string manipulation and returns an array of multilinestrings. This query then does what I want:

select ST_asGeoJson(ST_Collect(geom), 6, 1) 
from (
    select unnest(explode_geometry_collection(track)) as geom from trips
) t;

Not particularly pretty and might not be performant, but it won't be used much, so it'll do for now.

Answer 2

In your initial attempts, you noted that ST_CollectionExtract(trips.track) flattened two MULTILINESTRING into one. That's exactly what its documentation says it will do: "returns a homogeneous multi-geometry". Once that has been done, you've lost the structure information needed to recreate the groupings of linestrings in the original data.

ST_Dump provides more information, returning each component of the feature collection along with a path value showing the relative location within the feature collection of each component.

SELECT (ST_Dump(track)).* from trips LIMIT 1;

Result:

 path  |                        geom                    
-------+--------------------------------------------------
 {1,1} | 01020000E0E61000 ... <snip> ... 000000085A0FDD641
(1 row)

In this case, the path value is an array encoding the hierarchical path from the top of the GEOMETRYCOLLECTION to the 1st line segment within the 1st MULTILINESTRING in that collection. Combining the path with a record ID from the original table (to distinguish paths that are only unique w.r.t. components in a single record) provides enough information to variously GROUP and ORDER the components when creating a single geometry collection to hold them all.

The following query will do this by dumping the track, unpacking the path arrays, creating a collection grouped by the record ID and top-level path value (to recreate the MULTILINESTRING groupings), and then collecting everything into a feature collection and transforming to GeoJSON.

WITH segs AS (
  SELECT id, (ST_Dump(track)).*
    FROM trips
), unwrap AS (
  SELECT
    id, segs.path[1] AS path, segs.path[2] AS seg, geom
    FROM segs
    ORDER BY id, segs.path[1], segs.path[2]
), tracks AS (
  SELECT
    id, path, ST_Collect(geom) as geom
    FROM unwrap
   GROUP BY id, path
)
SELECT ST_asGeoJson(ST_Collect(geom))
  FROM tracks;

Result:

{
    "type":"GeometryCollection",
    "geometries":[
        {
            "type":"MultiLineString",
            "coordinates":[
                [
                    [18.71961,43.27301,1517],
                    [18.72108,43.27021,1468],
                    [18.72025,43.27044,1457],
                    [18.72065,43.26784,1415],
                    [18.72196,43.26709,1398],
                    [18.72201,43.26576,1383],
                    [18.72119,43.26576,1378],
                    [18.72308,43.26059,1452],
                    [18.72279,43.25933,1475],
                    [18.72008,43.25659,1533]
                ],[
                    [18.72011,43.25444,1548],
                    [18.72266,43.2492,1538],
                    [18.72816,43.24982,1550],
                    [18.73495,43.25345,1781],
                    [18.73683,43.26157,2175],
                    [18.73888,43.2652,2148],
                    [18.74799,43.26395,2024],
                    [18.74816,43.25745,2022]
                ]
            ]
        },{
            "type":"MultiLineString",
            "coordinates":[
                [
                    [18.76791,43.26094,1440],
                    [18.77323,43.26245,1273],
                    [18.77347,43.2652,1309],
                    [18.77974,43.26583,1125],
                    [18.77792,43.2689,1140],
                    [18.77782,43.27117,1134],
                    [18.78194,43.27238,1077],
                    [18.78533,43.27164,1032],
                    [18.78559,43.26976,977],
                    [18.78761,43.26969,949],
                    [18.78819,43.26914,935],
                    [18.787,43.26848,927],
                    [18.78757,43.26718,928],
                    [18.79291,43.26829,892]
                ]
            ]
        },{
            "type":"MultiLineString",
            "coordinates":[
                [
                    [18.79533,43.26709,891],
                    [18.79908,43.26738,867],
                    [18.80567,43.26617,778]
                ]
            ]
        }
    ]
}