Skip to topic | Skip to bottom
Main.MultiObsSchemar1.4 - 30 May 2006 - 21:22 - JeremyCothrantopic end

Start of topic | Skip to actions
Update: May 30, 2006: I've been trying to reduce and simplify what we're doing in terms of data collection and sharing for ocean observations down to a single set of relational database tables and scripts. See XeniaPackage which extends more on the below schema.

Lately(January 2006) I've been rethinking some of the table structure issues. While on the one hand the one table per observation approach has been working fine, my temptation is to want to collapse these singular similar structured observation tables into one mega-table with an extra index of observation type. The advantage to this approach is hopefully easier code and database maintenance as there are less individual table references involved, but the disadvantage is also that a singular table reference can get into an all or nothing scenario when it comes to performance or problems at the database table level.

CREATE TABLE multi_obs
 row_entry_date TIMESTAMP with time zone,
 row_update_date TIMESTAMP with time zone,
 platform_id INT NOT NULL,
 sensor_id INT NOT NULL,
 m_type_id INT NOT NULL,
 m_date TIMESTAMP with time zone,
 m_lon FLOAT,
 m_lat FLOAT,
 m_z FLOAT,
 m_value FLOAT,
 m_desc VARCHAR(32),
 qc_level INT,
 qc_flag INT);

CREATE UNIQUE INDEX i_multi_obs ON multi_obs (m_type_id,m_date,platform_id, sensor_id);

Variations on the above table could be created as needed with additional columns such as adding a geospatial index column(lat,long,depth) for the latest observation map, etc.

The following is an example site using the above multi_obs table schema to generate the corresponding graphs and csv files. Perl scripts have been attached showing how the schema is referenced to produce the products.

Sample data file

Column layout:

Temperature (oC) 
Salinity (ppt) 
Pressure (psia) 
% Saturation of Dissolved Oxyzen
Dissolved Oxygen (mg/l) 
Remaining Power (%)

Script to push ascii files to database

Script to create webpage latest readout status

Script to drive graph creation (see earlier documentation at

Scripts(,graphSingleLine.lib) to create graph using gnuplot

PHP graph page and stylesheet

Script to produce monthly csv from database

-- JeremyCothran - 31 Jan 2006
to top

I Attachment Action Size Date Who Comment
h0210021.dat manage 0.6 K 10 Feb 2006 - 20:54 JeremyCothran NA manage 3.7 K 10 Feb 2006 - 20:56 JeremyCothran NA manage 4.7 K 10 Feb 2006 - 20:57 JeremyCothran NA manage 2.7 K 10 Feb 2006 - 20:58 JeremyCothran NA manage 6.7 K 10 Feb 2006 - 21:01 JeremyCothran NA
graphSingleLine.lib.txt manage 2.7 K 10 Feb 2006 - 21:01 JeremyCothran NA manage 2.1 K 10 Feb 2006 - 21:02 JeremyCothran NA
platform_graph_content.php.txt manage 4.4 K 10 Feb 2006 - 21:22 JeremyCothran NA
graph.css manage 0.7 K 10 Feb 2006 - 21:22 JeremyCothran NA

You are here: Main > MultiObsSchema

to top

Copyright © 1999-2018 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding DMCC? Send feedback