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
SELECT row_number () over () as x_id, ST_COLLECT(layer1.geom, layer2.geom) as geom FROM sensus.layer1, sensus.layer2
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
SELECT row_number () over () as x_id, ST_COLLECT(ARRAY[list of all 10 layers here]) FROM all layers