My goal is simple. 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 precessing time is just too long to get any results. I read a lot of question throughout this forum and I set up a PostGIS Database.

My query is:

 CREATE TABLE dlm_union_2 AS
  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. Every suggestion are highly appreciated.

UPDATE: 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 ones. I will keep you up to date.

  • 1
    I suppose you do not want to combine the geometries of polygons (that's what you're doing with the query you sent). What you're doing now is you're ST_Unioning every geometry from table A with every geometry from table B. What do you want to achieve? You want to get one table out of many tables? If so, you can use PostgreSQL UNION to get the results. It will union all rows from table A with all rows from table B without combining their geometries. – Michal Zimmermann Nov 26 '13 at 13:37
  • Hi zimmi, thx for your fast response. Yes exactly i want one new table out of many input tables. Analog to the shape file stored in a Gis, where i have like 10 shapes and i want 1 shape, stored all polygons from input. There is no need of preserving any attributes i just need the output geometry which i will use to intersect with another set of unioning geometrys. Thx – Steffen Nov 26 '13 at 13:41
  • Maybe i missread your question. My goal is to get one polygon or multipolygon representing all geometrys from the input tables. Like i use the union-function of qgis,saga,grass i.e – Steffen Nov 26 '13 at 13:43
  • I see. Well it might be faster if you do ST_Union on every table first (and you can save it as a view or a new table, as you wish). Then, when running ST_Union again, you would be actually unioning two insted of a bunch of geometries. – Michal Zimmermann Nov 26 '13 at 14:09
  • Yeah i read this. My Problem is first i dont know how to get my geometries in what they called "set of geometries". Second problem is that the processing time with 2 tables was greater then 1,000,000 ms so i think with more input tables the time will explode dramatically. Steffen – Steffen Nov 26 '13 at 14:15

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.

Good Luck!

| improve this answer | |
  • This sounds good, i will try it on my dataset. – Steffen Nov 27 '13 at 8:06
  • Hi mattmakesmaps. I used the repley from Benno to create a geometric index on the table containing ~ 300,000 Polygons. The Code was: CREATE INDEX index_name ON table USING GIST(geom); After that i run ST_Collect with: CREATE TABLE strasse_buffer_collect AS SELECT ST_Collect(buffer) AS geom FROM strasse_buffer. Iam getting out-of-memory error: ERROR: out of memory DETAIL: Failed on request of size 278831528. ********** Fehler ********** ERROR: out of memory SQL Status:53200 Detail:Failed on request of size 278831528. How to fix that problem? – Steffen Nov 27 '13 at 8:58
  • A hint on other question was to iterate over the data dividing the problem into smaller ones. But iam not sure how to do that, do yo have any ideas? Thx a lot, Steffen – Steffen Nov 27 '13 at 9:01
  • When i try to use your solution on a dataset with 100,777 polygons i get an error when iam buffering the through ST_Collect merged table. ST_Collect worked fine with processing time ~25,000 ms. ST_Buffer on this table throws after 921,850 ms "ERROR: GEOSBuffer: std::bad_alloc SQL Status:XX000". Steffen – Steffen Nov 27 '13 at 10:34
  • After all that union or fake-union processes i need to difference some on the union tables with a set of another union tables. My calculations shows taht the processing time is here extremly high as well. For now i try to use ST_Collect to group my data in one table and use St_Buffer to form a single MULTIPOLYGON. My purpose is to compute the difference between 2 MULTIPOLYGON. When i use ST_Difference the process takes looong time, union as well. My thought was that the time will be highley decreased using a MULTIPOLYGON instead of lots of smaller ones. Is there any error in reasoning? – Steffen Nov 27 '13 at 17:22

1) To accelerate the geometric processing:

A. Add a geometric index:

CREATE INDEX index_name
   ON sie01_f has
   USING gist
CREATE INDEX index_name_b
   ON sie02_f b
   USING gist

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
  a.gid AS ID,
  a.geom AS geom 
  FROM sie01_f a
  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

| improve this answer | |
  • Thanks for the advice benno. Does creating a geometrix index works independent from the subsequent query? I will try to do that tomorrow and will post if it will succeed accelerating my process. – Steffen Nov 26 '13 at 19:58

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.