dissolving polygon records that only fall completely in another polygon from the same table

Question

I have a polygon table that consists of 700 records with the subtype of 'OutdoorRecreationalFacility'. this postgres table is supposed to be a table of 1000 foot buffers around certain parcels. However in some instances the parcel geometry exists in a separate record shown in the picture below

The problem is none of these records have addresses so I cannot st_union them by address which would solve my problem. There is no attribute link between the parcels and the parcel buffer. they are only spatially linked. I am trying to come up with a query that would either

1. select the larger geometry if one geometry falls completely within another geometry or select the larger geometry

2. for all the geometry records that fall within another geometry record st_union them together

I formulated this query but it just keeps running and running (has an index)

 select case
when st_within(st_makevalid(t1.geom),st_makevalid(t2.geom)) then st_union(st_makevalid(t1.geom),st_makevalid(t2.geom))
else t2.geom end geom
from buffer_unions t1 cross join buffer_unions t2
where t1.subtype = 'OutdoorRecreationalFacility' and t2.subtype  ='OutdoorRecreationalFacility'

My thought process behind this query is if a geom falls within another geom, union them together, if not just return the t2.geom ....

Answer 1

If the small polygons are more or less rectangular, not overlapping each other and have a simple geometry, you could match the buffered polygons to the small ones by assessing if their centroid falls within the small polygons.

The query to identify the small polygons that contains the centroid of another (buffered) one should be similar to this. You can then delete the not-null IDs

  select  smallpoly.id smallID_to_delete ,
          bufferedpoly.id bufferID_for_reference
  from buffer_unions bufferedpoly
  left join buffer_unions smallpoly
  on st_within(ST_Centroid(bufferedpoly.geom), smallpoly.geom) 
  where smallpoly.subtype = 'OutdoorRecreationalFacility'

PS: the query you posted will return way too much results, count(*) * (count(*) - 1), as you do a cross join and return the 2nd geometry if t1 is not within t2 (and each t1 polygon is at most in one t2, so you get almost a copy of t2 for each t1). With such query you must keep the matching entries only