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

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

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

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

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