Obtaining Each Unique Area of Polygon Overlap in Postgres 9.6/Postgis 2.3
I have recently finished compiling Postgres 9.6/GDAL 2.2 and Postgis 2.3 and am looking to build a generic, efficient solution for obtaining each unique area of polygon overlap within a given dataset where said dataset can have many overlapping polygons -some of which will have holes. Given that this is a very core geoprocessing operation which doesn't have a function in postgis (yet), I have been attempting to build a solution to this problem on and off since 2012 -when Newt Gingrich was considered an insurgent, anti-establishment republican candidate. Every trial resulted in abject failure -where either the process errored out or resulted in a dumpster-fire dataset riddled with missing polygons and undissolved grid lines..
In the past few days I attempted this again with renewed excitement and hope that some new algorithms and a new GEOS 3.5 engine for PostGIS would perhaps have fixed some of the precision-robustness issues that have plagued my postgis operations in the past -but sadly I am still unable to get the operation to work without it producing garbage.
I have seem several solutions out there, but none seem to account for a variable number of hole-ridden geometries that overlap within a single dataset; consequently, I'm wondering if anyone has a performant solution to this problem that works with your run of-the-mill GIS dataset that sits in an ESRI shapefile that was created with ArcGIS desktop.
2) Create Feature balanced grid by dividing the extent of dataset until no grid cell intersects more than 50 features.
3) Intersect Subdivided Ring Polygons with each grid cell.
4) Dump exterior rings of intersected polygons.
5) Group rings by grid cell id and st_polygonize() for each grid cell.
5) Use st_pointonsurface() to relate the last step's results to the original features.
6) Union geometries by using GROUP BY on each unique array of original feature ids.
I also attempted to use Postgis topology, since it should theoretically be perfect for solving this exact problem as it has a tolerance threshold and should automatically create what I need -but it errors out almost immediately on a side-location conflict using the following code: