Need unique id and to select for polygons by size after ST_Dump, ST_Intersection

Question

I have two polygon tables, 'aa_poly_edited' and 'fields'. I am reducing the size of both (ST_Buffer), then intersecting, then using ST_Dump because after the intersection I am getting a couple of multipolygons which I don't want. Here is my query:

SELECT aa_poly_edited.id, (ST_Dump(ST_Intersection(ST_Buffer(aa_poly_edited.geom26912, -5), 
  ST_Buffer(fields.geom26912, -30.48)))).geom::geometry (POLYGON, 26912) as simplegeom
FROM production.aa_poly_edited, production.fields
WHERE fields.field = 'sec35middle' AND ST_Area(ST_Buffer(aa_poly_edited.geom26912, -5)) >= 200
  AND ST_Intersects(aa_poly_edited.geom26912, fields.geom26912);

I need to add two aspects to this query and can't figure them out:

1. the id that is returned is no longer unique so I can't display results in QGIS. I've tried row_number () OVER () as newid but this returns ids that are the row numbers from the original feature, pre - ST_Dump so it is not unique. I also found this blog post http://longwayaround.org.uk/notes/disaggregate-multilinestrings-using-st_dump-postgis/, which uses COALESCE. There is also a SE question to this effect. Trouble is, I am new to PostGIS and SQL and I don't yet have the grasp of subqueries.
2. AFTER ST_Dump, I want to select only those polygons that have ST_Area >= 200 and I'm not sure where to put that in my query as well.

Answer 1

This would be simpler if you wrap your query in a subquery. That will allow you to do two things. (1) You can select the row number over both your original id and the path number of each individual polygon that is produced from ST_Dump. (2) You can use your ST_Area selection criterion. Something like this:

SELECT row_number() OVER(), id, simplegeom FROM ( SELECT aa_poly_edited.id, (ST_Dump(ST_Intersection(ST_Buffer(aa_poly_edited.geom26912, -5), ST_Buffer(fields.geom26912,-30.48)))).geom::geometry (POLYGON, 26912) as simplegeom FROM production.aa_poly_edited, production.fields WHERE fields.field = 'sec35middle' AND ST_Area(ST_Buffer(aa_poly_edited.geom26912, -5)) >= 200 AND ST_Intersects(aa_poly_edited.geom26912, fields.geom26912)) t1 WHERE ST_Area(simplegeom) >= 200;

If the row number still doesn't give you unique rows, you can get the path number from the ST_Dump and get row_number OVER(id, path).