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

[karsten vennemann]

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 :-) ?

Any query examples or general  insight are  greatly appreciated .

 

Cheers

Karsten

 

Karsten Vennemann

 

Terra GIS Ltd

2119 Boyer Ave E

Seattle, WA 98112

USA

tel ++ 206 905 1711

fax ++ 925 905 1711

 

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