4

I want to compound the red and green links (lines between the poinst) to one or more lines. I need it to finde the green links that not touching the red lines in my network.

Step 1: compound touching links to lines Step 2: find the lines don't touching, crossing the network

How does it works with postgresql ?

enter image description here

Tibor
  • 375
  • 2
  • 7
  • What is the criteria for determining "isolated lines"? Sets of connected links containing no nodes of degree > 2? – dr_jts Aug 21 '21 at 18:31

1 Answers1

5

You probably want to cluster your lines by intersection - use ST_ClusterDBSCAN with eps := 0:

SELECT *,
       ST_ClusterDBSCAN(<geom>, 0, 1) OVER() AS _clst
FROM   <lines>
;

to assign a cluster id (_clst) to connected segments on a per-row basis.

From here you could ST_Union/ST_Collect based on _clst:

SELECT ST_Union(<geom>) AS geom
FROM   (
  SELECT *,
         ST_ClusterDBSCAN(<geom>, 0, 1) OVER() AS _clst
  FROM   <lines>
) q
GROUP BY
       _clst
;

or, in order to find only those disconnected sub-networks, make a selection by ST_Length:

SELECT *
FROM   (
  SELECT ST_Union(<geom>) AS geom
  FROM   (
    SELECT *,
             ST_ClusterDBSCAN(<geom>, 0, 1) OVER() AS _clst
    FROM   <lines>
  ) q
  GROUP BY
         _clst
) q
ORDER BY
       ST_Length(geom) DESC
OFFSET 1
;
geozelot
  • 30,050
  • 4
  • 32
  • 56
  • ST_Union doesn't merge adjacent lines. ST_LineMerge might be more effective here. But that depends on whether ordering by length meets the requirement to identify disconnected lines. (If it does, then the union is probably superfluous?) – dr_jts Aug 21 '21 at 18:29
  • @dr_jts we're only interested in total cluster length (thus ST_Union) in those examples, I don't see any incentive for more sophisticated topological editing. Not sure what OP is really after, but the gist of my answer is the clustering which leaves you with plenty options. – geozelot Aug 22 '21 at 10:57
  • Understood. In that case using ST_Collect will be much faster than ST_Union. – dr_jts Aug 22 '21 at 17:38
  • thx, st_union was works for my case. i try with st_collect to test the perfromance. Is it possible to combine the clustering with additional attribute ? maybe the attribute is quality and have three values 1, 2, 3. group by _clst and quality - – Tibor Aug 23 '21 at 07:36
  • @Tibor ST_Collect is a lot faster, it merely collects geometries into a (minimally fitting) super-type; ST_Union however dissolves and collapses overlapping geometries first - costly but useful for 'dirty' networks. The actual benefit of ST_ClusterDBSCAN being a Window function is that is assigns a numeric id (_clst here) to each row of the input set. So in effect, you can treat the result set like the initial table, but with that additional attribute. You can group by it plus all columns that you have available. Is that what you mean? – geozelot Aug 23 '21 at 09:27
  • And it should be possible to use SUM(ST_Length(..)) and avoid collecting the geometries entirely. SUM can be used as a aggregate function with GROUP BY _clst or as a window function OVER (PARTITION BY _clst) – dr_jts Aug 23 '21 at 16:33
  • ST_Collect To ST_Union is 1:3 - by over 3 million links collect 10 sec und union 30 sec. both okay. Grouping can't try. – Tibor Aug 26 '21 at 16:27