ST_Collect runs out of memory

Question

I am trying to calculate the convexhull for an entire table containing 3 200 000 records. the_geom is of type POINT:

SELECT ST_ConvexHull(ST_Collect(the_geom)) FROM mytable;

After 1-2 seconds it fails with the following error:

ERROR:  out of memory
DETAIL:  Failed on request of size 134217728.

********** Error **********

ERROR: out of memory
SQL state: 53200
Detail: Failed on request of size 134217728.

I have tried removing the ST_Convexhull call and just called ST_Collect with the same result.

I am currently running this on PostGIS 1.5, PostgreSQL 9.1 on Windows 8. Is it likely running on Linux will somehow fix the problem?

I have tried to set the following values in postgresql.conf, but nothing seem to help:

shared_buffers = 1024MB
temp_buffers = 1024MB
work_mem = 1024MB
maintenance_work_mem = 1024MB

I have also tried using ST_MemCollect but it runs extremely slow.

Answer 1

I suggest you do it in smaller steps, with let's say 100k points per iteration. I wouldn't store the result of ST_Collect for these, but their convex hull directly, since it will have a much simpler representation and will be more efficient to work with. Once you have a new table of convex hulls, just repeat the same process over it, since the desired convex hull is the same as the convex hull of the mini convex hulls you created in the first part. Dissolve the hull collection first, if you get wierd results.