0

I have the following scenario:

SELECT * FROM owner_poly;
>
|id| owner_id | polygon_ids |
|1 |    10    |'{1,2,3,20}' |
|2 |    10    |'{12,22,2}'  |
|3 |    10    |'{1}'        |
|4 |    10    |'{12}'       |
|5 |    11    |'{5,7}'      |
|6 |    11    |'{5,9}'      |
|7 |    12    |'{15,77}'    |
|8 |    12    |'{19,47}'    |

SELECT * FROM poly;
>
|    id    |       geom      |
|    1     |MultiPolygon(..) |
|    2     |MultiPolygon(..) |
....

I have a owner_poly table that stores an owner_id and associated polygons through their id's as an array. It is ensured that all polygons inside one array are interconnected. Now I need to group them by their interconnectivity with other arrays. For the above example I need the following output:

| owner_id | polygon_ids      |
|    10    |'{1,2,3,12,20,22}'|
|    11    |'{5,7,9}'         |
|    12    |'{15,77}'         |
|    12    |'{19,47}'         |

All polygons for owner_id 10 and 11 were interconnected and, thus, need to be stored in one array. For 12 it is not the case and, thus, they stay as they are.

Now I am trying to wrap my head around to group them based on the array values but couldn't find a solution.

Therefore I am wondering if a PostGIS function / algorithm exists that could identify interconnected geometries. For instance: id 4 is connected with id 1 through id 2 (polygon_id 12->2), but may not directly intersect with it spatially.

Could someone provide me with a guideline for either way (through the array or the geometry)?

Vince
  • 20,017
  • 15
  • 45
  • 64
JoeBe
  • 1,230
  • 1
  • 9
  • 20
  • I am just providing theory. You can try to use dissolve(ST_UNION) operation group by owner_id if you know polygons are adjacent for each owner_id. If you are not sure which polygons are not adjacent irrespective of owner_id, then dissolve all the polygons in the layer and convert multipolygon to polygons and then apply contains relation to reassign ids from original table. – user3174598 Sep 12 '21 at 14:51
  • st_clusterdbscan – ziggy Sep 12 '21 at 19:46
  • have a look at this post which uses st_clusterdbscan to union connected lines – JGH Sep 13 '21 at 12:50
  • @JoeBe happy to give this a shot if you can provide a sql file with dummy data via a github gist or so – Timothy Dalton Sep 15 '21 at 20:31

0 Answers0