| Added: | ||||||||
| > > |
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.
| ||||||||
|
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.
| ||||||||||||||||||||||||||||
| Line: 22 to 22 | ||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Added: | ||||||||||||||||||||||||||||
| > > |
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 | ||||||||||||||||||||||||||||
| Added: | ||||||||||||||||||||||||||||
| > > |
| |||||||||||||||||||||||||||
| 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. | ||||||||
| Added: | ||||||||
| > > |
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); | |||||||
| -- JeremyCothran - 31 Jan 2025 | ||||||||
| Line: 1 to 1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| Added: | ||||||||
| > > |
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. -- JeremyCothran - 31 Jan 2025 | |||||||