I'm looking for guidance on a pgSQL query to dissolve polygons in PostGIS that are within 200 m of each other, have the same creation date and are above a threshold area.
I am trying
SELECT ST_Union(ST_Buffer(geom, 100)) as bffrd, ST_Union(geom) as parcelgeom, "Date", STRING_AGG("name",',') AS namez, STRING_AGG("Desc",',') AS Descz FROM mapz.polygons WHERE ST_Area(geom) >'20000' GROUP BY "Date" ;
However this query does what it says and aggregates the polygons by date, not being restricted to adjacent polygons. So I am getting a multipolygon feature with polygons very distant from the target which have erroneously been included.
For the most part, the polygons of interest do not overlap. All the training guides I have seen discuss joins between tables, whereas all these polygons are in the one table. Also, the resulting polygons should retain their same size, I am not trying to increase their size, only select and aggregate them into multipolygons based them on the buffer.
I think I am seeking how to include ST_Buffer(geom, 100) in the Group By but that won't run as a query for me.