PostGIS union multiple tables, big dataset, faster approach

Question

I want to union a given set of shapefiles. In the shapefile are stored polygons. The data bounding box is Germany, so the data is relatively big. I tried to use all functions implemented in QGIS. Either they failed or the processing time is just too long to get any results. I read a lot of question throughout this site and I set up a PostGIS Database.

My query is:

 CREATE TABLE dlm_union_2 AS
  SELECT
  a.gid AS ID,
  ST_UNION(a.geom,b.geom) AS geom 
  FROM sie01_f a, sie02_f b

I added the Selection of the a.gid since I have no objects returned when I export the table via pgSHAPELOADER.

I want to use more than two tables in one expression and is there a way to get this faster?

With only two tables my processing time is now about 800,000 ms and the progress isn't finished yet. After that I want to buffer the merged regions and would like to union it again afterwards. I have a lot of shapes that I have to union at different states of my project.

I am working on WIN 7, 4GB ram, Intel Core i5 M520. PostGIS and PostgreSQL should be up to date, because I installed the components today.

I have created a buffer on a multilinestring table. Processing time was about 300,000 ms what is actually great compared to functions implemented in QGIS. My problem is that I need to union the result for further analysis. I am using ST_UNION with a single table with polygon geometry input. Objectsize is around 300,000 polygon features. At this point my processing time is 2,800,000 ms and I don't know when it will finish.

Is there any way to get such a calculation faster?

After processing time 5,319,543 ms the query aborted with following exception. This was the try to use ST_UNION with one table including 300,000 polygons.

ERROR:  GEOSUnaryUnion: std::bad_alloc

********** Fehler **********

ERROR: GEOSUnaryUnion: std::bad_alloc
SQL Status:XX000

According to @Mattmakesmaps I tried the way of ST_Buffer(ST_Collect(geom),0) instead of using the UNION command. With the errors I am posted above I was not able the handle the whole extend of the data. So I split the problem into smaller ones. In my case I used the state-borders in Germany to divide my problem into 16 smaller ones. @Mattmakesmaps solution works fine here. Recently I am try to use the ST_Simplify command to make the dimension of the polygons smaller giving me a chance to process all of the data at once.

Answer 1

For what it's worth, the ST_Collect() function is much faster then a call to ST_Union().

The speed increase is mainly attributed to the fact that ST_Collect() returns a Geometry Collection object, and doesn't try to dissolve overlapping geometries.

You can create a single, OGC-compliant geometry (e.g. a POLYGON or MULTIPOLYGON) by wrapping ST_Collect() in a 0-width buffer, e.g. ST_Buffer(ST_Collect(geometry), 0).

I've used it to generate global-scale label points for OpenStreetMap water features (a rather large dataset). With proper GIST indexes as noted by previous respondents, it's a pretty fast operation.

Answer 2

1) To accelerate the geometric processing:

A. Add a geometric index:

CREATE INDEX index_name
   ON sie01_f has
   USING gist
   (geom);
CREATE INDEX index_name_b
   ON sie02_f b
   USING gist
   (geom);

If there are none.

B. Start VACUUM to collect statistics on tables:

VACUUM ANALYZE sie01_f (geom);
VACUUM ANALYZE sie02_f (geom);

2) If you want to create a single table for all of your entities(without ST_Union) you can run the following query:

CREATE TABLE dlm_union_2 AS
  SELECT
  a.gid AS ID,
  a.geom AS geom 
  FROM sie01_f a
UNION
  SELECT
  b.gid AS ID,
  b.geom AS geom 
  FROM sie02_f b ;

Otherwise your first query is valid :

CREATE TABLE dlm_union_2 AS SELECT a.gid AS ID,
ST_UNION(a.geom,b.geom) AS geom FROM sie01_f a, sie02_f b