Finding and merging lines that touch in PostGIS?

Question

I would like to select and merge segments that touch like in the image below.

My table contains single segments with their own id (shown in the figure), I just want to isolate single "group" of segments that creates individual lines with their own new ID. I used different colors to explain the different groups of segments I want to find.

I have no idea how to do that, cause I don't know the length of the lines.

Answer 1

You could avoid the need for a recursive query by taking the following approach:

• From each input LineString, extract the endpoints into a two-point MultiPoint
• Run ST_ClusterWithin on the extracted endpoints
• Spatially join the endpoint clusters to the original geometries.

Here's an example:

CREATE TABLE test_lines (id serial, geom geometry);

INSERT INTO
    test_lines (geom)
VALUES
    ('LINESTRING (0 0, 1 1)'),
    ('LINESTRING (2 2, 1 1)'),
    ('LINESTRING (7 3, 0 0)'),
    ('LINESTRING (2 4, 2 3)'),
    ('LINESTRING (3 8, 1 5)'),
    ('LINESTRING (1 5, 2 5)'),
    ('LINESTRING (7 3, 0 7)');

WITH endpoints AS (
    SELECT
        ST_Collect(ST_StartPoint(geom), ST_EndPoint(geom)) AS geom
    FROM
        test_lines
),
clusters AS (
    SELECT
        unnest(ST_ClusterWithin(geom, 1e -8)) AS geom
    FROM
        endpoints
),
clusters_with_ids AS (
    SELECT
        row_number() OVER () AS cid,
        ST_CollectionHomogenize(geom) AS geom
    FROM
        clusters
)
SELECT
    ST_Collect(test_lines.geom) AS geom
FROM
    test_lines
    LEFT JOIN clusters_with_ids 
    ON ST_Intersects(test_lines.geom, clusters_with_ids.geom)
GROUP BY
    cid;

Resulting in the following three MultiLineStrings:

Answer 2

Another option: use the PostGIS ST_LineMerge function. Here's the previous example redone with it:

WITH data(geom) AS (
    VALUES
        ('LINESTRING (0 0, 1 1)'),
        ('LINESTRING (2 2, 1 1)'),
        ('LINESTRING (7 3, 0 0)'),
        ('LINESTRING (2 4, 2 3)'),
        ('LINESTRING (3 8, 1 5)'),
        ('LINESTRING (1 5, 2 5)'),
        ('LINESTRING (7 3, 0 7)')
),
merged AS (
    SELECT
        (ST_Dump(ST_LineMerge(ST_Collect(geom)))).geom
    FROM
        data
)
SELECT
    row_number() OVER () AS cid,
    geom
FROM
    merged;