[postgis-users] Geomunion performance advice needed

[Martin Davis]

The reason for the improved performance of geomunion in newer versions 
of PostGIS is that it takes advantage of the Cascaded Union approach in 
GEOS.  You can approximate that functionality at the SQL level - see 
this for a guide:

http://trac.osgeo.org/postgis/wiki/UsersWikiCascadeUnion

pcreso at pcreso.com wrote:
> Hi,
>
> I have a jopb which looks like taking 15 days... perhaps someone here has some ideas on how to speed things up.
>
> My query is intended to generate polygons from points with a depth attribute. I buffer the points so they all overlap & geomunion these into a single multipolygon. My base dataset is 155,000,000 points.
>
> As a single monolithic query, it works on smaller regions within the overall area, but slows down as the area of interest increases (as you'd expect). Based on tests I've done previously, a series of smaller areas processed sequenntially & then combined is faster than the monolithic approach. 
>
> I have stored (& indexed) the X value of each point, so my script firstly generates a list of the X values within the user defined bounding box, then iterates through this list building a multipolygon for each longitude value, as below.
>
>  psql -d $DB -qc "insert into depth_poly values
>                   (
>                    default,
>                    'TEMP',
>                    '$USER',
>                    $MINDEPTH,
>                    $MAXDEPTH,
>                    '$DESC',
>                    (select multi(setsrid(buffer(geomunion(geom),0.0065,2)),4326)
>                     from gebco_depths
>                     where geom && $ROSS_BOX
>                       and x_val=$LON
>                       and depth >= $MINDEPTH
>                       and depth <= $MAXDEPTH)
>                   );"                    
>
>
> After this all the sub polygons are geomunion()-ed to generate a single multipolygon defining the regions between specified depths within the specified bbox. This can then be overlaid on administrative boundaries for various further assessments to be made.
>
> I'm not using current versions of PostGIS or Postgres (I'm not really in control of this), so I'm also interested in any suggestions as to how much impact an apgrade might make for this sort of query (based on my understanding of recent upgrades, this could be substantial).
>
> gebco08=# select postgis_full_version();
>                                 postgis_full_version
> -------------------------------------------------------------------------------------
>  POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.1, 21 August 2008" USE_STATS
> (1 row)
> gebco08=# select version();
>                                                       version
> -------------------------------------------------------------------------------------------------------------------
>  PostgreSQL 8.1.11 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070115 (prerelease) (SUSE Linux)
> (1 row)
>
> Hardware is an 8 core Xeon 3Ghz server with 16Gb memory, & the queries are getting around 100% cpu on 1 core.
>
> Any advice gratefully accepted.
>   
>   Brent Wood
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022