3

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.

figure

1
  • Tipp: recursive queries (WITH RECURSIVE) can help, join the table to itself by ST_Touches.
    – Zoltan
    Jan 17, 2016 at 21:05

2 Answers 2

8

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: enter image description here

3
  • Hello, thank you for your help. I'm trying to run your query but the consolle shows me this error: "function st_clusterwithin(geometry, numeric) does not exist". I've substituted 1e-8 with float8(1e-8) or other numeric types but no positive results, how can I do? Jan 24, 2016 at 14:11
  • Oh, I just found that I'm running postgis 2.1.4 and this function is in 2.2 .. don't know if I can upgrade now cause I have to be careful since I have to complete this work in the next 2 days.. Jan 24, 2016 at 14:13
  • You could use ST_Boundary(geom) instead of ST_Collect(ST_StartPoint(geom), ST_EndPoint(geom)). Note this produces an empty MultiPoint for closed linestrings, so these might have be filtered out if they occur.
    – dr_jts
    Dec 29, 2019 at 21:16
8

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.