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?
After testing with a smaller subset, I realize that the only way I can get the correct output is to use:
...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?
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.