[postgis-users] Server not responding

[Andreas Forø Tollefsen]

Hi all,

I have a query that previously have been running without problems. However,
after installing everything from scratch it no longer work.
Ubuntu 11.04, Postgresql 8.4.8, PostGIS 2.0 (7932), Geos 3.3.1, GDAL 1.8.1.

The script measures distances from centroids of a vector grid to the nearest
international border to a neighboring country.

It uses psycopg2 module in python to loop through the years. So it inserts
the distances for each year into the borddist table.

However, the problem after the reinstall is that it usually loops through
some years, and then everything stops. The usual processing time is ~20
minutes per year.
Could this be a memory issue in postgresql.conf? Or is there a problem i am
not aware of? This used to work on the previous installation, but we wanted
to update the server to latest.

Python log and script below.
Thanks.

>From python shell:
Wed Oct 05 09:40:53 2011 Calculate distance to border
Wed Oct 05 09:40:53 2011 Calculating the distance to nearest border
Wed Oct 05 09:40:53 2011 Creating borddist for year 1946.
Wed Oct 05 09:59:04 2011 Creating borddist for year 1947.
Wed Oct 05 10:16:49 2011 Creating borddist for year 1948.
Wed Oct 05 10:35:17 2011 Creating borddist for year 1949.
Wed Oct 05 10:54:33 2011 Creating borddist for year 1950.
Wed Oct 05 11:13:27 2011 Creating borddist for year 1951.
Wed Oct 05 11:32:29 2011 Creating borddist for year 1952.
Wed Oct 05 11:51:30 2011 Creating borddist for year 1953.
Wed Oct 05 12:10:26 2011 Creating borddist for year 1954.
Wed Oct 05 12:29:23 2011 Creating borddist for year 1955.
Wed Oct 05 12:48:34 2011 Creating borddist for year 1956.
Wed Oct 05 13:07:46 2011 Creating borddist for year 1957.
Wed Oct 05 13:27:01 2011 Creating borddist for year 1958.
Wed Oct 05 13:46:17 2011 Creating borddist for year 1959.
Wed Oct 05 14:05:23 2011 Creating borddist for year 1960.
Wed Oct 05 14:24:52 2011 Creating borddist for year 1961.
Wed Oct 05 14:44:33 2011 Creating borddist for year 1962.
Wed Oct 05 15:04:01 2011 Creating borddist for year 1963.
... 15.49 nothing more have happened.

Script:

# 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(ST_Transform(a.centroid, 954010),
ST_Transform(b.geom, 954010)))/1000 "\
                "FROM priogridall_geom a, cshapeswdate b, cshapeswdate c
WHERE a.gwcode != b.gwcode AND b.startdate <= '"+str(x)+"/01/01' 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;")

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()
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111005/bde6b204/attachment.html>