7

New to PostGIS so I trust this is a very basic question but can't seem to find the right solution.

I have a materialized view consisting of ~4 million polygons. Some of them overlap. I need to merge those that overlap regardless of their attributes (the view only has two attributes; a unique ID and a geom column). Here's my query:

   CREATE MATERIALIZED VIEW dissolved_polygons AS 
   SELECT ST_Union(c.geom) AS geom        
   FROM overlapping_polygons c;

Every solution I've seen groups polygons by attribute, but when I remove the GROUP BY clause I get the following error:

ERROR: array size exceeds the maximum allowed (1073741823) Query failed PostgreSQL said: array size exceeds the maximum allowed (1073741823) Here's my query:

After suspecting I was generating one massive multi-polygon I also attempted:

  CREATE MATERIALIZED VIEW dissolved_polygons AS
  SELECT (ST_Dump(a.geom)).geom FROM
  (SELECT ST_Union(c.geom) AS geom FROM overlapping_polygons c) AS a;

...but got the same error message. Another post here suggested ST_MemUnion might work but the table is way too big for such a slow process. Any ideas?


UPDATE 1

After testing with a smaller subset, I realize that the only way I can get the correct output is to use:

(ST_dump(ST_MemUnion(geom))).geom

...but I've executed this query on the real dataset and 9 hours later it's still running. I suspect it could take several days. Is there anyway to get away with using ST_Union without the memory overload, or is there a different command I could use I'm not aware of?


UPDATE 2

I've more clearly identified the problem but not the solution. According to (Much) Faster Unions in PostGIS 1.4 by Paul Ramsey, ST_Union (post PostGIS 1.4) aggregates all the data in memory before executing the union operation. The limitation of that memory storage appears to be 1GB, and my dataset is bigger than that. ST_MemUnion does what ST_Union used to do, which is union features row by row, without storing anything in memory. That solves the memory issue but it's incredibly inefficient and slow. He talks about using ST_Accum() but I'm not clear on how/if this might help my query, given that I'm now using PostGIS 2.1.

  • Just a side-question, because thats often a problem: Does it matter where the overlapping parts are merged to? – Matte Apr 2 '16 at 6:01
  • If I understand your question right then no, as long as the overlapping polygons merge into single geometries and those that don't overlap remain distinct I can do what I need to do. – Kasey Apr 2 '16 at 6:17
  • Your query would create one multipolygon that would have all the original polygons as members. No overlaps would be merged. I recommend to play with small test data first and develop a query which makes desired result. – user30184 Apr 2 '16 at 8:55
  • It seems to me that you are "unioning" the entire table. Where did you check for overlaps ? – WKT Apr 2 '16 at 10:08
  • What version of postgis are you using? – tilt Apr 2 '16 at 12:05
4

The problem is that ST_Union will add all geometries to the output, not only the overlapping ones, causing postgres to complain. So if there are not too many overlapping polygons, you should try to isolate them first and then run a union. This should work:

SELECT (ST_Dump(ST_Union(a.geom)).geom  
FROM polygons a ,polygons b -- compare the table with itself
WHERE ST_Intersects(a.geom, b.geom) -- only when a geom overlaps
AND (a.id < b.id) -- but not with itself and only one way (hence the < )

The query will likely still take a considerable amount of time, try with a smaller subset (say 40k) first to get an estimate and make sure you have indexed geometry and id.

After you did this, you still have to add the non-overlapping polygons.

  • wouldnt you have to st_union the geoms from table a and b??? – ziggy Dec 12 '17 at 20:51
  • and can you explain this part "AND (a.id < b.id)" – ziggy Dec 12 '17 at 20:52
  • @tilt your code is missing a closing bracket in the first line - besides from that it works perfect – blabbath Jun 16 '18 at 17:03
0

Install the PostGIS Addons and look at the ST_DifferenceAgg() example to remove overlaps:

SELECT a.id, ST_DifferenceAgg(a.geom, b.geom) geom
FROM overlappingtable a, 
     overlappingtable 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)) AND 
       (ST_Area(a.geom) < ST_Area(b.geom) OR 
        (ST_Area(a.geom) = ST_Area(b.geom) AND 
         ST_AsText(a.geom) < ST_AsText(b.geom))))
GROUP BY a.id;

Try on a small subset and then on the whole table if the result matches your expectations.

0

I can think of two other ways to try:

  • ST_Collect followed by ST_Buffer set to 0
  • using grid to union geometries in two steps: first, union all being in the same cell, then union the results
  • 1
    ST_Collect and ST_Union both put their inputs into an array before doing the actual processing, so they would both hit the same 1gb size limit. The grid approach ought to work, though. – dbaston Nov 18 '16 at 19:06

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.