back to first page ..
back to CookBook Eda
back to Temperature

• Etape 1 : Lancer Temperature.r
• Etape 2 : Lancer la requête "Joining tmp with CCM layer"
• Etape 3 : Lancer main_ccm.r "Calcul des temperatures"

Il faut modifier la requête ci-dessous pour pouvoir importer les colonnes summer, winter et annual per year soit : 26*3 =78 colonnes !!!
N'y aurait-il pas une meilleure façon de faire ? avec plsql ou sous R ou sans passer par tmp puis tmp_ccm puis riversegments.
ci-dessous j'ai déjà intégré la variable winter1977 dans la requête
Ne pourrait-on pas faire cette requête sous R ?? #60

Joining the temperature with CCM layer

One temperature correspond to several riversegments

• Joining tmp with CCM layer

Le script ci dessous utilisant la clause distinct on permet de virer les lignes répétées sur gid

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, min(distance) as distance, the_geom FROM (
SELECT gridid,t.winter, t.summer, t.winter1977, 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, 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 );

• Joining tmx with CCM layer

TODO change to include new variables for winter or summer

DROP TABLE IF EXISTS temperature.tmx_ccm;

CREATE TABLE temperature.tmx_ccm as(
SELECT gridid, gid, min(distance) as distance, the_geom FROM (
SELECT gridid, gid, CAST(distance(c.the_geom,t.the_geom) as decimal(15,1)) as distance, t.the_geom FROM temperature.tmx AS t
INNER JOIN ccm21.riversegments c ON ST_DWithin(c.the_geom,t.the_geom,70000)
ORDER BY gridid) AS sub
GROUP BY gridid, distance, gid, the_geom
);

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

CREATE INDEX indextmx_ccm ON temperature.tmx_ccm
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

• Joining tmn with CCM layer

TODO change to include new variables for winter or summer

DROP TABLE IF EXISTS temperature.tmn_ccm;

CREATE TABLE temperature.tmn_ccm as(
SELECT gridid, gid, min(distance) as distance, the_geom FROM (
SELECT gridid, gid, CAST(distance(c.the_geom,t.the_geom) as decimal(15,1)) as distance, t.the_geom FROM temperature.tmn AS t
INNER JOIN ccm21.riversegments c ON ST_DWithin(c.the_geom,t.the_geom,70000)
ORDER BY gridid) AS sub
GROUP BY gridid, distance, gid, the_geom
);

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

CREATE INDEX indextmn_ccm ON temperature.tmn_ccm
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

Including a new column in the ccm riversegments database

We chose not to join to individual years (what would we predict then) and instead to stick to the riversegments (one temperature for each river

Joining tmp with bdmap layer

This class uses the jointable and join table attributes to get the join schema and will load the temperature of the spatial join into the ccm

• source:trunk/EDACCM/BaseEdaCCMRiversegmentstemp.R
• source:trunk/EDACCM/main_CCM.R