I am trying to generated a dissolved buffer of a large point dataset (Ideally 29 million points - Address data in Great-Britain, but I receive the data by chunks of 1 million points, so these could be used instead).
After trying (and obviously failing!) different methods using ArcGIS, I came across this article: http://blog.cleverelephant.ca/2009/01/must-faster-unions-in-postgis-14.html
Since I have been willing to try postgis for a while a gave this a go. Obviously being an absolute beginner I struggled a bit to get the whole thing up and running, but I finally managed to import data and buffer it. I am struggling however with the ST_Union call, because of memory issues (I am using postgis 2.0.1 running on pretty old 32bits windows pc with little scope to change that in the near future).
I tried changing the memory settings of postgres, as described in the question below: Queries returning very big datasets in PostGIS
I believe I also created a spatial index properly on my geometry column. As far as I can tell, it didn't help.
I managed to run ST_Union on a 500 000 points subset, which is already pretty big. I think at that point the best for me might be to try using the Declare/Fetch method mentioned in the above question, however being a complete beginner I am having difficulties.
I believe it is in my interest to keep the slices as big as possible (because the ST_Union algorithm is fast and optimised). I think I would therefore just need to find a way to "slice" my ST_Union() queries to output one multipolygon for each 500000 rows. I would then be just one ST_Union on the resulting multipolygons away from the holy grail...
It is also possible that my query is sub-optimal. Or, there might by another great simple solution that I just haven't thought about or found online yet...
Here is a query I am currently using:
drop table if exists ab_buffer_unioned; create table ab_buffer_unioned ( gid serial primary key , the_geom geometry ); insert into ab_buffer_unioned (the_geom) select st_Union(the_geom) from (select the_geom from ab_buffer limit 500000) as absubset;
Does anybody have any idea on how to fix my issues, or modify that query to add a loop or a cursor?
Many thanks to Paul for his suggestion below. The GeoHash trick worked great as can be seen below (see how the cluster size varies with the population density!)