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
