Query to get all clusters of geometry objects

I'm trying to make groups of geometries that share a border. 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.

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.

https://stackoverflow.com/questions/65897019/query-to-get-all-clusters-of-geometry-objects January 26, 2021 at 02:55PM