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'