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 trouve pas de moyen simple de virer les lignes mutliples
{{{
#!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
); 
-- to extract only the min distance you need a join

select t.gid, t.winter, t.summer from(
select min(distance),gid,pk from temperature.tmp_ccm
group by distance, gid,pk) as sub1
join temperature.tmp_ccm t on sub1.pk=t.pk;

-- final query used in R (loaddb method for riversegmentstemp)

SELECT r.gid, j.winter, j.summer FROM( SELECT min(distance), gid, pk FROM temperature.tmp_ccm group by distance, gid, pk) as sub1  
JOIN temperature.tmp_ccm j ON sub1.pk=j.pk  
RIGHT JOIN ccm21.riversegments r ON r.gid=j.gid 
where wso_id in (select wso_id from france.wso where area='France') 
  ORDER BY r.gid ;

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