Clip geometries in table based upon value

Question

I have defined a view in postgres that returns a set of polygons and can be queried as follows:-

SELECT wkb_geometry, grade_count FROM ng_schema.grade_count_report [sql.where] order by grade_count asc

This returns me a set of polygons with varying values of grade_count

What I want to do next is find geometries that overlap and cut out the overlap from the geometry that has the lower grade_count. Where they were equal I would cut the geometry with the largest area.

How best to do this?

UDATE: To be clear when I say "has the lower grade_count" this is the lower grade count of the two intersecting geometries NOT the lower grade_count of all of them as the answer below has calculated.

So I need to find ALL intersections across ALL geometries and clip them all with the LOWEST grade_count of the two losing part of its geometry.

Answer 1

Let say that this view is called grade_geom_table

SELECT wkb_geometry, grade_count FROM ng_schema.grade_count_report [sql.where] order by grade_count asc

The following query will return you the lowest grade_count with the biggest area:

SELECT wkb_geometry FROM grade_geom_table ORDER BY grade_count ASC, ST_Area(wkb_geometry) DESC LIMIT 1

Put things together and this will give you this:

SELECT
  ST_Difference(grade_geom_table.wkb_geometry, lower_grade.wkb_geometry),
  grade_geom_table.grade_count
FROM
  (SELECT wkb_geometry FROM grade_geom_table ORDER BY grade_count ASC, ST_Area(wkb_geometry) DESC LIMIT 1) as lower_grade,
  grade_geom_table
WHERE
  grade_geom_table.wkb_geometry && lower_grade.wkb_geometry AND
  ST_Intersects(grade_geom_table.wkb_geometry, lower_grade.wkb_geometry)

Note that I didn't test this since I don't have your dataset so it's possible that I made a typo or inverted an ASC or DESC. Let me know!

PS: If you didn't create a PostgreSQL view you can simply replace grade_geom_table in the above query by:

(SELECT wkb_geometry, grade_count FROM ng_schema.grade_count_report [sql.where] order by grade_count asc) as grade_geom_table