How to merge polygons that share some features
--
If the same "original" shape has the same `cat` and `unid` in both tables, you can just [`UNION`][1] the tables, then [`ST_Union()`][2] the shapes back together, grouping by `cat` and `unid`:
```pgsql
create table hrl_tst.your_shapes_back_together as
with both_tiles as (table hrl_tst.tile1 union all table hrl_tst.tile2)
select cat
,unid
,st_union(geom) as geom
from both_tiles
group by cat,unid;
```
This will use the aggregate variant of `ST_Union()` and dissolve *all* shapes that share the same `unid` and `cat` from both tables at the same time.
Your example used two-variant `ST_Union()` in STEP 1 that kept only dissolving two shapes at a time and in STEP 2 you merged those pairs into groups sharing one common shape, which is still a few steps away from dissolving the whole group.
How to merge polygons that touch and share some features
--
You only need [`ST_ClusterIntersectingWin()`][3].
If you're below PostGIS 3.4.0 but above 2.3.0, you can use [`ST_ClusterDBSCAN()`][4] with target distance of `0`. [Here's a related thread on SO from recently][5].
```pgsql
create table hrl_tst.your_shapes_back_together as
with both_tiles as (table hrl_tst.tile1 union all table hrl_tst.tile2)
,clustered as (
select *,ST_ClusterDBSCAN(geom,0,1)over(partition by cat) c_num
from both_tiles)
select cat
,array_agg(unid order by unid)--dissolve same `cat`s ignoring `unid` diff
,st_makevalid(st_union(geom)) as geom --it's healthy to `st_makevalid()`
from clustered
group by cat,c_num;
```
I spawned around 190 polygons assigned to 3 `cat` classes (it's zoomed in on ~120):
[![enter image description here][6]][6]
And after running the query above, all polygons that share the `cat` feature and `ST_Touch()` (actually, they can `ST_Intersect()` in any way) directly or indirectly (through a neighbour they touch, or a chain of touching neighbours' neighbours) get merged.
[![enter image description here][7]][7]
It can stitch shapes broken along a tile boundary back together, but as you can see it works with arbitrarily shaped splits just as well - I just smashed my two squares into pieces, threw some away and shook everything hard to make mess\*.
This does not attempt to simply `ST_Union()` all Polygons that share the same `cat` feature - that would be trivial, but result in one large MultiPolygon for each `cat`. The disjoint brown, blue and green shapes above are separate, standalone shapes unless they touch somehow.
If your `unid` field is unique but spans the sets, meaning that the same shape's split between the two tables but holds the same unid in both, and you don't want to merge shapes of different `unid`, you can add `unid` anywhere on the `group by` list and swap out `array_agg()` for just `unid`.
```pgsql
create table hrl_tst.your_shapes_back_together as
with both_tiles as (table hrl_tst.tile1 union all table hrl_tst.tile2)
,clustered as (
select *,ST_ClusterDBSCAN(geom,0,1)over(partition by cat) c_num
from both_tiles)
select cat
,unid
,st_makevalid(st_union(geom)) as geom --it's healthy to `st_makevalid()`
from clustered
group by cat,unid,c_num;
```
***
\*<sub>(Make some squares, spawn 90 random points in each, subdivide them with Voronoi cells around those, discard random 30%, wiggle the rest furiously with `ST_Translate()` with `random()` x&y offsets, then trim them with `ST_Difference()` so that they can touch, but not overlap).</sub>
***
About your initial approach
--
1. A `join` results in pairs, which you don't want if you aim to connect more than two things in each row.
2. By joining the two tables, you not only didn't achieve your goal, you duplicated your data. Each time you had a shape on in `hrl_tst.tile1` that touched **N** shapes in `hrl_tst.tile2` with the same `cat`, the first shape got repeated **N times** on output, one time with each of those N touching shapes in `tile2`.
If you only inspected your intermediate results visually, you probably didn't see them, since they'll be in the exact same spot. If you increase transparency, you'll see undissolved lines everywhere, because those shapes will each time dissolve with one neighbour at a time.
3. You're ***not*** *supposed to* use the two-input variant of [`ST_Union()`][2] here, at all - not over a window, not directly. You want the aggregate variant, but you also need a way to apply it to entire clusters of touching shapes. Not all shapes of the same `cat`, not *pairs* of touching shapes - whole *clusters* or *groups* of touching shapes.
4. Technically, there is a way to do that with a bunch of `join`s and the two-input `st_union`, if you used a [`recursive`][8] query, made pairs, them kept joining more and more shapes to those, until you ran out. It'd be a slow, ungly [Goldberg machine][9], and run out of memory pretty fast.
5. Your observation that you got different results by splitting STEP 2 was likely due to the duplication. One time your map viewer showed one set of merged pairs on top obfuscating the ones below, another time it sunk them down below, giving you an impression that those are different results, while you were just viewing different subsets of the same overall set.
[1]: https://www.postgresql.org/docs/current/queries-union.html
[2]: https://postgis.net/docs/ST_Union.html
[3]: https://postgis.net/docs/ST_ClusterIntersecting.html
[4]: https://postgis.net/docs/ST_ClusterDBSCAN.html
[5]: https://stackoverflow.com/a/78228340/5298879
[6]: https://i.sstatic.net/nHMUpVPN.png
[7]: https://i.sstatic.net/lQiMkUy9.png
[8]: https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE
[9]: https://en.wikipedia.org/wiki/Rube_Goldberg_machine