[postgis-users] Geomunion performance advice needed

[pcreso at pcreso.com]

Thanks Paul...

I sort of figured that would be the answer... But I think it will take longer to make that happen than to run my jobs...

One thing I'm considering is parallelising the generation of the intermediate polygons, so my list of X values is split 4 ways & I run it on 4 cores.

But I'll start off trying to get the upgrade started...


Thanks again,

  Brent



--- On Tue, 9/22/09, Paul Ramsey <pramsey at cleverelephant.ca> wrote:

> From: Paul Ramsey <pramsey at cleverelephant.ca>
> Subject: Re: [postgis-users] Geomunion performance advice needed
> To: pcreso at pcreso.com, "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Date: Tuesday, September 22, 2009, 10:13 AM
> Upgrade. We're talking orders of
> magnitude here.
> 
> P
> 
> On Mon, Sep 21, 2009 at 3:03 PM,  <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
> >
>