I face a challenge with PostGIS that I cannot seem to wrap my head around. I know I can solve this using a programming language (and that is my backup-plan), but I really do like to solve this in PostGIS. I've tried searching, but could not find any answers that matches my problem, this might be because I'm uncertain on my search terms, so please excuse that and point me in the right direction of there indeed is an answer.
My problem is this:
- I have a table with mixed Polygons/MultiPolygons
- Each (multi)polygon has an attribute that ranks it (priority)
- Each polygon also has a value I would like to know
- I have a search area (polygon)
- For my query area I want to find the area covered by each polygon value
And that the smaller, blue rectangle is my query polygon
In addition, the attributes are
geom | rank | value -------|------|---- red | 3 | 0.1 green | 1 | 0.2 indigo | 2 | 0.2
What I want is to select these geometries, so that the highest ranked (green) fills all the area it can (i.e the intersection between my query geom and that geom), then the next highest (indigo) fills the intersection between the query geom and the geom MINUS the already covered) etc.
I've found this question: Using ST_Difference to remove overlapping features? but it does not seem to do what I want.
I can myself figure out how to compute areas and such, so a query that gives me the three geometries as depicted in the second image is fine!
Additional info: - This is not a large table (~2000 rows) - there may be zero or multiple overlaps (not just three) - there may not be any polygons in my query area (or just in parts of it) - i'm running postgis 2.3 on postgres 9.6.6
My fallback solution is to do a query like this:
SELECT ST_Intersection(geom, querygeom) as intersection, rank, value FROM mytable WHERE ST_Intersects(geom, querygeom) ORDER by rank asc
And then iteratively "chop off" parts of the geometries in code. But, as I said, I would really like to do this in PostGIS