PostgreSQL - Merge only lines that share a specific attribute

Question

I have a trail that is split up into multiple small parts. On some of the parts i have managed to get the traile name as attribute "text". The rest has text as "null". Now I would like to join all lines in the trail that has close enough endpoints and that has either share the "text" or has the text as null.

Example of attribute "text" in lines that all have close endpoints:

1. null
2. null
3. Trail1
4. null
5. Trail1
6. null
7. null
8. Trail2
9. null
10. Trail2

In the above list I would like to group:

• 1-7 with a common attribute "text" = Trail1
• 8-10 with a common attribute "text" = Trail2

EDIT - Added illustration(Left part of the long black line should be grouped as Trail1, right part as Trail2:

I have used ST_ClusterDBSCAN(geom, eps := 5, minpoints := 2) to group by distance before but not on attributes like this. If i would run it on the example above it would put all lines in the same entity Wich is an issue.

The queryes i have been using this far.

1.This query joins two tables, one with all of the small trail parts and one with the texts, so im joining the texts that are within 50 meters from a trail and assume that is the trail name. This gives me the data in the illustration above.

DROP TABLE IF EXISTS "lines_with_text";
CREATE TABLE "lines_with_text" AS 
SELECT
  lines.id,
  max(lines.location) as location,
  max(texts.text) as text,
  lines.geom
FROM
  "line_table" as lines
LEFT OUTER JOIN (SELECT * FROM "text_table") AS texts ON ST_DWithin(texts.geom, lines.geom,50)
GROUP BY lines.id, lines.geom

2.Then i tried to merge them with ST_ClusterDBSCAN but this does not give me the desiered result. This will join all lines that are close together an will split them when two lines are not within 20 meters. I would instead split when a new trailname is found rather than when the distance is to long. Usally the lines are between 0-2 meters apart from each other(they are not perfectly aligned but sometimes they are more than 20 meters apart and then i have accepted that it will be a new polyline. Maby with the same Trail name as the prior one.

DROP TABLE IF EXISTS with_text_joined;
CREATE TABLE with_text_joined AS 
SELECT
  min(location) as location,
  ST_Union(geom) as geom,
  min(text) as text
FROM (
  SELECT
    location,
    geom,
    text,
    ST_ClusterDBSCAN(geom, eps := 20, minpoints := 2) OVER() AS cluster_id
  FROM "lines_with_text" as lines
) sq
GROUP BY cluster_id

Answer 1

This is complicated because you have multiple null track part, so you can't really get only the closest text for the null because it can be quite far, plus I imagine that you can have specific cases where the closest part is not the one you want. I think in this case the cleanest approch would be to construct your track from the start.

Here I imagine that your tracks linestring geom are in the good orientation. You can do a recursive CTE to build your full track parts by sewing the parts with the closest start of the current end, for exemple like so (I couldn't test the code, so I'm not sure it is entirely correct sorry):

WITH start_end AS (
  SELECT
    id,
    text,
    ST_StartPoint(geom) as start_point,
    ST_EndPoint(geom) as end_point,
    geom
  FROM
    lines_with_text
), RECURSIVE propagate_track(id_list, start_point, end_point, text, geom) AS (
  SELECT
      ARRAY[rec.id] as id_list,
      start_point,
      end_point,
      text,
      geom,
  UNION ALL
  SELECT
      array_append(id_list, new_track.id) as id_list,
      start_point,
      new_track.end_point,
      coalesce(text, new_track.text) as text,
      ST_LineMerge(ST_Collect(geom, new_track.geom) as geom
    FROM
      propagate_track pt
    -- Get the closest track part from the current end
    LEFT JOIN LATERAL
    (
      SELECT 
          id,
          start_point,
          end_point,
          text,
          geom,
        FROM
          start_end new_track
        WHERE
          pt.text IS NULL OR new_track.text IS NULL -- stop if there is a different name
          AND ST_DWithin(pt.end_point, new_track.start_point, 10) -- Limit the search to 10m
          AND NOT (new_track.id = ANY(id_list)) -- Avoid to loop on an already taken track
        ORDER BY
          pt.end_point <-> new_track.start_point
        LIMIT 1
    ) 
)
SELECT * FROM propagate_track;

The result of this request would be the construction of all the tracks having a unique name, starting from all the tracks parts. So you should filter after by grouping these result by text and keeping only the longer one.