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_id SERIAL PRIMARY KEY,
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,
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
% Saturation of Dissolved Oxyzen
Dissolved Oxygen (mg/l)
Remaining Power (%)
to push ascii files to database
to create webpage latest readout status
to drive graph creation (see earlier documentation at http://caro-coops.org/bb/viewtopic.php?t=330
) to create graph using gnuplot
PHP graph page
to produce monthly csv from database
- 31 Jan 2006