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 <[email protected]> wrote: > From: Paul Ramsey <[email protected]> > Subject: Re: [postgis-users] Geomunion performance advice needed > To: [email protected], "PostGIS Users Discussion" > <[email protected]> > 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, <[email protected]> > 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 > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
