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:
- 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.
- 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.