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,
 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.

http://nautilus.baruch.sc.edu/longbay/realtime.php

Sample data file

Column layout:

yymmdd 
hhmmss 
Temperature (oC) 
Conductivity(uS/cm) 
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 http://caro-coops.org/bb/viewtopic.php?t=330)

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

PHP graph page and stylesheet

Script to produce monthly csv from database

-- JeremyCothran - 31 Jan 2025
to top