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
select the larger geometry if one geometry falls completely within another geometry or select the larger geometry
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 ....