[postgis-users] Geomunion performance advice needed

[Paul Ramsey]

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
>