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.
-- DROP TABLE IF EXISTS priogridall_geom;
-- SELECT priogridall.*, priogrid.cell, priogrid.centroid INTO priogridall_geom from priogridall
-- LEFT JOIN priogrid ON priogridall.gid = priogrid.gid;
--
-- CREATE INDEX idx_priogrid_geom ON priogridall_geom USING GIST(centroid);
-- CREATE INDEX idx_priogrid_gid ON priogridall_geom USING btree(gid, gwcode, col, row, area);
-- DROP TABLE IF EXISTS borddist;
-- CREATE TABLE borddist(gid int, gwcode int, gridyear int, borddist decimal, primary key(gid, gridyear));
--
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1946, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1946 AND b.gweyear >= 1946 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1946 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1947, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1947 AND b.gweyear >= 1947 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1947 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1948, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1948 AND b.gweyear >= 1948 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1948 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1949, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1949 AND b.gweyear >= 1949 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1949 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1950, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1950 AND b.gweyear >= 1950 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1950 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1951, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1951 AND b.gweyear >= 1951 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1951 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1952, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1952 AND b.gweyear >= 1952 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1952 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1953, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1953 AND b.gweyear >= 1953 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1953 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1954, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1954 AND b.gweyear >= 1954 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1954 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1955, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1955 AND b.gweyear >= 1955 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1955 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1956, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1956 AND b.gweyear >= 1956 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1956 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1957, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1957 AND b.gweyear >= 1957 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1957 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1958, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1958 AND b.gweyear >= 1958 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1958 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1959, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1959 AND b.gweyear >= 1959 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1959 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1960, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1960 AND b.gweyear >= 1960 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1960 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1961, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1961 AND b.gweyear >= 1961 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1961 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1962, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1962 AND b.gweyear >= 1962 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1962 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1963, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1963 AND b.gweyear >= 1963 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1963 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1964, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1964 AND b.gweyear >= 1964 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1964 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1965, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1965 AND b.gweyear >= 1965 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1965 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1966, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1966 AND b.gweyear >= 1966 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1966 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1967, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1967 AND b.gweyear >= 1967 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1967 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1968, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1968 AND b.gweyear >= 1968 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1968 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1969, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1969 AND b.gweyear >= 1969 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1969 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1970, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1970 AND b.gweyear >= 1970 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1970 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1971, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1971 AND b.gweyear >= 1971 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1971 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1972, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1972 AND b.gweyear >= 1972 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1972 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1973, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1973 AND b.gweyear >= 1973 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1973 GROUP BY a.gid, a.gwcode;
INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1974, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1974 AND b.gweyear >= 1974 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1974 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1975, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1975 AND b.gweyear >= 1975 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1975 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1976, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1976 AND b.gweyear >= 1976 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1976 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1977, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1977 AND b.gweyear >= 1977 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1977 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1978, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1978 AND b.gweyear >= 1978 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1978 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1979, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1979 AND b.gweyear >= 1979 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1979 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1980, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1980 AND b.gweyear >= 1980 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1980 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1981, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1981 AND b.gweyear >= 1981 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1981 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1982, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1982 AND b.gweyear >= 1982 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1982 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1983, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1983 AND b.gweyear >= 1983 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1983 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1984, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1984 AND b.gweyear >= 1984 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1984 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1985, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1985 AND b.gweyear >= 1985 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1985 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1986, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1986 AND b.gweyear >= 1986 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1986 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1987, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1987 AND b.gweyear >= 1987 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1987 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1988, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1988 AND b.gweyear >= 1988 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1988 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1989, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1989 AND b.gweyear >= 1989 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1989 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1990, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1990 AND b.gweyear >= 1990 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1990 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1991, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1991 AND b.gweyear >= 1991 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1991 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1992, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1992 AND b.gweyear >= 1992 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1992 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1993, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1993 AND b.gweyear >= 1993 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1993 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1994, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1994 AND b.gweyear >= 1994 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1994 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1995, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1995 AND b.gweyear >= 1995 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1995 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1996, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1996 AND b.gweyear >= 1996 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1996 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1997, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1997 AND b.gweyear >= 1997 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1997 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1998, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1998 AND b.gweyear >= 1998 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1998 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 1999, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 1999 AND b.gweyear >= 1999 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 1999 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 2000, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2000 AND b.gweyear >= 2000 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 2000 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 2001, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2001 AND b.gweyear >= 2001 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 2001 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 2002, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2002 AND b.gweyear >= 2002 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 2002 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 2003, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2003 AND b.gweyear >= 2003 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 2003 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 2004, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2004 AND b.gweyear >= 2004 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 2004 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 2005, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2005 AND b.gweyear >= 2005 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 2005 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 2006, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2006 AND b.gweyear >= 2006 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 2006 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 2007, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2007 AND b.gweyear >= 2007 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 2007 GROUP BY a.gid, a.gwcode;
-- INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, 2008, MIN(ST_Distance(a.centroid, b.geom)) FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2008 AND b.gweyear >= 2008 AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = 2008 GROUP BY a.gid, a.gwcode;
--
-- CREATE INDEX idx_borddist_all ON borddist USING btree(gid, gwcode, gridyear, borddist);
--
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
