Using postgis to flatten all polygons in table

Question

I have a table with many polygons. The polygons all have a name and a value.

I want to flatten these polygons so that each polygon border remains as border to the resulting polygons. Instead of having a stack of polygons I will remain with a mosaic of polygons based on the same lines.

Resulting in this:

The names are insignificant. I want to sum the values of each original polygon within the different tiles of the mosaic. The number of overlaps would also be relevant to calculate.

I am looking for a solution using PostGIS. I have solved this using FME, but my data size (20.000+ objects with millions of overlaps) makes this too time consuming. The union function in QGIS does this fairly straightforward as illustrated form their document webpages.

For those in need of a context the data is actually multi-ring buffers around seabird nesting sites in Norway. The data sets are per species and we are looking at several thousands nesting sites per species.

You may download a test data set here.

Answer 1

Download the PostGIS Addons from this link: https://github.com/pedrogit/postgisaddons

Install by running the postgis_addons.sql file.

Test by running the postgis_addons_test.sql file.

Here is a self contained example of a problem similar to your one:

WITH geomtable AS (
  SELECT 1 id, ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0), (0.2 0.5, 0.2 1.5, 0.8 1.5, 0.8 0.5, 0.2 0.5))') geom
  UNION ALL
  SELECT 2 id, ST_GeomFromText('POLYGON((1 0.2, 1 1, 3 1, 3 0.2, 1 0.2))') geom
  UNION ALL
  SELECT 3 id, ST_GeomFromText('POLYGON((1.5 0.8, 1.5 1.2, 2.5 1.2, 2.5 0.8, 1.5 0.8))') geom
  UNION ALL
  SELECT 4 id, ST_GeomFromText('MULTIPOLYGON(((3 0, 3 2, 5 2, 5 0, 3 0)), ((4 3, 4 4, 5 4, 5 3, 4 3)))') geom
)
SELECT a.id, unnest(ST_SplitAgg(a.geom, b.geom, 0.00001)) geom
FROM geomtable a,
     geomtable b
WHERE ST_Equals(a.geom, b.geom) OR
      ST_Contains(a.geom, b.geom) OR
      ST_Contains(b.geom, a.geom) OR
      ST_Overlaps(a.geom, b.geom)
GROUP BY a.id;

In your query, just replace geomtable with your table:

SELECT a.id, unnest(ST_SplitAgg(a.geom, b.geom, 0.00001)) geom
FROM polytest a,
     polytest b
WHERE ST_Equals(a.geom, b.geom) OR
      ST_Contains(a.geom, b.geom) OR
      ST_Contains(b.geom, a.geom) OR
      ST_Overlaps(a.geom, b.geom)
GROUP BY a.id;

Should work for thousands of polygons and when there are more than two overlaps.

Answer 2

You can go by parts... Use the approach mentioned before:

CREATE TABLE tbl_intersection AS
SELECT
    ST_INTERSECTION(t1.geom, t2.geom) as geom
FROM original_dataset t1, original_dataset t2 
WHERE t1.ID < t2.ID AND ST_INTERSECTS(t1.geom, t2.geom);

And then you use the previous table and the original dataset to run the ST_Difference (The order is important in this case) between then. It should put you on the track

SELECT
    ST_Difference(t2.geom, t1.geom) AS geom
FROM
  original_dataset t1, tbl_intersection t2

Answer 3

If you've added all the correct indexes (on the ID as well as GIST index on geom), the following should do the job - as long as your server is up to spec;

   select st_intersection(a.geom, b.geom) as geom
   from table a, table b
   where st_intersects(a.geom, b.geom)
   group by st_intersection(a.geom, b.geom)