back to first page [..][[BR]] back to ["CookBook Eda"][[BR]] back to ["Temperature"][[BR]] == Extract the data from CRU with TETYN == Download Tetyn at : http://sourceforge.net/projects/tetyn/ [[BR]] Download the data at http://www.cru.uea.ac.uk/cru/data/hrg/cru_ts_2.10/data_all/ [[BR]] Last download link (Laurent 2018) http://www.cru.uea.ac.uk/data cru_ts_2_10.1901-2002.pre.Z (137MB) [[BR]] cru_ts_2_10.1901-2002.tmn.Z (118MB) [[BR]] cru_ts_2_10.1901-2002.tmp.Z (118MB) [[BR]] cru_ts_2_10.1901-2002.tmx.Z (119MB) [[BR]] Unzip the data [[BR]] Open TETYN > Climate dataset > CRU TS 2.10 [[BR]] In-out > Data folder choose the folder where you have extracted the data and the output folder[[BR]] Query : select the year, the month, parameters : temperature, temperature minimum, temperature maximum, precipitation[[BR]] Space : select the country [[BR]] source:"data/dataEDAcommun/temperature/tmn.txt" [[BR]] source:"data/dataEDAcommun/temperature/tmp.txt" [[BR]] source:"data/dataEDAcommun/temperature/tmx.txt" [[BR]] == Create the table in Postgre to import to == - '''Import the temperature tmp (temperature moyenne)''' {{{ #!sql CREATE schema temperature; DROP TABLE IF EXISTS temperature.tmp; }}} ["Create table tmp"] {{{ #!sql COPY temperature.tmp FROM 'D:/CelineJouanin/Temperature/tmp.txt' USING DELIMITERS ';' CSV HEADER; }}} - '''Import the temperature tmx (temperature maximum)''' {{{ #!sql DROP TABLE IF EXISTS temperature.tmx; }}} ["Create table tmx"] {{{ #!sql COPY temperature.tmx FROM 'D:/CelineJouanin/Temperature/tmx.txt' USING DELIMITERS ';' CSV HEADER; }}} - '''Import the temperature tmn (temperature minimum)''' {{{ #!sql DROP TABLE IF EXISTS temperature.tmn; }}} ["Create table tmn"] {{{ #!sql COPY temperature.tmn FROM 'D:/CelineJouanin/Temperature/tmn.txt' USING DELIMITERS ';' CSV HEADER; }}} == Create and Populate the geometry field == The geometry field is populated using the Longitude and Latitude fields[[BR]] The srid is : 4326 - WGS84[[BR]] - '''Geometry for the temperature tmp''' {{{ #!sql 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''' {{{ #!sql 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''' {{{ #!sql 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''' {{{ #!sql 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''' {{{ #!sql 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''' {{{ #!sql 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''' {{{ #!sql CREATE INDEX indextmp ON temperature.tmp USING GIST ( the_geom GIST_GEOMETRY_OPS ); }}} - ''' Transform geometry for the temperature tmx''' {{{ #!sql CREATE INDEX indextmx ON temperature.tmx USING GIST ( the_geom GIST_GEOMETRY_OPS ); }}} - ''' Transform geometry for the temperature tmn''' {{{ #!sql 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 == Ticket #58 The following file uses the !BaseEda loaddb and add_column method to calculate statistics The mean temperatures are chosen on the four hottest month (summer) and the three coldest month (winter) [[BR]] [[Image(source:eda/data/Docs/trac/Temperature/temperatures.jpeg,400px)]] [[BR]] source:eda/EDACCM/temperatures.R == Joining with the CCM layer == ["CookBook join Temperature_CCM"]