<div dir="ltr"><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">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..</p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">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.</p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">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. </p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">I documented the entire process I undertook, with code,images and link to source dataset here: <a href="http://gis.stackexchange.com/questions/206473/obtaining-each-unique-area-of-overlapping-polygons-in-postgres-9-6-postgis-2-3">http://gis.stackexchange.com/questions/206473/obtaining-each-unique-area-of-overlapping-polygons-in-postgres-9-6-postgis-2-3</a>. </p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)"><br></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">See summary of steps below:</p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">1) Subdivide geometry and dump outer rings.</p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">2) Create Feature balanced grid by dividing the extent of dataset until no grid cell intersects more than 50 features.</p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">3) Intersect Subdivided Ring Polygons with each grid cell.</p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">4) Dump exterior rings of intersected polygons.</p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">5) Group rings by grid cell id and st_polygonize() for each grid cell.<br></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">5) Use st_pointonsurface() to relate the last step's results to the original features.</p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">6) Union geometries by using GROUP BY on each unique array of original feature ids.</p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)">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:</p><p style="margin:0px 0px 1em;padding:0px;border:0px;clear:both;background-color:rgba(248,248,248,0.6)"><font color="#242729" face="Arial, Helvetica Neue, Helvetica, sans-serif"><span style="font-size:15px;line-height:19.5px">SELECT CreateTopology('comm_reg_topo',find_srid('austin','community_registry','geom'));</span></font></p><p style="margin:0px 0px 1em;padding:0px;border:0px;clear:both;background-color:rgba(248,248,248,0.6)"><font color="#242729" face="Arial, Helvetica Neue, Helvetica, sans-serif"><span style="font-size:15px;line-height:19.5px">SELECT</span></font></p><p style="margin:0px 0px 1em;padding:0px;border:0px;clear:both;background-color:rgba(248,248,248,0.6)"><font color="#242729" face="Arial, Helvetica Neue, Helvetica, sans-serif"><span style="font-size:15px;line-height:19.5px">  TopoGeo_AddPolygon('comm_reg_topo',(st_dump(geom)).geom)</span></font></p><p style="margin:0px 0px 1em;padding:0px;border:0px;clear:both;background-color:rgba(248,248,248,0.6)"><font color="#242729" face="Arial, Helvetica Neue, Helvetica, sans-serif"><span style="font-size:15px;line-height:19.5px">FROM</span></font></p><p style="margin:0px 0px 1em;padding:0px;border:0px;clear:both;background-color:rgba(248,248,248,0.6)"><font color="#242729" face="Arial, Helvetica Neue, Helvetica, sans-serif"><span style="font-size:15px;line-height:19.5px">  austin.community_registry;</span></font></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:15px;clear:both;color:rgb(36,39,41);font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;line-height:19.5px;background-color:rgba(248,248,248,0.6)"><br></p></div>