2

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.
2
  • For unique id's I usually use a SEQUENCE like: CREATE SEQUENCE counter; You can then use nextval('counter') in your final select to get a unique number. I would further suggest you split the query into WITH statements, this helps tremendously in keeping a clean workflow compared to subselects (just google for some examples).
    – tilt
    Oct 5, 2016 at 16:31
  • I'm not familiar with the SEQUENCE - I'll look into that. I was able to rearrange the subquery in the accepted answer into a CTE and also got it to work. Thanks for the tip!
    – pdavis
    Oct 5, 2016 at 17:04

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.