Pages

Saturday, July 9, 2016

World Ocean Database Project

Fig. 1 World Ocean Database (WOD)) Zones
I have the first WOD database and first table within that database working now.

It is the first of at least three tables for synchronizing Dredd Blog Zones (Fig. 2) with WOD Zones (Fig. 1).


The first order of business was a table that contained all of the WOD zone ids, zone latitudes and longitudes , as well as the Dredd Blog zone for that zone.
Fig. 2 Dredd Blog Zones

There are 648 WOD zones but only 36 Dredd Blog zones on the face of it (Fig. 2).

The quadrants and sub-quadrants in the Dredd Blog system has subsections (4 sub-quadrants,16 sub-quadrants = 720 spaces) which make it very close to the WOD system of categorizing the globe into zones.

Fig 3 SQL Schema for WOD zones table
At least in terms of size of the zones compared to the sub-quadrants.

The schema is shown in Fig. 3 and a sample query is shown in Fig. 4.

The next table will be based on the first one, allowing association of various types of information with the zone area, such as:
WOD zone: 7617
Unique Cast No: 67064
Cast Type: C
Cruise No: 11203
Country: US
Year: 1934
Day: 7
Month:8
Time: 10.37
Latitude: 61.93
Longitude: -172.27
Cast Length: 1303
Cast Type: 0
Variables: 6
Depth Levels: 4

WOD zone: 7015
Unique Cast No: 15495257
Cast Type: C
Cruise No: 34483
Country: US
Year: 2012
Day: 30
Month:12
Time: 8.39
Latitude: 3.364
Longitude: -153.819
Cast Length: 2025
Cast Type: 1
Variables: 2
Depth Levels: 66
I have the code working which generates that information from the raw WOD data I presented in an earlier post (Databases Galore - 14).
Fig. 4

Following that table, comes the table with the actual temperature, salinity, and other chemical readings.

I may have one table for each data type or category, such as temperatures at depth, salinity at depth, pollutants at depth, and so forth, so as to allow detailed and  specific investigations via SQL queries.

What I haven't mentioned is that the program will process all WOD files in a directory.

As the program becomes more and more robust, I put more files in the directory.

It will handle many files and build up the SQL databases and tables from many files with millions of casts in them.

I am moving slow enough to stay careful, to make sure of the data before it enters the SQL database.

The next post in this series is here.