-- MonishaKanoth - 19 May 2006

Table of contents

Analysis of CTD & Bottle Data with Ocean Data View

Ocean Data View is a powerful tool developed by the folks at Alfred Wegener Institute-Bremerhaven that allows in-depth analysis and graphing of Oceanographic data. In addition to providing Scatter plots(property-property) plots, Surface Distributions (defining iso-surface variables), Section Plots & Maps, it also supports netCDF formats, Derived variables, Editing and Flagging questionable data and Animation. The Ocean Data View(pdf) ODV Guide serves as a good resource to understanding the functionality this flexible, easy-to-use tool has to offer.

For beginners with Ocean Data View, click here for Getting Started with Ocean Data View(pdf)(right hand sidebar).

To view Initial analysis of NOAA-SCDNR MARMAP data with ODV :Introduction to using NOAA-SCDNR MARMAP data with ODV(pdf)(right hand sidebar).

Database Schema for storing CTD data

CREATE TABLE multi_obs
(
    row_id SERIAL PRIMARY KEY,
    row_entry_date TIMESTAMP ,
    row_update_date TIMESTAMP ,
    platform_id INT NOT NULL,
    sensor_id INT NOT NULL,
    m_collection_id INT,
    m_type_id INT NOT NULL,
    m_date TIMESTAMP,
    m_lon FLOAT,
    m_lat FLOAT,
    m_entry_order_id INT,
    m_z FLOAT,
    m_value FLOAT,
    m_station_depth FLOAT    
);
CREATE UNIQUE INDEX i_multi_obs ON multi_obs (m_type_id,m_collection_id,m_z,platform_id,sensor_id,m_entry_order_id); 
CREATE INDEX i_query_multi_obs on multi_obs(m_type_id,m_date);

Field Descriptions:

platform_id: corresponds to an internal platform or station id within the platform look up table.
sensor id: corresponds to an internal gear or sensor id within sensor look up table.
m_type_id: corresponds to an internal measurement type id for each observation type(eg. temp, salinity,nitrate etc)
m_z: depth at observation value.

• Multi Obs Table Schema File: multi_obs_schema.txt

Lookup Tables

Look up tables exist for Collection Ids (lut_collection_id),Measurement Type Id(lut_m_type_id),Platform Id(lut_platform_id) and Sensor Id(lut_sensor_id).
The lookup table for the collection ids maps the cast collection id to a numeric value. (The NOAA-SCDNR MARMAP project from 1973-2005 has 3073 distinct collection ids).

• Collection Id Look up table schema: lut_collection_id_schema.txt
• Lookup table for Measurement Type Id (lut_m_type_id)= lut_m_type_id_data.txt
• Lookup table for Platform Id (lut_platform_id) : lut_platform_id_data.txt
• Lookup table for Sensor Id (lut_sensor_id): lut_sensor_id_data.txt

In the look up table for measurement type id(lut_m_type_id) each measurement type is mapped to an integer. Eg. Temperature(C)-> 1.

---

Translating CTD Data to WOCE(World Ocean Circulation Experiment) Exchange Format for Hydrographic Data

Click on this link for information on WHPO Exchange formats:An Improved Exchange Format for WOCE Hydrographic Program Data Files

Ocean Data View supports data import in WHP exchange format(Bottle data). This format includes cast information such as Castno, lat, lon, station number, observations & observation units; and format specific information such as Expocode and Bottle in the data file.
Each observation type (temp,salinity etc.)is translated to a cross tabular format from the multi obs table schema for the exchange format.

Crosstabular Format

Steps to obtain data in the crosstabular format is all follows:

SQL similar to the one below can be used for tables/views for each observation type.

Eg. Salinity: View

CREATE VIEW salinity AS
    SELECT DISTINCT (((((((multi_obs.m_collection_id)::text || '_'::text) || (multi_obs.m_entry_order_id)::text) || '_'::text) 
       || (multi_obs.m_z)::text) || '_'::text) || (multi_obs.m_date)::text) AS id, multi_obs.m_value AS salinity 
    FROM multi_obs 
    WHERE (multi_obs.m_type_id = 3) 
    ORDER BY (((((((multi_obs.m_collection_id)::text || '_'::text) 
       || (multi_obs.m_entry_order_id)::text) || '_'::text) || (multi_obs.m_z)::text) || '_'::text) 
       || (multi_obs.m_date)::text), multi_obs.m_value;

• Salinity View Schema : salinity_schema.txt

In order to create the cross tabular format we create a unique id table where the id is a concatenation of collection_id,depth and entry order.

• Unique Id View Schema : unique_id_schema.txt

CrossTabular^? Table/View

SQL to create view Crossdata:

CREATE VIEW crossdata AS
    SELECT unique_id.platform_id, unique_id.sensor_id, unique_id.m_collection_id,
       unique_id.m_date, unique_id.m_lon,unique_id.m_lat, unique_id.m_entry_order_id,
       unique_id.m_z, unique_id.m_station_depth, temperature.temperature, salinity.salinity,
       oxygen.oxygen, pressure.pressure, phosphate.phosphate, nitrate.nitrate, nitrite.nitrite,
       fluorometer.fluorometer 
    FROM ((((((((unique_id LEFT JOIN salinity ON ((unique_id.id = salinity.id))) 
    LEFT JOIN pressure ON ((unique_id.id = pressure.id))) 
    LEFT JOIN oxygen ON ((unique_id.id = oxygen.id))) 
    LEFT JOIN temperature ON ((unique_id.id = temperature.id))) 
    LEFT JOIN nitrate ON ((unique_id.id = nitrate.id))) 
    LEFT JOIN fluorometer ON ((unique_id.id = fluorometer.id))) 
    LEFT JOIN phosphate ON ((unique_id.id = phosphate.id))) 
    LEFT JOIN nitrite ON ((unique_id.id = nitrite.id)));

where each table/view for an observation type(temperature,fluorometer etc.) is created simlar to the the sample salinity view schema salinity_schema.txt .

• Cross Tabular Table Schema: crossdata_schema.txt

WOCE WHP Exchange Format (Bottle)

To include WOCE WHP Exchange information in the data files (.csv), the following perl script: 'make_woce_whp.pl' is used. The database parameters are read in through 'environment.xml'. For the NOAA-MARMAP SCDNR Program the collection ids are divided into four quarters (Jan-Mar, Apr-June, July-Sep, Oct-Dec).

• Make WOCE WHP Bottle Format : make_woce_whp.pl.txt

• Sample Environment XML File Structure : environment.xml

---

to top