This is an old revision of the document!
Case Studies
An interesting example to test in a space-time-qc. A sudden drop (10 degrees) in temp caused by front-passage gives error message in QC1. To be granted in QC2.
|
Station Gartland, Trøndelag 14 july 2009
Corresponding data series (two-days spanning).
Test Files
- …
- …
- …
Test Data
Test data sets for populating test versions of the kvalobs database:
Note the above is a plain text file. The .doc extension is used since only a limited set of extensions can be uploaded to the wiki 
. The text file comprises data extracted from the operational or historic kvalobs database in the form of lines such as:
17741;2007-04-02 06:00:00;-1;110;2007-10-03 16:39:21;402;0;0;-1;1110000000001000;7000000000000000;NULL
and may be loaded into a test version of the kvalobs database with:
$ psql kvalobs kvalobs=# COPY data FROM //The quoted ' ' Full-path-of-text-file// USING DELIMITERS ';' WITH NULL AS 'NULL';
Using generator.pl
Data Before:
kvalobs=# select * from data where obstime>'2032-03-01 07:00:00' and obstime<'2032-03-10 07:00:00' and stationID=75410 and paramid=110
;
stationid | obstime | original | paramid | tbtime | typeid | sensor | level | corrected | controlinfo | useinfo | cfailed
-----------+---------------------+----------+---------+---------------------+--------+--------+-------+-----------+------------------+------------------+---------
75410 | 2032-03-02 06:00:00 | 4 | 110 | 2032-03-02 05:39:14 | -308 | 0 | 0 | 4 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-03 06:00:00 | 12.5 | 110 | 2032-03-03 05:44:06 | -308 | 0 | 0 | 12.5 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-04 06:00:00 | 2.4 | 110 | 2032-03-04 05:39:01 | -308 | 0 | 0 | 2.4 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-05 06:00:00 | 5.4 | 110 | 2032-03-05 06:44:20 | -308 | 0 | 0 | 3.4 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-06 06:00:00 | 10.7 | 110 | 2032-03-06 05:33:03 | -308 | 0 | 0 | 10.7 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-07 06:00:00 | 1.8 | 110 | 2032-03-07 05:36:53 | -308 | 0 | 0 | 1.8 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-08 06:00:00 | 0.6 | 110 | 2032-03-08 05:39:09 | -308 | 0 | 0 | 0.6 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-09 06:00:00 | 0.7 | 110 | 2032-03-09 05:45:07 | -308 | 0 | 0 | 0.7 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-10 06:00:00 | 0.3 | 110 | 2032-03-10 05:36:04 | -308 | 0 | 0 | 15.2 | 0000000000000000 | 9999900000000000 |
(9 rows)
Run generator.pl with the following paramters:
kvalobs@pak:~/kvalobs-svn/src/kvQc2/Reference/TestDataGeneration$ ./generator.pl "2032-03-06 06:00:00" 4 75410 -308 110
This generates the folowing SQL statements that are applied to the kvalobs database to generate the test data (see below):
SELECT * FROM data WHERE obstime='2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; SELECT * FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767; UPDATE data SET original=((SELECT SUM(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)+(SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original=-1)) WHERE obstime='2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; UPDATE data SET controlinfo='9999999999992990' WHERE obstime='2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; UPDATE data SET controlinfo='9999999999992990' WHERE obstime BETWEEN date '2032-03-06 06:00:00' -interval '1 days' AND date '2032-03-06 06:00:00' -interval '0 days' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; UPDATE data SET original=-11111 WHERE obstime BETWEEN date '2032-03-06 06:00:00' -interval '1 days' AND date '2032-03-06 06:00:00' -interval '0 days' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; UPDATE data SET controlinfo='9999999999992990' WHERE obstime BETWEEN date '2032-03-06 06:00:00' -interval '2 days' AND date '2032-03-06 06:00:00' -interval '1 days' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; UPDATE data SET original=-11111 WHERE obstime BETWEEN date '2032-03-06 06:00:00' -interval '2 days' AND date '2032-03-06 06:00:00' -interval '1 days' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; UPDATE data SET controlinfo='9999999999992990' WHERE obstime BETWEEN date '2032-03-06 06:00:00' -interval '3 days' AND date '2032-03-06 06:00:00' -interval '2 days' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; UPDATE data SET original=-11111 WHERE obstime BETWEEN date '2032-03-06 06:00:00' -interval '3 days' AND date '2032-03-06 06:00:00' -interval '2 days' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; SELECT * FROM data WHERE obstime='2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767 AND (SELECT COUNT(original) FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767)=4; SELECT * FROM data WHERE obstime BETWEEN date '2032-03-06 06:00:00' - interval '3 days' AND '2032-03-06 06:00:00' AND stationid=75410 AND typeid=-308 AND paramid=110 AND original>-32767;
The following data is created:
kvalobs=# select * from data where obstime>'2032-03-01 07:00:00' and obstime<'2032-03-10 07:00:00' and stationID=75410 and paramid=110;
stationid | obstime | original | paramid | tbtime | typeid | sensor | level | corrected | controlinfo | useinfo | cfailed
-----------+---------------------+----------+---------+---------------------+--------+--------+-------+-----------+------------------+------------------+---------
75410 | 2032-03-02 06:00:00 | 4 | 110 | 2032-03-02 05:39:14 | -308 | 0 | 0 | 4 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-03 06:00:00 | -11111 | 110 | 2032-03-03 05:44:06 | -308 | 0 | 0 | 12.5 | 9999999999992990 | 9999900000000000 |
75410 | 2032-03-04 06:00:00 | -11111 | 110 | 2032-03-04 05:39:01 | -308 | 0 | 0 | 2.4 | 9999999999992990 | 9999900000000000 |
75410 | 2032-03-05 06:00:00 | -11111 | 110 | 2032-03-05 06:44:20 | -308 | 0 | 0 | 3.4 | 9999999999992990 | 9999900000000000 |
75410 | 2032-03-06 06:00:00 | 31 | 110 | 2032-03-06 05:33:03 | -308 | 0 | 0 | 10.7 | 9999999999992990 | 9999900000000000 |
75410 | 2032-03-07 06:00:00 | 1.8 | 110 | 2032-03-07 05:36:53 | -308 | 0 | 0 | 1.8 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-08 06:00:00 | 0.6 | 110 | 2032-03-08 05:39:09 | -308 | 0 | 0 | 0.6 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-09 06:00:00 | 0.7 | 110 | 2032-03-09 05:45:07 | -308 | 0 | 0 | 0.7 | 0000000000000000 | 9999900000000000 |
75410 | 2032-03-10 06:00:00 | 0.3 | 110 | 2032-03-10 05:36:04 | -308 | 0 | 0 | 15.2 | 0000000000000000 | 9999900000000000 |
(9 rows)
Finally replace “-11111” with a missing value.
Scripts work .. but need to be automated more.
Another example after more work on the test data generator.pl script:
Before
58390 | 2032-03-02 06:00:00 | 0.3 | 110 | 2032-03-02 06:25:19 | 302 | 0 | 0 | 0.3 | 1110000000001001 | 3000000000000070 |
58390 | 2032-03-03 06:00:00 | 4.5 | 110 | 2032-03-03 06:10:32 | 302 | 0 | 0 | 4.5 | 1110000000001001 | 3000000000000070 |
58390 | 2032-03-04 06:00:00 | 2.5 | 110 | 2032-03-04 06:10:54 | 302 | 0 | 0 | 2.5 | 1110000000001000 | 7000000000000000 |
58390 | 2032-03-05 06:00:00 | 8.5 | 110 | 2032-03-05 06:25:24 | 302 | 0 | 0 | 8.5 | 1110000000001000 | 7000000000000000 |
58390 | 2032-03-06 06:00:00 | 7 | 110 | 2032-03-06 06:25:24 | 302 | 0 | 0 | 7 | 1110000000001000 | 7000000000000000 |
58390 | 2032-03-07 06:00:00 | 0.2 | 110 | 2032-03-07 06:25:21 | 302 | 0 | 0 | 0.2 | 1110000000001000 | 7000000000000000 |
58390 | 2032-03-08 06:00:00 | -1 | 110 | 2032-03-08 06:25:17 | 302 | 0 | 0 | -1 | 1110000000001000 | 7000000000000000 |
58390 | 2032-03-09 06:00:00 | 2.4 | 110 | 2032-03-09 06:06:31 | 302 | 0 | 0 | 2.4 | 1110000000001000 | 7000000000000000 |
58390 | 2032-03-10 06:00:00 | 2.2 | 110 | 2032-03-10 06:25:20 | 302 | 0 | 0 | 2.2 | 1110000000001001 | 3000000000000070 |
Command
./generator.pl "2032-03-09 06:00:00" 7 58390 302 110
After
58390 | 2032-03-02 06:00:00 | 0.3 | 110 | 2032-03-02 06:25:19 | 302 | 0 | 0 | 0.3 | 1110000000001001 | 3000000000000070 |
58390 | 2032-03-03 06:00:00 | -32767 | 110 | 2032-03-03 06:10:32 | 302 | 0 | 0 | 4.5 | 9999999999992990 | 3000000000000070 | TEST-DATA-RA_RR24 original=4.5
58390 | 2032-03-04 06:00:00 | -32767 | 110 | 2032-03-04 06:10:54 | 302 | 0 | 0 | 2.5 | 9999999999992990 | 7000000000000000 | TEST-DATA-RA_RR24 original=2.5
58390 | 2032-03-05 06:00:00 | -32767 | 110 | 2032-03-05 06:25:24 | 302 | 0 | 0 | 8.5 | 9999999999992990 | 7000000000000000 | TEST-DATA-RA_RR24 original=8.5
58390 | 2032-03-06 06:00:00 | -32767 | 110 | 2032-03-06 06:25:24 | 302 | 0 | 0 | 7 | 9999999999992990 | 7000000000000000 | TEST-DATA-RA_RR24 original=7
58390 | 2032-03-07 06:00:00 | -32767 | 110 | 2032-03-07 06:25:21 | 302 | 0 | 0 | 0.2 | 9999999999992990 | 7000000000000000 | TEST-DATA-RA_RR24 original=0.2
58390 | 2032-03-08 06:00:00 | -32767 | 110 | 2032-03-08 06:25:17 | 302 | 0 | 0 | -1 | 9999999999992990 | 7000000000000000 | TEST-DATA-RA_RR24 original=-1
58390 | 2032-03-09 06:00:00 | 25.1 | 110 | 2032-03-09 06:06:31 | 302 | 0 | 0 | 2.4 | 9999999999992990 | 7000000000000000 | TEST-DATA-RA_RR24 original=2.4
58390 | 2032-03-10 06:00:00 | 2.2 | 110 | 2032-03-10 06:25:20 | 302 | 0 | 0 | 2.2 | 1110000000001001 | 3000000000000070 |
What is this data all about?
18500 | 2032-03-01 14:00:00 | 22.3 | 110 | 2032-03-01 13:50:08 | 342 | 0 | 0 | 22.3 | 1100000000000000 | 7000000000000000 |
18500 | 2032-03-01 15:00:00 | 22.2 | 110 | 2032-03-01 14:50:05 | 342 | 0 | 0 | 22.2 | 1100000000000000 | 7000000000000000 |
18500 | 2032-03-01 16:00:00 | 22.5 | 110 | 2032-03-01 15:50:15 | 342 | 0 | 0 | 22.5 | 1100000000000000 | 7000000000000000 |
18500 | 2032-03-01 17:00:00 | 22.6 | 110 | 2032-03-01 16:50:08 | 342 | 0 | 0 | 22.6 | 1100000000000000 | 7000000000000000 |
18500 | 2032-03-01 18:00:00 | 22.6 | 110 | 2032-03-01 17:50:09 | 342 | 0 | 0 | 22.6 | 1100000000000000 | 7000000000000000 |
18500 | 2032-03-01 19:00:00 | 22.6 | 110 | 2032-03-01 18:50:11 | 342 | 0 | 0 | 22.6 | 1100000000000000 | 7000000000000000 |
18500 | 2032-03-01 20:00:00 | 21.7 | 110 | 2032-03-01 19:50:04 | 342 | 0 | 0 | 21.7 | 1100000000000000 | 7000000000000000 |
18500 | 2032-03-01 21:00:00 | 19.9 | 110 | 2032-03-01 20:50:10 | 342 | 0 | 0 | 19.9 | 1100000000000000 | 7000000000000000 |A