sql - Query to get all clusters of geometry objects

Question

I'm trying to make clusters of geometries that are all connected to each other. All borders are written to a table and have their own id. Then there's a table that maps the borders to the geometry objects.

Table NGM_DECOMPOSITIE_GRENS_ID with columns DCG_ID (id of the border) and IDENTIFICATIE (id of the geometry object).

In the image below you can get a general idea of the problem. I want to identify all clusters of connected objects. At a glance there are about 7 clusters in this image.

Borders between objects will have multiple entries in NGM_DECOMPOSITIE_GRENS_ID. They have the same value for DCG_ID and an IDENTIFICATIE for each object they are part of.

Given a specific object ID I've managed to get all it's neighbours.

select distinct g1.identificatie,g2.identificatie
  from ngm_decompositie_grens_id g1
      ,ngm_decompositie_grens_id g2
 where g1.dcg_id = g2.dcg_id
   and g1.identificatie != g2.identificatie
 start with g1.identificatie = 2591003
connect by nocycle prior g1.dcg_id = g2.dcg_id

IDENTIFICATIE   IDENTIFICATIE
1   2591003 2593126
2   2591003 2593156
3   2591003 2589815
4   2591003 2591125
5   2591003 2592920
6   2591003 2591819
7   2591003 2591135

The yellow object is 2591003 and the red ones are all the objects that share a border with it.

Given this result I can run the query for one of the neigbours.

select distinct g1.identificatie,g2.identificatie
  from ngm_decompositie_grens_id g1
      ,ngm_decompositie_grens_id g2
 where g1.dcg_id = g2.dcg_id
   and g1.identificatie != g2.identificatie
 start with g1.identificatie = 2593126
connect by nocycle prior g1.dcg_id = g2.dcg_id

To get:

What I can't figure out is how to get the query to return all objects in a cluster automatically. So that, given a starting object (2591003 for example) I get all objects related directly and indirectly to this starting object.

I've tried creating a view:

create view ngm_vw_decompositie_id as
select distinct g1.identificatie id1,g2.identificatie id2
  from ngm_decompositie_grens_id g1
      ,ngm_decompositie_grens_id g2
 where g1.dcg_id = g2.dcg_id
   and g1.identificatie != g2.identificatie
connect by nocycle prior g1.dcg_id = g2.dcg_id

And query this view hierarchically

select id1,id2 
from ngm_vw_decompositie_id
start with id1=2591003
connect by prior id2=id1

But that results in a ORA-01436: CONNECT BY loop in userdata.

Adding a NOCYCLE

select id1,id2 
from ngm_vw_decompositie_id
start with id1=2591003
connect by NOCYCLE prior id2=id1

Results in an endless result set. With the data not being strictly hierarchical that might be expected. Maybe I have to implement a solution that uses the query I have. Every object one by one and excluding already visited objects. As suggested in the answer to this question. Oracle hierarchical query on non-hierarchical data

question from:https://stackoverflow.com/questions/65897019/query-to-get-all-clusters-of-geometry-objects

Answer 1

Waitting for answers