Hi all.<div>I have lately experiences a problem that i cannot fix. Any help would be very very much appreciated.</div><div><br></div><div><b>Task:</b></div><div>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.</div>
<div><br></div><div><b>Problem:</b></div><div>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.</div>
<div>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.</div><div><br>
</div><div><b>Tables:</b></div><div>One containing my global vector grid. Each cell is assigned a country code.</div>
<div>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.</div><div>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.</div>
<div>Both tables are in SRID 4326.</div><div><br></div><div><b>Specifications:</b></div><div>Postgresql 8.4.8 + Postgis 2.0.0(SVN 7959).</div><div><br></div><div>I have also tried Postgresql 9.1 with Postgis2.0.0(SVN 7959) and with Postgis1.5.3.</div>
<div><br></div><div>Running this on a Dell with Intel C2D 6600 @2.4ghz, 6GB memory, 500gb harddrive.</div><div>Also tried the same configurations on my old HP notebook with Pentium M, 1.73 Ghz, 2Gb ram.</div><div><br></div>
<div>Tried with both Ubuntu 11.04 and Xubuntu 10.10.</div><div><br></div>
<div><b>Query:</b></div><div>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.</div>
<div>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.</div><div>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.</div>
<div>I have tried both with insert into ... select .. and select into annual tables and the put them together. Same problem.</div><div><br></div><div>General query:</div><div><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><b>for x in yearlist:</b></div><div><b> print str(time.ctime())+ " Creating borddist for year "+str(x)+"."</b></div><div><b> cur.execute("INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, "+str(x)+", "\</b></div>
<div><b> "MIN(ST_Distance(a.centroid, b.geom)) "\</b></div><div><b> "FROM priogridall_geom a, cshapeswdate b, cshapeswdate c WHERE a.gwcode != b.gwcode AND b.gwsyear <= "+str(x)+" AND b.gweyear >= "+str(x)+" "\</b></div>
<div><b> "AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")</b></div><div> db1.commit()</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></div><div><div><br></div></div><div>Another try:</div><div><div># Import modules</div>
<div>import psycopg2, time</div><div><br></div><div># Establish connection</div><div>db1 = psycopg2.connect("host=localhost 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># 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("DROP TABLE IF EXISTS borddist"+str(x)+";")</div>
<div> cur.execute("SELECT a.gid, a.gwcode, "+str(x)+", "\</div><div> "MIN(ST_Distance(a.centroid, b.geom)) INTO borddist"+str(x)+" "\</div><div> "FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode 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> db1.commit()</div><div> cur.execute("INSERT INTO borddist SELECT * FROM borddist"+str(x)+";")</div>
<div> db1.commit()</div><div> cur.execute("DROP TABLE borddist"+str(x)+";")</div><div> db1.commit()</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>db1.commit()</div><div>print str(time.ctime())+ " Done"</div><div>cur.close()</div><div>db1.close()</div></div><div><br></div><div>Output python:</div><div><div>Tue Oct 11 10:46:12 2011 Calculate distance to border</div>
<div>Tue Oct 11 10:46:12 2011 Creating priogridall table with centroid geometry as priogridall_geom</div><div>Tue Oct 11 10:46:38 2011 Creating indexes on priogridall table</div><div>Tue Oct 11 10:48:01 2011 Calculating the distance to nearest border</div>
<div>Tue Oct 11 10:48:01 2011 Creating borddist for year 1946.</div><div>Tue Oct 11 10:52:07 2011 Creating borddist for year 1947.</div><div>Tue Oct 11 10:56:04 2011 Creating borddist for year 1948.</div><div>Tue Oct 11 11:00:35 2011 Creating borddist for year 1949.</div>
<div>Tue Oct 11 11:05:33 2011 Creating borddist for year 1950.</div><div>Tue Oct 11 11:09:51 2011 Creating borddist for year 1951.</div><div>Tue Oct 11 11:14:00 2011 Creating borddist for year 1952.</div><div>Tue Oct 11 11:18:10 2011 Creating borddist for year 1953.</div>
<div>Tue Oct 11 11:22:20 2011 Creating borddist for year 1954.</div><div>Tue Oct 11 11:26:34 2011 Creating borddist for year 1955.</div><div>Tue Oct 11 11:30:46 2011 Creating borddist for year 1956.</div><div>Tue Oct 11 11:34:57 2011 Creating borddist for year 1957.</div>
<div>Tue Oct 11 11:39:10 2011 Creating borddist for year 1958.</div><div>Tue Oct 11 11:43:24 2011 Creating borddist for year 1959.</div><div>Tue Oct 11 11:47:45 2011 Creating borddist for year 1960.</div><div>Tue Oct 11 11:52:03 2011 Creating borddist for year 1961.</div>
<div>Tue Oct 11 11:56:17 2011 Creating borddist for year 1962.</div><div>Tue Oct 11 12:01:05 2011 Creating borddist for year 1963.</div><div>Tue Oct 11 12:05:27 2011 Creating borddist for year 1964.</div><div>Tue Oct 11 12:10:18 2011 Creating borddist for year 1965.</div>
<div>Tue Oct 11 12:15:11 2011 Creating borddist for year 1966.</div><div>Tue Oct 11 12:20:02 2011 Creating borddist for year 1967.</div><div>Tue Oct 11 12:24:54 2011 Creating borddist for year 1968.</div><div>Tue Oct 11 12:29:45 2011 Creating borddist for year 1969.</div>
<div>Tue Oct 11 12:34:38 2011 Creating borddist for year 1970.</div><div>Tue Oct 11 12:38:56 2011 Creating borddist for year 1971.</div><div>Tue Oct 11 12:43:53 2011 Creating borddist for year 1972.</div><div>Tue Oct 11 12:48:46 2011 Creating borddist for year 1973.</div>
<div>Tue Oct 11 12:53:38 2011 Creating borddist for year 1974.</div><div>Tue Oct 11 12:58:31 2011 Creating borddist for year 1975.</div><div>Tue Oct 11 13:03:32 2011 Creating borddist for year 1976.</div><div>Tue Oct 11 13:08:33 2011 Creating borddist for year 1977.</div>
<div>Tue Oct 11 13:13:34 2011 Creating borddist for year 1978.</div><div>Tue Oct 11 13:18:32 2011 Creating borddist for year 1979.</div><div>Tue Oct 11 13:22:56 2011 Creating borddist for year 1980.</div></div><div>14.22, nothing have happened. </div>
<div><br></div><div><br></div>