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




== Joining the temperature with CCM layer ==
One temperature correspond to several riversegments

   - ''' Joining tmp with CCM layer'''
Dans le script ci dessous je ne garde pas la référence du gridid car je les informations utiles sont collées dans la table de jointure
{{{
#!html
<h2 style="color: orange">This is raw HTML</h2>
}}}

{{{
#!sql
-- script original
DROP TABLE IF EXISTS temperature.tmp_ccm;

CREATE TABLE temperature.tmp_ccm as(
SELECT gridid, gid, winter, summer,min(distance) as distance, the_geom FROM (
SELECT gridid, winter, summer,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)
ORDER BY gridid) AS sub
GROUP BY gridid, distance, gid, winter, summer,the_geom
); 

-- script modifié, je n'utilise plus la colonne gridid, je garde le script ci dessus car on risque d'en avoir besoin
DROP TABLE IF EXISTS temperature.tmp_ccm;

CREATE TABLE temperature.tmp_ccm as(
SELECT gid, winter, summer,min(distance) as distance, the_geom FROM (
SELECT winter, summer,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)
ORDER BY gridid) AS sub
GROUP BY gid, distance, , winter, summer,the_geom
); 

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 column pk serial PRIMARY KEY;
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