I need to create a "total" polygon by merging together all the geometries from about 10+ tables into one big polygon. For a test with just 2 tables I tried ST_UNION and ST_COLLECT, the result was 500,000 rows and took 15 minute to process. To process all 10 will probably take several days..

Any ideas how I can tackle this problem? Can I get the outermost line of all the polygons and convert that back to a polygon perhaps? Each table contain several polygons.

EDIT: The system I am running on is i7-4800MQ CPU 2.7GHz with 8GB ram, 64-bit, win7

The number of vertex points on the datasets vary between 25335 to 585723, average vertex points is estimated to about 100000.

I am not very familiar with spacial queries but what I tried so far is this simple query

row_number () over () as x_id, 
ST_COLLECT(layer1.geom, layer2.geom) as geom

Did the same with ST_UNION. I have not tried the to run with all the layers yet as I am just trying out with a few to begin with. My initial idea was to run

row_number () over () as x_id, 
ST_COLLECT(ARRAY[list of all 10 layers here])
FROM all layers
  • Please edit the question to include your actual SQL query and information about the row and average vertex counts of each table. Union is an expensive operation, requiring both CPU and RAM resources to complete processing. You should give an indication of the OS, CPU, and available RAM. – Vince Apr 4 '16 at 11:02
  • 500,000 rows is not very helpful without knowing how many there are in the input and whether this is reasonable for unioned ouput. ST_Collect merely converts a set/array of geometries to a collection of some type, ie, multipolygon in your case. As Vince says, without seeing the queries you are trying, and knowing something about your input tables, it is impossible to help further. – John Powell Apr 4 '16 at 17:43
  • Thanks for the comments and sorry for my noobity, I have added some more information. I understand these kind of queries are very comp expensive, do you think this will be possible to complete withing around 30 minutes? That would be an accepted time otherwise I might have to look for another solution. – geogrow Apr 5 '16 at 7:38
  • Sorry, missed the response. If you want to alert someone in particular, use @name syntax. OK, so, ST_Collect simply merges the input into a collection of geometries, without removing any overlaps (as ST_Union does). Is this what you want? It will be much quickly than ST_Union, for that reason. – John Powell Apr 5 '16 at 15:12
  • Dont worry, I am not expecting emediate response =)@JohnBarça, Im just always impressed with the knowledge this forum has! I finally got ST_Collect to work after combining all the geom layers into one big table holding all layers, not very dynamic but OK for now. I would like to use ST_Union. My problem is geometry problem, getting errors like found non-noded intersection etc. I have tried using makevalid, snaptogrid, buffer. I think I have to dive deeper in my data and find out whats wrong. I will post any solution I come up with! Thanks – geogrow Apr 6 '16 at 6:55

Your Answer

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

Browse other questions tagged or ask your own question.