4

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.

A stack of polygons

Resulting in this:

enter image description here

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.

Figure on QGIS union from the QGIS webpage

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.

  • Does ST_Union without a GROUP BY work? – DPSSpatial Nov 1 '18 at 15:44
  • @DPSSpatial no, I have been testing on the above pictured dataset. I should ideally get 7 individual tiles. – ragnvald Nov 1 '18 at 15:59
  • 1
    Are you sure that works on 1 input table? Seems by the documentation that's doing a combination of intersects /clips on mutiple inputs... – DPSSpatial Nov 1 '18 at 17:40
  • 2
    Hello I'm also working in the same thing. If you find the answer, please post it here. What I did until now was getting the intersections with the following sql statement : SELECT ST_INTERSECTION(t1.geom, t2.geom) FROM yourtable t1, yourtable t2 WHERE t1.ID < t2.ID AND ST_INTERSECTS(t1.geom, t2.geom); This gives you all the intersections, however with a overlapping between them. So I think we need to overlap between them again and add in the end the parts out of the intersection with "ST_Difference(original_dataset, intersected_dataset)". I think this would be a start for you two. – Matt_Geo Nov 2 '18 at 8:03
  • 1
    since PostGIS 2.5, ST_Split supports splitting Polygon by MultiLinestring; if that's applicable, collect the exterior rings as blade, split all polygons and count equals. distinct and join those with the original table to sum up the attributes. if you join/collect/split by intersection (bbox intersection, even), performance might be alright. – ThingumaBob Nov 3 '18 at 13:00
2

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.

1

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
  • Not there quite yet. In the first query on the third line it should perhaps be "ST_INTERSECTION(t1.geom, t2.geom) AS geom"? When I remove the condition "t1.id > t2.id and" I got nine unique objects. It does however fail to isolate more than two overlaps at a time. Remember the case involve tens if not hundreds of overlaps. I am looking for all possible pieces in the mosaic - flattended with no overlaps. Much like the QGIS sample above. – ragnvald Nov 2 '18 at 21:24
0

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)
  • Otherwise - try something like in this article: postgis.net/2014/03/14/tip_intersection_faster – Will Allen Nov 2 '18 at 9:49
  • 1
    Not too bad. The above one comes up with nine individual objects. So it splits them totally which is good. But I would also like to keep a select attribute and summarize an integer value in the overlapping polygons. So we are moving forward for sure. I should mention in my case I do not have two different tables, but one. Inputting the same table in your SQL query is what I did. – ragnvald Nov 2 '18 at 10:42
  • 1
    Great, yes you should be able to amend the query to include summing of integers. Yes table a and table b are meant to be the same. Good luck! – Will Allen Nov 2 '18 at 11:07
  • After further testing your suggestion seems not to produce the required outcome. So we're back at square one. – ragnvald Nov 2 '18 at 13:57
  • Oh dear - what is it missing? – Will Allen Nov 2 '18 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.