ECMWF - Revision history

Stacy shinneman: /* Historical data */

2018-02-22T10:26:17Z

‎Historical data

Stacy shinneman: /* ECMWF */

2018-02-20T11:39:53Z

‎ECMWF

Judy: /* Helpful Functions & Queries */

2016-03-02T16:04:50Z

‎Helpful Functions & Queries

Willem bouten: /* FORECAST and UVA_FORECAST */

2015-10-06T16:38:23Z

‎FORECAST and UVA_FORECAST

Judy: /* FORECAST and UVA_FORECAST */

2015-10-06T12:46:16Z

‎FORECAST and UVA_FORECAST

Judy: /* Helpful Functions & Queries */

2015-01-29T14:24:24Z

‎Helpful Functions & Queries

Judy: /* Helpful Functions & Queries */

2013-06-11T15:38:11Z

‎Helpful Functions & Queries

Judy: /* Helpful Functions & Queries*/

2013-06-11T15:03:19Z

‎Helpful Functions & Queries

Judy: /* Interpolation */

2011-05-03T08:19:02Z

‎Interpolation

Bart: /* ANALYSIS */

2010-01-25T16:44:12Z

‎ANALYSIS

@@ Line 201: / Line 201: @@
 ANALYSISHISTORY which inherit from FORECAST and ANALYSIS respectively.
-The FORECASTHISTORY does not always contain TP (228 TP total precipitation accumulated).  Instead of this it contains the following values :
+The FORECASTHISTORY does not always contain TP (228 TP total precipitation accumulated).  Instead, it contains the following values:
 {|border=1

@@ Line 3: / Line 3: @@
 Datamodel for the tables in the ECMWF schema.
-Data is created by the ECMWF and retrieved from the KNMI ftp site as GRIB files. The original GRIB files can also be  retrieved from the flysafe server from :
+Data is created by the ECMWF and retrieved from the KNMI ftp site as GRIB files. The original GRIB files can also be  retrieved from the Flysafe server from :
 [https://flysafe-pps.grid.sara.nl/archive/knmi/ecmwf/ ECMWF Data Archive]

@@ Line 243: / Line 243: @@
 These function can for example be used in a join as follows :
-   SELECT g.ptt_id
+   SELECT g.device_info_serial
         , g.date_time
         , g.latitude
         , g.longitude
-        , g.speed
+        , g.speed_2d
         , g.altitude
         , (ecmwf.forecast_nearest( g.latitude, g.longitude,g.date_time)).*
-   FROM gps.gps_tracking_data g
+   FROM gps.ee_tracking_speed_limited g
-     , gps.individual i
+   WHERE g.device_info_serial = 1
      AND g.latitude <> 0
    ORDER BY g.date_time;
@@ Line 265: / Line 259: @@
 Although these functions were converted to PLPgSQL to speed them up, a faster ways to retrieve the same data is with (thanks to Floris Sluiters):
-   SELECT ptt_id, date_time, latitude, longitude, speed, course, altitude, (x::ecmwf.forecast).*
+   SELECT device_info_serial, date_time, latitude, longitude, speed_2d, altitude, (x::ecmwf.forecast).*
    FROM (SELECT
         g.*
@@ Line 274: / Line 268: @@
               AND f.modelts = TIMESTAMP 'epoch' + round(EXTRACT(EPOCH FROM g.date_time)
                   / (60*60*3))::integer * INTERVAL '3 hours' limit 1)::ecmwf.forecast) as x
-         FROM   gps.gps_tracking_data g
+         FROM   gps.ee_tracking_speed_limited g
-             , gps.individual i
+         WHERE g.device_info_serial = 1
-             , gps.track_session t
+             AND g.latitude <> 0
-         WHERE g.ptt_id = t.ptt_id
+             AND g.userflag = 0
            ORDER BY g.date_time) t

@@ Line 58: / Line 58: @@
 |U10||165 10U	10 meters wind U-component m/s
 |-
-|V10||166 10V	10 meters wind V-component
+|V10||166 10V	10 meters wind V-component	 m/s
 |-
 |T2||167 2T	2 metres air temperature

@@ Line 56: / Line 56: @@
 |TCC||164 TCC	total cloud coverage
 |-
-|U10||165 10U	10 meters wind U-component
+|U10||165 10U	10 meters wind U-component m/s
 |-
 |V10||166 10V	10 meters wind V-component

@@ Line 230: / Line 230: @@
-They can be used in the following way:
+They can be used in the following way, which will return all the fields in the relevant ECMWF data:
-   select * from ecmwf.forecast_nearest( 52.35983, 5.615, timestamp '2008-05-19 13:34:00');
+   SELECT * FROM ecmwf.forecast_nearest( 52.35983, 5.615, timestamp '2008-05-19 13:34:00');
-   select * from ecmwf.analysis_nearest( 52.35983, 5.615, timestamp '2008-05-19 13:34:00');
+   SELECT * FROM ecmwf.analysis_nearest( 52.35983, 5.615, timestamp '2008-05-19 13:34:00');
 These function can for example be used in a join as follows :

@@ Line 283: / Line 283: @@
    SELECT f.modelts, f.latitude, f.longitude, f.blh
-   FROM ecmwf.uva_forecast as f
+   FROM ecmwf.forecast as f
    WHERE f.latitude = round(42.718104*100/25)::integer*25 AND f.longitude = round(11.517727*100/25)::integer*25 and
      f.modelts >= '2007-08-01 12:00' and f.modelts <= '2008-08-01 12:00' and date_part('hour', f.modelts) = 12

@@ Line 221: / Line 221: @@
 |}
-== Helpful Functions ==
+== Helpful Functions & Queries ==
@@ Line 279: / Line 279: @@
            AND g.latitude <> 0
            ORDER BY g.date_time) t
 === Interpolation ===

@@ Line 282: / Line 282: @@
 === Interpolation ===
-The following functions are added so thast simple linear interpolation for a specific point in space and time can be done in Postgres.
+The following functions are added so that simple linear interpolation for a specific point in space and time can be done in Postgres.
 These are '''ecmwf.forecast_interpolate''' and '''ecmwf.analysis_interpolate''' and they take a latitude, longitude and timestamp as parameter.

@@ Line 115: / Line 115: @@
 |}
-== ANALYSIS ==
+== ANALYSIS and UVA_ANALYSIS ==
 {|border=1