Union of vector polygons split on tile boundaries

Question

I have two geopackages - vectorized output from a classification exercise (let's call them tile1.gpkg and tile2.gpkg). These are located on the border between the two tiles like so:

Each of these has the following data of note: geom (geometry of the polygon), cat (class value), and unid (a unique identifier). I wish to merge these vectorized tiles into a single vector dataset and additionally dissolve the borders between the polygons that have the same value of cat. So far I have the following process:

-- STEP 1: get all polygons at vertices
create table hrl_tst.tile1_2 as
select concat(t1.unid, '-' ,t2.unid) as t1t2_cunid, 
t1.unid as t1unid,
t2.unid as t2unid,
t1.cat as cat1,
t2.cat as cat2, 
st_union(t1.geom, t2.geom) as geom from hrl_tst.tile1 t1
join hrl_tst.tile2 t2
on t1.cat = t2.cat
and 
st_touches(t1.geom, t2.geom);

After STEP 1 I get part of the result I'm looking for - the polygons that are split into only two parts are merged and dissolved as expected (in blue), but the polygons that have a more complex shape as a result of the split (meaning a part from tile1 touches a part of tile2, and tile2 then touches the other part of tile1, but between themselves, the separate parts of tile1 do not touch) fail to dissolve properly (in red):

I know I'm supposed to use ST_Union as an aggregate window function with a partition, as demonstrated in this helpful post, so I have the following STEP 2:

-- STEP 2: merge them with window function
create table hrl_tst.tile1_2_single_3 as
SELECT t1t2_cunid as unid, cat1 as cat, 
ST_Union(geom) over(partition by t1unid) as geom_un1,
ST_Union(geom) over(partition by t2unid) as geom_un2
FROM hrl_tst.tile1_2;

After STEP 2 I have the two pieces of the puzzle, but I'm not sure how to now merge them into a single geometry.

geom_un1 :

geom_un2 :

Interestingly enough, if I carry out STEP 2 as two separate queries:

create table hrl_tst.tile1_2_single as
SELECT t1t2_cunid as unid, cat1 as cat, ST_Union(geom) over(partition by t1unid) as geom
FROM hrl_tst.tile1_2;

and

create table hrl_tst.tile1_2_single_2 as
SELECT t1t2_cunid as unid, cat1 as cat, ST_Union(geom) over(partition by t2unid) as geom
FROM hrl_tst.tile1_2;

it does not yield the same result, which additionally confuses me.

Can anyone explain to me why this is the case?

Answer 1

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 the tables, then ST_Union() the shapes back together, grouping by cat and unid:

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().
If you're below PostGIS 3.4.0 but above 2.3.0, you can use ST_ClusterDBSCAN() with target distance of 0. Here's a related thread on SO from recently.

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):

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.

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 a bit of 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.

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;

---

*_{(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).}

---

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() 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 joins and the two-input st_union, if you used a recursive query, made pairs, them kept joining more and more shapes to those, until you ran out. It'd be a slow, ungly Goldberg machine, 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.