[postgis-users] Server not responding

Fri Oct 7 02:32:14 PDT 2011

We will now try the python script again and terminate and open the
connection for each year in the for loop.
If this works, then it seems like there is a resource issue in Postgresql or
in one of the functions of postgis i think.
As i said. This worked without a problem before.

2011/10/7 Andreas Forø Tollefsen <andreasft at gmail.com>

> 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/fb9e7720/attachment.html>