[postgis-users] Server not responding

Fri Oct 7 00:39:41 PDT 2011

Hi,
What i have done now is to try to run the query in PgAdmin SQL windows
instead of through the Python/Psycopg2 framework to see if that was the
problem.

This means one insert query for each year. I tried to run a limited set with
10 annual insert queries and this worked. Then i tried the rest of the
years, and this never completed. Stopped the query. Then trying with 3
years, it works. So there is a problem here with a query that runs for a
long time. It never finishes, and the cpu load is still 100 %, but it never
completes.
Could there be settings in postgresql.conf could cause this problem? As i
wrote earlier, this query had no problem finishing on our previous server.

Here is my explain analyze for one year: http://explain.depesz.com/s/xRj

The complete query is now:

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