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

Extract the data from CRU with TETYN

Download Tetyn at : ​http://sourceforge.net/projects/tetyn/
Download the data at ​http://www.cru.uea.ac.uk/cru/data/hrg/cru_ts_2.10/data_all/
cru_ts_2_10.1901-2002.pre.Z (137MB)
cru_ts_2_10.1901-2002.tmn.Z (118MB)
cru_ts_2_10.1901-2002.tmp.Z (118MB)
cru_ts_2_10.1901-2002.tmx.Z (119MB)

Unzip the data

Open TETYN > Climate dataset > CRU TS 2.10

In-out > Data folder choose the folder where you have extracted the data and the output folder
Query : select the year, the month, parameters : temperature, temperature minimum, temperature maximum, precipitation
Space : select the country

source:"data/dataEDAcommun/temperature/tmn.txt"
source:"data/dataEDAcommun/temperature/tmp.txt"
source:"data/dataEDAcommun/temperature/tmx.txt"

Create the table in Postgre to import to

• Import the temperature tmp (temperature moyenne)

  CREATE schema temperature;
  DROP TABLE IF EXISTS temperature.tmp;
  

  Create table tmp

  COPY temperature.tmp FROM 'D:/Celine Jouanin/Temperature/tmp.txt' USING DELIMITERS ';' CSV HEADER;
  

• Import the temperature tmx (temperature maximum)

  DROP TABLE IF EXISTS temperature.tmx;
  

  Create table tmx

  COPY temperature.tmx FROM 'D:/Celine Jouanin/Temperature/tmx.txt' USING DELIMITERS ';' CSV HEADER;
  

• Import the temperature tmn (temperature minimum)

  DROP TABLE IF EXISTS temperature.tmn;
  

  Create table tmn

  COPY temperature.tmn FROM 'D:/Celine Jouanin/Temperature/tmn.txt' USING DELIMITERS ';' CSV HEADER;
  

Create and Populate the geometry field

The geometry field is populated using the Longitude and Latitude fields
Problem : I haven't yet found the spatial reference (maybe the srid is : 4326 - WGS84)

• Geometry for the temperature tmp

  select AddGeometryColumn('temperature','tmp','the_geom',4326,'POINT',2);
  UPDATE temperature.tmp
          SET the_geom = ST_SetSRID(ST_Point( longitude, latitude),4326);
  

• Geometry for the temperature tmx

  select AddGeometryColumn('temperature','tmx','the_geom',4326,'POINT',2);
  UPDATE temperature.tmx
          SET the_geom = ST_SetSRID(ST_Point( longitude, latitude),4326);
  

• Geometry for the temperature tmn

  select AddGeometryColumn('temperature','tmn','the_geom',4326,'POINT',2);
  UPDATE temperature.tmn
          SET the_geom = ST_SetSRID(ST_Point( longitude, latitude),4326);
  

Transforming to Another spatial reference system

• Transform geometry for the temperature tmp

  ALTER TABLE temperature.tmp DROP COLUMN the_geom CASCADE;
  SELECT AddGeometryColumn('temperature','tmp','the_geom',3035,'POINT',2);
  UPDATE temperature.tmp SET the_geom = transform(setsrid(makepoint(longitude, latitude),4326), 3035);
  

• Transform geometry for the temperature tmx

  ALTER TABLE temperature.tmx DROP COLUMN the_geom CASCADE;
  SELECT AddGeometryColumn('temperature','tmx','the_geom',3035,'POINT',2);
  UPDATE temperature.tmx SET the_geom = transform(setsrid(makepoint(longitude, latitude),4326), 3035);
  

• Transform geometry for the temperature tmn

  ALTER TABLE temperature.tmn DROP COLUMN the_geom CASCADE;
  SELECT AddGeometryColumn('temperature','tmn','the_geom',3035,'POINT',2);
  UPDATE temperature.tmn SET the_geom = transform(setsrid(makepoint(longitude, latitude),4326), 3035);
  

Create a GIST index to increase a query speed

• Transform geometry for the temperature tmp

  CREATE INDEX indextmp ON temperature.tmp
    USING GIST ( the_geom GIST_GEOMETRY_OPS );
  

• Transform geometry for the temperature tmx

  CREATE INDEX indextmx ON temperature.tmx
    USING GIST ( the_geom GIST_GEOMETRY_OPS );
  

• Transform geometry for the temperature tmn

  CREATE INDEX indextmn ON temperature.tmn
    USING GIST ( the_geom GIST_GEOMETRY_OPS );
  

Full script (for cedric change location c:/base according to where you placed the files... source:trunk/EDAcommun/sql/temperature.sql

Transmitting file data ...

Calculation of summer and winter temperatures

The following file uses the BaseEda? loaddb and add_column method to calculate statistics The mean temperatures are source:trunk/EDACCM/temperatures.R

Joining with the CCM layer

CookBook join Temperature_CCM