<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><span>These tend to assume each operation is constrained to a single cell, hence parallelizable, hence undertaking an operation on multiple cells concurrently.</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span>While I'm dealing with many cells - they are all being merged into a single multipolygon feature. I can't load two cells into the same multipolygon at the same time - two writes to the same record - so can't run concurrently.<br></span></div><div><br></div><div>What may be possible is to perhaps run multiple processes on
subsets to create intermediate (larger) merged polygons which can then be merged themselves to create the final single feature.</div><div><br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;">This would probably allow better use of resources on a multi core system... at present I'm using 1.7% of memory on a 100% cpu process, so I'll look into this approach - 8 cores running concurrently giving giving close to 8x faster is useful.</div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;">Brent<br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new
york,times,serif; background-color: transparent; font-style: normal;"><br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br></div> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div dir="ltr"> <hr size="1"> <font face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> "maplabs@light42.com" <maplabs@light42.com><br> <b><span style="font-weight: bold;">To:</span></b> Brent Wood <pcreso@pcreso.com>; PostGIS Development Discussion <postgis-devel@lists.osgeo.org>; Bborie Park <dustymugs@gmail.com> <br><b><span style="font-weight: bold;">Cc:</span></b> PostGIS Users Discussion <postgis-users@lists.osgeo.org> <br> <b><span style="font-weight: bold;">Sent:</span></b> Thursday,
October 17, 2013 3:16 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [postgis-devel] ST_Union() performance problem (with possiblefunding)<br> </font> </div> <div class="y_msg_container"><br><div id="yiv2456178540"><div><style>#yiv2456178540 p{margin:0;padding:0;}</style><div>yes,
true.. but with a thorough read you might notice that the gdal_retile.py
experiment was largely ineffective, </div><div>but if you click on the link at the
top to the *next post* </div><h2 class="yiv2456178540posttitle"> Variable Buffers in
PostGIS</h2><div>you will find the one that really worked well.. in fact, we used
that 2nd post in production for months, to great effect.</div><div>The trick on one
machine was to split to work by some constant, and then make psycopg2
connections for each "bucket."</div><div><br clear="none"></div><div>This worked very well..
</div><div><br clear="none"></div><div>Since then I have experimented only a tiny bit with SPARK from
the Berkeley Amp Lab for a distributed work load on a Hadoop file system, but
that world has no GEOS (yet) </div><div><br clear="none"></div><div>--</div><div>Brian M
Hamlin</div><div>OSGeo California
Chapter</div><div>blog.light42.com</div><div><br clear="none"></div><div class="yiv2456178540yqt2192847258" id="yiv2456178540yqt20276"><div><br clear="none"><br clear="none">On Wed, 16 Oct 2013
17:28:27 -0700, Bborie Park <dustymugs@gmail.com>
wrote:<br clear="none"></div><blockquote dir="ltr" style="border-left:2px solid rgb(0, 0, 0);padding-right:0px;padding-left:5px;margin-left:5px;margin-right:0px;"><div id="yiv2456178540html-message"><div dir="ltr">Your best bet is to consider splitting the workload among several
postgresql connections.<div><br clear="none"></div><div>darkblueb had a blog post about
this...</div><div><br clear="none"></div><div><a rel="nofollow" shape="rect" target="_blank" href="http://blog.light42.com/wordpress/?p=23">http://blog.light42.com/wordpress/?p=23</a><br clear="none"></div></div><div class="yiv2456178540gmail_extra"><br clear="none"></div><div class="yiv2456178540gmail_extra"><br clear="none"></div><div class="yiv2456178540gmail_extra"><br clear="none"><div class="yiv2456178540gmail_quote">On Wed, Oct 16, 2013 at 5:21
PM, Brent Wood <span dir="ltr"><<a rel="nofollow" shape="rect" ymailto="mailto:pcreso@pcreso.com" target="_blank" href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</a>></span> wrote:<br clear="none"><blockquote class="yiv2456178540gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div><div style="font-size:12pt;font-family:times new roman, new york, times, serif;"><div>Hi, <br clear="none"></div><div><br clear="none"></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:times new roman, new york, times, serif;">
Any advice appreciated!!</div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:times new roman, new york, times, serif;"><br clear="none"></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:times new roman, new york, times, serif;">
I'm undertaking a spatial analysis using Postgis (what else would I use!!!). The
first part works well.<br clear="none"></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:times new roman, new york, times, serif;">
<br clear="none"></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:times new roman, new york, times, serif;">I take a large number (potentially millions) of lines defined
by start & end points & buffer them to create polygons. (I'm working in
lat/long EPSG:4326 but transforming to a custom equal area projection for the
buffering operation).</div>
<div style="font-style:normal;font-size:16px;background-color:transparent;font-family:times new roman, new york, times, serif;"><br clear="none"></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:times new roman, new york, times, serif;">
I generate a grid of 5x5km cells (polygons) covering the region of
interest.</div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:times new roman, new york, times, serif;"><br clear="none"></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:times new roman, new york, times, serif;">
I clip the line based polygons to the grid, so I can generate statistics for
each cell describing the lines that intersect with it, various quantitative
measures such as ST_Union() the clipped line polygons to generate a footprint in
each cell to work out how much is/is not covered, or sum the ST_Area() of the
clipped polygons grouped by cell to calculate an aggregate cover, which can be
several times the actual cell area.<br clear="none"></div><div style="font-family:times new roman, new york, times, serif;font-size:12pt;"><div style="font-family:times new roman, new york, times, serif;font-size:12pt;"><br clear="none"><br clear="none">So far so good, it works
well, the code is clear & transparent & provides a good result. At least
as good as any commercial software can do. My test
data subset is processed from scratch in about 30 minutes.<br clear="none"></div> </div>
<span><br clear="none">Now I want to ST_Union() all the cell based polygons into an overall
single multipolygon representing the footprint. The code is simple. The
performance, even with my subset, is a problem.<br clear="none"><br clear="none">I have thousands of
cell based footprint multipolygons, each potentially with thousands of vertices
to be ST_Union()ed. Runtime is weeks for an iteration. If I need separate total
footprints for 20 different species annually for 5 years, that is 100
iterations. Memory & I/O use is minimal - it is totally cpu bound.<br clear="none"><br clear="none">I
am looking at trying to simplify the polygons to be unioned to reduce the number
of vertices (& hence processing) involved, but to achieve any significant
benefit I'm having to change the shape of the polygons to ST_Union() too much.
<br clear="none"><br clear="none"><br clear="none"><br clear="none">Does anyone have any suggestions as to how this could be made
significantly faster? <br clear="none">If I
had $$ to throw at developers to work on the codebase (presumably GEOS?) could
performance be significantly improved?<br clear="none"><br clear="none"><br clear="none">Thanks,<br clear="none"><br clear="none"> Brent
Wood</span><div></div></div></div><br clear="none">_______________________________________________<br clear="none">
postgis-devel mailing list<br clear="none"><a rel="nofollow" shape="rect" ymailto="mailto:postgis-devel@lists.osgeo.org" target="_blank" href="mailto:postgis-devel@lists.osgeo.org">postgis-devel@lists.osgeo.org</a><br clear="none"><a rel="nofollow" shape="rect" target="_blank" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel</a><br clear="none"></blockquote></div><br clear="none"></div>
</div>
<br clear="none"><hr><br clear="none">
_______________________________________________<br clear="none">
postgis-devel mailing list<br clear="none">
postgis-devel@lists.osgeo.org<br clear="none"><a rel="nofollow" shape="rect" target="_blank" href="http://webmail.light42.com/hwebmail/services/go.php?url=http%3A%2F%2Flists.osgeo.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fpostgis-devel">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel</a></blockquote></div><div><br clear="none"><br clear="none"></div><div><br clear="none"></div><div><br clear="none"></div></div></div><br><br></div> </div> </div> </div></body></html>