Tuesday, March 14, 2017

Databases Galore - 18

Fig. 1 Single Measurement structure
I hope to update this post later today with some new graphs (building the new format tables is time consuming).

If not, then I will present them in a separate post tomorrow.

The new SQL raw (single measurements) database table structure is shown in Fig. 1, while the new individual table sizes, in terms of rows of data,  now looks like this:
WOD database (raw) tables:

rawwod_1000_vc1 : 130,540,624 rows
rawwod_1000_vc2 : 129,203,775 rows
(259,744,399 total)
rawwod_3000_vc1 : 75,010,771 rows
rawwod_3000_vc2 : 73,224,853 rows
(148,235,624 total)
rawwod_5000_vc1 : 82,497,275 rows
rawwod_5000_vc2 : 81,429,853 rows
(163,927,128 total)
rawwod_7000_vc1 : 135,118,197 rows
rawwod_7000_vc2 : 133,317,260 rows
(268,435,457 total)
rawwod_new_vc1 : 67,126,528 rows
rawwod_new_vc2 : 67,091,192 rows
(134,217,720 total)
974,560,328 rows (grand total)
Yep, we are dealing with almost a billion rows of measurements which the scientific community has shared with us.

Fig. 2a
These data are being used to generate three more tables: year, month, and day-of-year, each of which are segmented into the usual seven depth layers (0-200m, 201-400m, 401-600m, 601-800m, 801-1000m, 1001-3000m, and >3000m).

The "vc1" in the raw table names indicates temperature measurements, while the "vc2" indicates salinity measurements.

Fig. 2b
The "1000", "3000", "5000", and "7000" segments of the table names indicate which quadrant of the Earth (NW, NE, SW, SE) the measurements took place in (the "new" segment combines measurements from all four quadrants, since they are the combined WOD updates).

The raw tables are produced from raw WOD files of the CTD and PFL types that are in what I call "PI format"  (WOD Database Selection Menu).

Fig. 3a
The good news is that these three processed formats (year, month, day-of-year) will give three different views of the same measurements, the difference being how they are averaged (time-slice oriented).

All of them, of course, will still be WOD zone based.

The "yearly" is the usual Dredd Blog presentation, the "day-of-year" is all measurements from all years that have been placed into the day of year table in the proper slot (1-366 - each slot representing the day of the year when the measurement was taken).

Fig. 3b
The "monthly" view presents all measurements taken in one of twelve months over the entire span of time in which measurements have been taken and recorded in the WOD.

Comparing the three views of every measurement, combined with the others, and formatted into mean average values, hopefully gives us a broad comprehension of what is going on.

Fig. 4a

We are going to zone in.

Fig. 4b
UPDATE: I finally got the graphing dynamics up and running enough to generate a dark background and light background for one zone.

All three presentation types are used (year, month, and day-of-year).

They generate different tracks as you can see.

Next, I am going to graph the layers beginning with the equator, then working away from it as was done in a previous series (The Layered Approach To Big Water, 2, 3, 4, 5, 6).

Anyone want to venture comments as to why the same data looks so different when mean averaged to a yearly basis, a monthly basis, and a day of year basis?

The next post in this series is here, the previous post in this series is here.


  1. Fig 4b shows extraordinary temp drops in all depth save the >3000 m depth. Is this the connection to The Great Salinity Anomaly around Greenland of the late 1960's, when large flows of ice passed through Fram Strait? During that time period, many were convinced that another ice age was developing and represented an enormous threat. Cooling sea temps were the 'result' not driver of what was being observed in Greenland.

    1. Mark,

      Great observations.

      I think that as time goes on, and as we look at these three-views (year mean, monthly mean, and day-of-year mean) of the same data, differentiated by a time oriented mean-average structure (and of course segmenting it into seven depth zones) we will notice more and more of these types of idea generating scenarios.