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 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


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!) ST_Union applied to slices of data sorted by GeoHash

  • running on pretty old 32bits windows pc - you are hitting the limitation of the operating system. This should work in PostGIS without to much issue - recommend a linux based 64 bit postgis, fast disk 15,000rpm and lots of RAM 8/16GB – Mapperz Aug 21 '12 at 16:15

You seem to be trying to union all the shapes without any use of attributes to group, which is odd, but taking that as a given... you want to try and union things that are close together first. So


WITH ordered AS (
  SELECT ST_Buffer(geom, 10) AS geom
  FROM points
  ORDER BY ST_GeoHash(geom)
grouped AS (
  SELECT nextval('bseq') / 100000 AS id, ST_Union(geom) AS geom
  FROM ordered
groupedfinal AS (
  SELECT ST_Union(geom) AS geom
  FROM grouped
SELECT * FROM groupedfinal;

You might still run out of memory since you're building one ultra huge geometry from millions of inputs. If you do actually have grouping that results in a smaller feature I'm sure things could be finessed.

  • Thanks for your help. I do have 1 attribute I could use in a group by clause, but I suspect it won't help much (only 2 values in the field, and the split is 90% / 10%). If spliting the multipolygon in individual polygons using ST_Dump or similar would help I can definitely do it, but from what I can tell this would happen after the massive multigeometry is created anyway so not sure it would help? also, my geometries are not in lat-long, so if I want to use geohash I believe I need to convert the coordinates first... is there an alternative? – Jahfet Aug 24 '12 at 11:16
  • Just wrap in a call to transform, ST_GeoHash(ST_Transform(geom, 4326)) – Paul Ramsey Aug 24 '12 at 21:07
  • Thanks Paul. Your solution worked (almost) perfectly. It is a bit slow to run on my server (I think I still have work to do to optimise memory usage, it now never ramps up!) but it does the job! I had to remove the last step (groupedfinal) because for some reason it crashes (Error message: GEOSUnaryUnion: St9bad_alloc - even though I call ST_Union not ST_UnaryUnion, and even if I ST_Dump() the multipolygons before the final union to simplify the geometries). However I managed to run that step separately in arcgis so that is not a big issue. – Jahfet Aug 31 '12 at 14:16

While it appears to be possible to do it completely in PostGIS easily, in such cases I would likely just whip together a brief batch script to do the OFFSET incrementing. It's a bit trickier on windows, but the general syntax idea is:

FOR %BATCH IN (0 500000 ... last_offset) DO psql.exe ... 

For a better hint about invocation and avoiding password prompting, check this question.

  • Thanks. This looks pretty simple indeed. I will fall back to this if my attempts are doing it through a sql loop fail (quite likely given my level!) – Jahfet Aug 24 '12 at 12:37

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.