[postgis-users] Query slow down, never completes

Andreas Forø Tollefsen andreasft at gmail.com
Tue Oct 11 05:25:20 PDT 2011 

Hi all.
I have lately experiences a problem that i cannot fix. Any help would be
very very much appreciated.

*Task:*
Run a query to estimate the shortest distance from the centroid of a vector
grid cell (0.5x0.5 decimal degree), to the nearest international border of
the neighboring country for every year from 1946 to 2008.

*Problem:*
After some time, the query slow down (stop continuing). On my desktop it
runs until 1980. On my laptop it runs until 1991. Then it never continue
before it stops after a couple of days with a termination.
Usually, the script takes 20 minutes per year with projecting it to eckert
VI, and 4-5 minutes without reprojecting. I removed the reprojection of the
data to speed things up in the test queries below.

*Tables:*
One containing my global vector grid. Each cell is assigned a country code.
One table containing the country geometries. This table includes all the
border changes from 1946 to 2008. Therefore i have to run the query for each
year.
Each country is represented by a polygon, if one country changes border,
this is replaced by a new polygon. The same for new states. Each polygon has
a start and end date.
Both tables are in SRID 4326.

*Specifications:*
Postgresql 8.4.8 + Postgis 2.0.0(SVN 7959).

I have also tried Postgresql 9.1 with Postgis2.0.0(SVN 7959) and with
Postgis1.5.3.

Running this on a Dell with Intel C2D 6600 @2.4ghz, 6GB memory, 500gb
harddrive.
Also tried the same configurations on my old HP notebook with Pentium M,
1.73 Ghz, 2Gb ram.

Tried with both Ubuntu 11.04 and Xubuntu 10.10.

*Query:*
I have tried various versions of this query. I have usually tried to do all
my queries through the psycopg2 module using python scripts. This give me a
simple for loop function to do the same query for every year.
I also wrote a sql file where there is one query per year (see attachment).
The same problem occurred, and therefore i don't think the problem is the
python or psycopg2 module.
I also tried to close the db1 connection for each year in the loop, and
reopen the connection for the next year in the loop. Same problem.
I have tried both with insert into ... select .. and select into annual
tables and the put them together. Same problem.

General query:
# Import modules
import psycopg2, time

# Establish connection
db1 = psycopg2.connect("host=192.168.1.186 dbname=priogrid user=postgres
password=postgres")
cur = db1.cursor()
print str(time.ctime())+ " Calculate distance to border"


# Create a table with the priogridall_geo data and the centroid column from
the priogrid table
print str(time.ctime())+ " Creating priogridall table with centroid geometry
as priogridall_geom"
cur.execute("DROP TABLE IF EXISTS priogridall_geom;")
cur.execute("SELECT priogridall.*, priogrid.cell, priogrid.centroid INTO
priogridall_geom from priogridall LEFT JOIN priogrid ON priogridall.gid =
priogrid.gid;")
print str(time.ctime())+ " Creating indexes on priogridall table"
cur.execute("CREATE INDEX idx_priogrid_geom ON priogridall_geom USING
GIST(centroid);")
cur.execute("CREATE INDEX idx_priogrid_gid ON priogridall_geom USING
btree(gid, gwcode, col, row, area);")
db1.commit()
# Create the borddist table
print str(time.ctime())+ " Calculating the distance to nearest border"
cur.execute("DROP TABLE IF EXISTS borddist;")
cur.execute("CREATE TABLE borddist(gid int, gwcode int, gridyear int,
borddist decimal, primary key (gid, gridyear));")
db1.commit()
# For each year calculate the distance to border and insert into the
borddist table
yearlist = range(1946, 2009, 1)
*for x in yearlist:*
*    print str(time.ctime())+ " Creating borddist for year "+str(x)+"."*
*    cur.execute("INSERT INTO borddist(gid, gwcode, gridyear, borddist)
SELECT a.gid, a.gwcode, "+str(x)+", "\*
*                "MIN(ST_Distance(a.centroid, b.geom)) "\*
*                "FROM priogridall_geom a, cshapeswdate b, cshapeswdate c
WHERE a.gwcode != b.gwcode AND b.gwsyear <= "+str(x)+" AND b.gweyear >=
"+str(x)+" "\*
*                "AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom)
AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")*
    db1.commit()
cur.execute("CREATE INDEX idx_borddist_all ON borddist USING btree(gid,
gwcode, gridyear, borddist);")
db1.commit()

print str(time.ctime())+ " Done"
cur.close()
db1.close()

Another try:
# Import modules
import psycopg2, time

# Establish connection
db1 = psycopg2.connect("host=localhost dbname=priogrid user=postgres
password=postgres")
cur = db1.cursor()
print str(time.ctime())+ " Calculate distance to border"

# Create a table with the priogridall_geo data and the centroid column from
the priogrid table
print str(time.ctime())+ " Creating priogridall table with centroid geometry
as priogridall_geom"
cur.execute("DROP TABLE IF EXISTS priogridall_geom;")
cur.execute("SELECT priogridall.*, priogrid.cell, priogrid.centroid INTO
priogridall_geom from priogridall LEFT JOIN priogrid ON priogridall.gid =
priogrid.gid;")
print str(time.ctime())+ " Creating indexes on priogridall table"
cur.execute("CREATE INDEX idx_priogrid_geom ON priogridall_geom USING
GIST(centroid);")
cur.execute("CREATE INDEX idx_priogrid_gid ON priogridall_geom USING
btree(gid, gwcode, col, row, area);")
db1.commit()
# Create the borddist table
print str(time.ctime())+ " Calculating the distance to nearest border"
cur.execute("DROP TABLE IF EXISTS borddist;")
cur.execute("CREATE TABLE borddist(gid int, gwcode int, gridyear int,
borddist decimal, primary key(gid, gridyear));")
db1.commit()
# For each year calculate the distance to border and insert into the
borddist table
yearlist = range(1946, 2009, 1)
for x in yearlist:
    print str(time.ctime())+ " Creating borddist for year "+str(x)+"."
    cur.execute("DROP TABLE IF EXISTS borddist"+str(x)+";")
    cur.execute("SELECT a.gid, a.gwcode, "+str(x)+", "\
                "MIN(ST_Distance(a.centroid, b.geom)) INTO
borddist"+str(x)+" "\
                "FROM priogridall_geom a, cshapes b, cshapes c WHERE
a.gwcode != b.gwcode AND b.gwsyear <= "+str(x)+" AND b.gweyear >= "+str(x)+"
"\
                "AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom)
AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")
    db1.commit()
    cur.execute("INSERT INTO borddist SELECT * FROM borddist"+str(x)+";")
    db1.commit()
    cur.execute("DROP TABLE borddist"+str(x)+";")
    db1.commit()
cur.execute("CREATE INDEX idx_borddist_all ON borddist USING btree(gid,
gwcode, gridyear, borddist);")
db1.commit()

db1.commit()
print str(time.ctime())+ " Done"
cur.close()
db1.close()

Output python:
Tue Oct 11 10:46:12 2011 Calculate distance to border
Tue Oct 11 10:46:12 2011 Creating priogridall table with centroid geometry
as priogridall_geom
Tue Oct 11 10:46:38 2011 Creating indexes on priogridall table
Tue Oct 11 10:48:01 2011 Calculating the distance to nearest border
Tue Oct 11 10:48:01 2011 Creating borddist for year 1946.
Tue Oct 11 10:52:07 2011 Creating borddist for year 1947.
Tue Oct 11 10:56:04 2011 Creating borddist for year 1948.
Tue Oct 11 11:00:35 2011 Creating borddist for year 1949.
Tue Oct 11 11:05:33 2011 Creating borddist for year 1950.
Tue Oct 11 11:09:51 2011 Creating borddist for year 1951.
Tue Oct 11 11:14:00 2011 Creating borddist for year 1952.
Tue Oct 11 11:18:10 2011 Creating borddist for year 1953.
Tue Oct 11 11:22:20 2011 Creating borddist for year 1954.
Tue Oct 11 11:26:34 2011 Creating borddist for year 1955.
Tue Oct 11 11:30:46 2011 Creating borddist for year 1956.
Tue Oct 11 11:34:57 2011 Creating borddist for year 1957.
Tue Oct 11 11:39:10 2011 Creating borddist for year 1958.
Tue Oct 11 11:43:24 2011 Creating borddist for year 1959.
Tue Oct 11 11:47:45 2011 Creating borddist for year 1960.
Tue Oct 11 11:52:03 2011 Creating borddist for year 1961.
Tue Oct 11 11:56:17 2011 Creating borddist for year 1962.
Tue Oct 11 12:01:05 2011 Creating borddist for year 1963.
Tue Oct 11 12:05:27 2011 Creating borddist for year 1964.
Tue Oct 11 12:10:18 2011 Creating borddist for year 1965.
Tue Oct 11 12:15:11 2011 Creating borddist for year 1966.
Tue Oct 11 12:20:02 2011 Creating borddist for year 1967.
Tue Oct 11 12:24:54 2011 Creating borddist for year 1968.
Tue Oct 11 12:29:45 2011 Creating borddist for year 1969.
Tue Oct 11 12:34:38 2011 Creating borddist for year 1970.
Tue Oct 11 12:38:56 2011 Creating borddist for year 1971.
Tue Oct 11 12:43:53 2011 Creating borddist for year 1972.
Tue Oct 11 12:48:46 2011 Creating borddist for year 1973.
Tue Oct 11 12:53:38 2011 Creating borddist for year 1974.
Tue Oct 11 12:58:31 2011 Creating borddist for year 1975.
Tue Oct 11 13:03:32 2011 Creating borddist for year 1976.
Tue Oct 11 13:08:33 2011 Creating borddist for year 1977.
Tue Oct 11 13:13:34 2011 Creating borddist for year 1978.
Tue Oct 11 13:18:32 2011 Creating borddist for year 1979.
Tue Oct 11 13:22:56 2011 Creating borddist for year 1980.
14.22, nothing have happened.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111011/08b424e0/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: borddist.sql
Type: text/x-sql
Size: 22476 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111011/08b424e0/attachment.bin>

More information about the postgis-users mailing list