back to first page [..] [[BR]]
back to ["CookBook Eda"] [[BR]]
back to ["Temperature"] [[BR]]

  * 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"

[[Color(,green,Il faut modifier la requête ci-dessous pour pouvoir importer les colonnes summer, winter et annual per year soit : 26*3 =78 colonnes !!!)]][[BR]]
[[Color(,green,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.)]][[BR]]
[[Color(,green,ci-dessous j'ai déjà intégré la variable winter1977 dans la requête)]][[BR]]
[[Color(,green, 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
{{{
#!sql

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 [[BR]]
 * source:trunk/EDACCM/BaseEdaCCMRiversegmentstemp.R 
 * source:trunk/EDACCM/main_CCM.R 

[[Image(source:data/Docs/trac/Temperature/summer.jpg,600px)]] [[BR]]
[[Image(source:data/Docs/trac/Temperature/winter.jpg,600px)]] [[BR]]