polygon dissolve or remove duplicate records with smaller geometry

Question

I have a polygon table in postgres that has important columns of: address,type_(i know confusing wasnt me),subtype and geom.

this table is parcels buffered 1000 feet. some records like the picture below contains both the parcel geometry and buffer geometry with the same exact records address,type_,subtype. I use this layer to perform various distance calculations to other parcels by the b_uniqueid and the records with just the parcel geometries are giving me a boat load of trouble.

things I have tried:

drop table if exists test2;
create table test2 as
select st_union(geom) geom,address,type_,subtype from buffer_unions group by address,type_,subtype

gives me a

ERROR:  GEOSUnaryUnion: TopologyException: Input geom 1 is invalid: Self-intersection at or near point 20021031.841838982 -322552.71008741739 at 20021031.841838982 -322552.71008741739
********** Error **********

ERROR: GEOSUnaryUnion: TopologyException: Input geom 1 is invalid: Self-intersection at or near point 20021031.841838982 -322552.71008741739 at 20021031.841838982 -322552.71008741739
SQL state: XX000

and in arcgis the dissolve tool does not give me the correct results.

I have also tried:

select st_multi(st_collectionextract(st_collect(geom),3))::geometry(multipolygon,102689) geom,address,type_,subtype from buffer_unions group by address,type_,subtype 

but because the 700 records of subtype OutdoorRecreationFacility has no address field those columns get dissolved down to one record.

What I am looking for is a query which goes through the table and analyzes 1. if a smaller geometry is inside a larger geometry and has the same address and subtype, dissolve them or alternatively maybe if have the same address and subtype compare there st_area's and just take the larger one (not sure how to do that) 2. for the OutdoorRecreationFacility records, because they have no addresses I need to either dissolve if a geometry falls completely within another geometry or just select the larger one

I tried this query but got the the topology error like above

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

Answer 1

You can select the entries using a Distinct On clause on the 3 fields and ordering them by area descending. Since Distinct On keeps the first returned row only, you are therefore keeping the largest polygon, i.e. the buffer. As you said some entries need a special handling, you can omit them from the first query and union them (or apply another rule for distincting them)

create table test2 as (
select  DISTINCT ON (address,type_,subtype) address, type_, subtype, geom
  from buffer_unions
  where subtype <> 'OutdoorRecreationalFacility' 
  order by address,type_,subtype, st_area(geom) desc
UNION ALL
select  address,type_,subtype,geom 
  from buffer_unions
  where subtype = 'OutdoorRecreationalFacility')