[postgis-users] Obtaining Each Unique Area of Polygon Overlap in Postgres 9.6/Postgis 2.3

Andrew Joseph ap.joseph at live.com
Wed Aug 10 18:54:30 PDT 2016

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

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.

I documented the entire process I undertook, with code,images and link to
source dataset here:

See summary of steps below:

1) Subdivide geometry and dump outer rings.

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:





-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160810/f1656ba4/attachment.html>

More information about the postgis-users mailing list