back to first page ..
back to CookBook join Temperature_CCM

DROP TABLE IF EXISTS temperature.tmp_ccm;

CREATE TABLE temperature.tmp_ccm as(
SELECT  distinct on(gid) gid, gridid, sub.winter, sub.summer, 
sub.winter1977,
sub.winter1978,
sub.winter1979,
sub.winter1980,
sub.winter1981,
sub.winter1982,
sub.winter1983,
sub.winter1984,
sub.winter1985,
sub.winter1986,
sub.winter1987,
sub.winter1988,
sub.winter1989,
sub.winter1990,
sub.winter1991,
sub.winter1992,
sub.winter1993,
sub.winter1994,
sub.winter1995,
sub.winter1996,
sub.winter1997,
sub.winter1998,
sub.winter1999,
sub.winter2000,
sub.winter2001,
sub.winter2002,
sub.summer1977,
sub.summer1978,
sub.summer1979,
sub.summer1980,
sub.summer1981,
sub.summer1982,
sub.summer1983,
sub.summer1984,
sub.summer1985,
sub.summer1986,
sub.summer1987,
sub.summer1988,
sub.summer1989,
sub.summer1990,
sub.summer1991,
sub.summer1992,
sub.summer1993,
sub.summer1994,
sub.summer1995,
sub.summer1996,
sub.summer1997,
sub.summer1998,
sub.summer1999,
sub.summer2000,
sub.summer2001,
sub.summer2002,
sub.annual1977,
sub.annual1978,
sub.annual1979,
sub.annual1980,
sub.annual1981,
sub.annual1982,
sub.annual1983,
sub.annual1984,
sub.annual1985,
sub.annual1986,
sub.annual1987,
sub.annual1988,
sub.annual1989,
sub.annual1990,
sub.annual1991,
sub.annual1992,
sub.annual1993,
sub.annual1994,
sub.annual1995,
sub.annual1996,
sub.annual1997,
sub.annual1998,
sub.annual1999,
sub.annual2000,
sub.annual2001,
sub.annual2002,
min(distance) as distance, the_geom FROM (
SELECT gridid,t.winter, t.summer, 
t.winter1977,
t.winter1978,
t.winter1979,
t.winter1980,
t.winter1981,
t.winter1982,
t.winter1983,
t.winter1984,
t.winter1985,
t.winter1986,
t.winter1987,
t.winter1988,
t.winter1989,
t.winter1990,
t.winter1991,
t.winter1992,
t.winter1993,
t.winter1994,
t.winter1995,
t.winter1996,
t.winter1997,
t.winter1998,
t.winter1999,
t.winter2000,
t.winter2001,
t.winter2002,
t.summer1977,
t.summer1978,
t.summer1979,
t.summer1980,
t.summer1981,
t.summer1982,
t.summer1983,
t.summer1984,
t.summer1985,
t.summer1986,
t.summer1987,
t.summer1988,
t.summer1989,
t.summer1990,
t.summer1991,
t.summer1992,
t.summer1993,
t.summer1994,
t.summer1995,
t.summer1996,
t.summer1997,
t.summer1998,
t.summer1999,
t.summer2000,
t.summer2001,
t.summer2002,
t.annual1977,
t.annual1978,
t.annual1979,
t.annual1980,
t.annual1981,
t.annual1982,
t.annual1983,
t.annual1984,
t.annual1985,
t.annual1986,
t.annual1987,
t.annual1988,
t.annual1989,
t.annual1990,
t.annual1991,
t.annual1992,
t.annual1993,
t.annual1994,
t.annual1995,
t.annual1996,
t.annual1997,
t.annual1998,
t.annual1999,
t.annual2000,
t.annual2001,
t.annual2002,
 gid, CAST(distance(c.the_geom,t.the_geom) as decimal(15,1)) as distance, t.the_geom FROM temperature.tmp AS t
INNER JOIN ccm21.riversegments c ON ST_DWithin(c.the_geom,t.the_geom,70000)
) AS sub
GROUP BY gid, distance, gridid, winter, summer,
winter1977,
winter1978,
winter1979,
winter1980,
winter1981,
winter1982,
winter1983,
winter1984,
winter1985,
winter1986,
winter1987,
winter1988,
winter1989,
winter1990,
winter1991,
winter1992,
winter1993,
winter1994,
winter1995,
winter1996,
winter1997,
winter1998,
winter1999,
winter2000,
winter2001,
winter2002,
summer1977,
summer1978,
summer1979,
summer1980,
summer1981,
summer1982,
summer1983,
summer1984,
summer1985,
summer1986,
summer1987,
summer1988,
summer1989,
summer1990,
summer1991,
summer1992,
summer1993,
summer1994,
summer1995,
summer1996,
summer1997,
summer1998,
summer1999,
summer2000,
summer2001,
summer2002,
annual1977,
annual1978,
annual1979,
annual1980,
annual1981,
annual1982,
annual1983,
annual1984,
annual1985,
annual1986,
annual1987,
annual1988,
annual1989,
annual1990,
annual1991,
annual1992,
annual1993,
annual1994,
annual1995,
annual1996,
annual1997,
annual1998,
annual1999,
annual2000,
annual2001,
annual2002,
 the_geom
); 

-- if no column in geometry column add it using
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'temperature', 'tmp_ccm', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM temperature.tmp_ccm LIMIT 1;



-- création d'index, clé primaire, et contraintes qui vont bien
alter table temperature.tmp_ccm ADD CONSTRAINT c_pk_gid PRIMARY KEY (gid);
alter table temperature.tmp_ccm add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table temperature.tmp_ccm add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table temperature.tmp_ccm add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);

CREATE INDEX indextmp_ccm ON temperature.tmp_ccm
  USING GIST ( the_geom GIST_GEOMETRY_OPS );