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
