Hi all,<div><br></div><div>I have a query that previously have been running without problems. However, after installing everything from scratch it no longer work.</div><div>Ubuntu 11.04, Postgresql 8.4.8, PostGIS 2.0 (7932), Geos 3.3.1, GDAL 1.8.1.</div>
<div><br></div><div>The script measures distances from centroids of a vector grid to the nearest international border to a neighboring country.</div><div><br></div><div>It uses psycopg2 module in python to loop through the years. So it inserts the distances for each year into the borddist table.</div>
<div><br></div><div>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.</div><div>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.</div>
<div><br></div><div>Python log and script below. </div><div>Thanks.</div><div><br></div><div>From python shell:</div><div><div>Wed Oct 05 09:40:53 2011 Calculate distance to border</div><div>Wed Oct 05 09:40:53 2011 Calculating the distance to nearest border</div>
<div>Wed Oct 05 09:40:53 2011 Creating borddist for year 1946.</div><div>Wed Oct 05 09:59:04 2011 Creating borddist for year 1947.</div><div>Wed Oct 05 10:16:49 2011 Creating borddist for year 1948.</div><div>Wed Oct 05 10:35:17 2011 Creating borddist for year 1949.</div>
<div>Wed Oct 05 10:54:33 2011 Creating borddist for year 1950.</div><div>Wed Oct 05 11:13:27 2011 Creating borddist for year 1951.</div><div>Wed Oct 05 11:32:29 2011 Creating borddist for year 1952.</div><div>Wed Oct 05 11:51:30 2011 Creating borddist for year 1953.</div>
<div>Wed Oct 05 12:10:26 2011 Creating borddist for year 1954.</div><div>Wed Oct 05 12:29:23 2011 Creating borddist for year 1955.</div><div>Wed Oct 05 12:48:34 2011 Creating borddist for year 1956.</div><div>Wed Oct 05 13:07:46 2011 Creating borddist for year 1957.</div>
<div>Wed Oct 05 13:27:01 2011 Creating borddist for year 1958.</div><div>Wed Oct 05 13:46:17 2011 Creating borddist for year 1959.</div><div>Wed Oct 05 14:05:23 2011 Creating borddist for year 1960.</div><div>Wed Oct 05 14:24:52 2011 Creating borddist for year 1961.</div>
<div>Wed Oct 05 14:44:33 2011 Creating borddist for year 1962.</div><div>Wed Oct 05 15:04:01 2011 Creating borddist for year 1963.</div></div><div>... 15.49 nothing more have happened. </div><div><br></div><div>Script:</div>
<div><br></div><div><div># Import modules</div><div>import psycopg2, time</div><div><br></div><div># Establish connection</div><div>db1 = psycopg2.connect("host=192.168.1.186 dbname=priogrid user=postgres password=postgres")</div>
<div>cur = db1.cursor()</div><div>print str(time.ctime())+ " Calculate distance to border"</div><div><br></div><div><br></div><div># Create a table with the priogridall_geo data and the centroid column from the priogrid table</div>
<div>print str(time.ctime())+ " Creating priogridall table with centroid geometry as priogridall_geom"</div><div>cur.execute("DROP TABLE IF EXISTS priogridall_geom;")</div><div>cur.execute("SELECT priogridall.*, priogrid.cell, priogrid.centroid INTO priogridall_geom from priogridall LEFT JOIN priogrid ON priogridall.gid = priogrid.gid;")</div>
<div>print str(time.ctime())+ " Creating indexes on priogridall table"</div><div>cur.execute("CREATE INDEX idx_priogrid_geom ON priogridall_geom USING GIST(centroid);")</div><div>cur.execute("CREATE INDEX idx_priogrid_gid ON priogridall_geom USING btree(gid, gwcode, col, row, area);")</div>
<div>db1.commit()</div><div># Create the borddist table</div><div>print str(time.ctime())+ " Calculating the distance to nearest border"</div><div>cur.execute("DROP TABLE IF EXISTS borddist;")</div><div>
cur.execute("CREATE TABLE borddist(gid int, gwcode int, gridyear int, borddist decimal, primary key (gid, gridyear));")</div><div>db1.commit()</div><div># For each year calculate the distance to border and insert into the borddist table</div>
<div>yearlist = range(1946, 2009, 1)</div><div>for x in yearlist:</div><div> print str(time.ctime())+ " Creating borddist for year "+str(x)+"."</div><div> cur.execute("INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, "+str(x)+", "\</div>
<div> "MIN(ST_Distance(ST_Transform(a.centroid, 954010), ST_Transform(b.geom, 954010)))/1000 "\</div><div> "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)+" "\</div>
<div> "AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")</div><div><br></div><div>cur.execute("CREATE INDEX idx_borddist_all ON borddist USING btree(gid, gwcode, gridyear, borddist);")</div>
<div>db1.commit()</div><div><br></div><div>print str(time.ctime())+ " Done"</div><div>cur.close()</div><div>db1.close()</div></div><div><br></div><div><br></div><div><br></div>