[postgis-users] merging geometries of buffer on large data set

Martin Davis mtnclimb at telus.net
Sun Jun 30 10:03:41 PDT 2013

Hi, Karsten.

One potential problem with merging all the buffers for a given value is 
that you may wind up with a very large polygon that creates its own 
handling issues.

If you only need this for analytical purposes, not for display, then it 
might work better to aim for a middle ground.  You could merge the 
buffers in "clumps".  To do this, you need to partition the buffers into 
spatially-coherent groups.   A simple way to do this is to create a grid 
over the area (say based on the coordinate system you are using).  
Create a reprsentative point for each buffer (e.g. the interior point or 
centroid).  The buffers can then be assigned to the grid cell their 
point lies in.  Then group the buffers by their grid cell, and union 
each group.

Actually, if you want to carry on and create single polygons, then the 
clumped buffer polygons give a better basis to work from (since they 
should have many fewer points than the source polygons).

If there are still memory issues with the clumped unions, then you can 
carry out this process in an iterated fashion, starting with small grid 
cells and then repeating with a larger size.  (At this point you will 
have basically reimplemented the GEOS CascadedUnion algorithm.  But 
that's ok - performing the algorithm at the SQL level allows more 
control over memory and processing usage).

I'd be interesting to hear if this approach works out.


On 6/28/2013 5:11 PM, karsten vennemann wrote:
> I was wondering if there is a good (or best practice) approach on how 
> to merge geometry features that are touching or overlapping and  have 
> one common value in one table field.
> Here is what I was trying to do: given a large dataset such as the 
> (detailed NHD data layer) of rivers in California I created multiple 
> buffers and inserted the results into a new table with one geometry 
> column adding a score value to each of the same buffers distances 
> used. Thus the buffer polygon layer has a score with a value of 
> 10,100,500 and 1000 m corresponding to the buffer distance used. Given 
> the approach I used  to create the buffers those are often spatially 
> overlapping (because there was no merge operation of the buffers and 
> because the rivers are split along the flow line in multiple segments 
> by node in the source shape file). The resulting layer works ok for my 
> purposes (which is to retrieve information in which buffers a certain 
> location is intersecting it with the river buffer (results can be 
> 10,100,500 and 1000 or no intersect with the buffers).
> Now the layers is about 20 GB big disk size having a lot of 
> unnecessary geometries with are overlapping.
> How can I go about merging all the existing geometries on this huge 
> data set into a result layer that has (optimally ) only 4 polygons 
> with the result scores to find my intersects.
> When I tried some of my own approaches (e.g. using  st_collect and 
> such to do this) so far whenever I started  these sever resource 
> intensive operations soon these where aborted by the system because i 
> got some kind of out of memeonry errosr on my server (an ubuntu 
> achjien). Is there a good way to optimize this kind of query operation 
> without using 100% of my server ram so that I will not run out of 
> memory or resulting in a lengthy query that would be running for 6 
> weeks or so J?
> Any query examples or general  insight are  greatly appreciated .
> Cheers
> Karsten
> Karsten Vennemann
> Terra GIS Ltd
> 2119 Boyer Ave E
> Seattle, WA 98112
> tel ++ 206 905 1711
> fax ++ 925 905 1711
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> No virus found in this message.
> Checked by AVG - www.avg.com <http://www.avg.com>
> Version: 2013.0.2904 / Virus Database: 3184/6359 - Release Date: 05/26/13
> Internal Virus Database is out of date.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130630/b3930adb/attachment.html>

More information about the postgis-users mailing list