• The multi_obs table is the only PostGIS^? enabled table. There is a trigger which auto-updates the_geom field for an inserted long and lat.

• The multi-obs table also includes an auto-update trigger to set the qc test table ignore flag to false so the qc test will be re-run on the next qc processing.

• added a support table(m_type_display_order) and population for ordering the measurement listings in various displays

• added to table organization columns url and opendap_url

• added a url field to the sensor table (each sensor could potentially link to it's own display page)

• made all varchar fields a minimum of 50 characters
• added several new uom_types, obs_types, m_types and corrected a few (see new obs_data_dictionary file)

• table of latest or past few hours of data available (small size tables)
• tables for past 2 weeks (medium size tables)
• tables for older than 2 weeks (large size tables)

Schema changes

October 28, 2025 added to table organization columns url and opendap_url

• file archives produced by month broken out by both observation type(all platforms for a specific observation type) and platform(all observation types for a specific platform). Current month is a daily appended file with past days data.

Temporal Table Design

Both schemas would use the existing strategy of using the same table type/schema(multi_obs) for holding certain time periods of data

• latest data available
• separate tables for time span of 2 days, 2 weeks, 2 months, 2 years
• file archives produced by month broken out by both observation type(all platforms for a specific observation type) and platform(all observation types for a specific platform)
• data cascades from smaller, more recent tables to larger archival tables/files

This allows queries on recent data to respond quickly and places a certain limit on how large any one table might get for indexing or backup purposes.

Simple schema

The simple schema dump of just the basic table components for capturing observation data is availabe here

A sample observation data dictionary population SQL is available here. This contains most of the currently collected observation types for the existing Seacoos database.

To install this table(assuming Postgres already installed) run

#as user postgres
createdb db_xenia_v1
##tables repopulated in following order
#install the base tables
psql -d db_xenia_v1 -f db_xenia_v1_simple_schema.sql
#if want/need, add postgis and 'the_geom' to multi_obs table
#install the observation data dictionary
psql -d db_xenia_v1 -f obs_data_dictionary.sql

I've removed any references to PostGIS^? from earlier(the multi_obs table used to contain a 'the_geom' column to store long/lat values) to help keep things simple/separate in the case a user doesn't want/need geospatial indexing or wants to use a different product/version than what I'm using.

Sample organization/platform/sensor records

A sample organization,platform_type,platform,sensor_type,sensor population SQL is available here. I haven't had time to populate more fully but it should be enough to get started with the buoy data processing example listed here and give the general idea.

A full example listing is as follows

• organization
• platform_type
• platform
• sensor_type
• sensor

To install these tables run

#organization/platform/sensor lookups
#don't run as is - for new regional areas, edit inserts to reflect regional organization, platform_type,sensor_type
##tables repopulated in following order
psql -d db_xenia_v1 -f insert_organization.sql
psql -d db_xenia_v1 -f insert_platform_type.sql
psql -d db_xenia_v1 -f insert_platform.sql
psql -d db_xenia_v1 -f insert_sensor_type.sql
psql -d db_xenia_v1 -f insert_sensor.sql

Extra schema

An extended schema which uses a trigger from the multi_obs table to activate quality control tests and user/group notification is available here

To install these extra tables run

psql -d db_xenia_v1 -f db_xenia_v1_extra_schema.sql

Both schemas would use the existing strategy of using the same table type for holding certain time periods of data - say one database holds the past 2 weeks worth of data - others hold older archival data(same table format) broken apart by yearly reference. This allows queries on recent data to respond quickly and places a certain limit on how large any one table might get for indexing or backup purposes.

http://nautilus.baruch.sc.edu/twiki_dmcc/bin/view/Main/CtdNotes

For HF Radar we'll want to track u/v vectors, so I'll probably specify a general observations table with 8 possible value columns - so that I can track from 2 to 8 associated data observation components(making an assumption that 8 columns should be generally large enough for now - could specify these additional vector components as small(1), medium(8), large(16) and so on if needed). I don't mind several empty columns for this type of scenario since when I archive data I'll use compression and unused columns should hopefully compress well.

• they are active (active flag=true)
• the test is within the effective date range (eff_date_begin to eff_date_end)
• they should not be ignored (ignore flag=false)
• the wait_interval has expired. wait_interval is measured in minutes. wait_interval expiration time = done_date time + the wait_interval time

The recurring flag is used to reset the effective date range one year ahead as it expires which enables tests to be seasonally enabled from year to year.

multi_obs table

The multi-obs table includes fields for row_entry_date and row_update_date to help determine latencies between observations made and when they are collected.

On the multi_obs table the field platform_handle is the same format as station_id referenced in earlier schemas - a concatenation of organization(short_name)_platform(short_name)_platform_type(type_name) like carocoops_CAP2_buoy

The multi_obs table is the only PostGIS^? enabled table. There is a trigger which auto-updates the_geom field for an inserted long and lat.

The multi-obs table also includes an auto-update trigger to set the qc test table ignore flag to false so the qc test will be re-run on the next qc processing.

Schema and lookup SQL source

The full schema dump using pg_dump is available here. Note that there's a lot of default Postgis stuff that I'd recommend just ignoring and just focusing on the tables listed in the above schema diagram.

A few sample observation data dictionary population SQL is available here. I haven't had time to populate this with the full Seacoos data dictionary listing, it's just a few meteorological observation types, but it should give the general idea.

History

This table schema is the latest adaptation from earlier approaches documented earlier at

http://nautilus.baruch.sc.edu/twiki_dmcc/bin/view/Main/SEACOOSCookbook2#Data_structures_canonical_forms

http://nautilus.baruch.sc.edu/twiki_dmcc/bin/view/Main/MultiObsSchema

Schema discussion

The schema as shown in the diagram above has been color-coded grouped into it's different functional aspects:

Organization/Project/Catalog

Metadata concerning the organization or institution and projects or catalogs developed within that organization.

Platforms/Sensors/Observations

Metadata concerning the platforms and sensors. Observations separately indexed by observation type(measurement type or 'm_type'), time and location.

Observations Data Dictionary

The 'm_type' table is a data dictionary which references a standard_name and unit of measure(uom).

Note the following sections are less finished and more developmental. The system could perform with just the tables above in relation to collecting and sharing observations without actually getting into further functionality like quality control test , test actions and notification.

Quality Control Tests

Tests which are periodically performed on the observations as they arrive.

Test Actions

Actions which are taken as a result of a test failure.

Email Notification

Actions which are email oriented specifying a particular message, importance and user/group to be notified.

Groups/Users

Every user belongs to their own default group. Groups can be additionally defined as a collection of users.

Conventions

column names

Tried to avoid using column names which are SQL or PostgreSQL^? reserved words using the listing at
http://www.php-editors.com/postgres_manual/p_sql-keywords-appendix.html

In some instances the reserved word was very useful, so to use the word without conflicting with it I'm prefixing these words with 'app_' like 'app_user', 'app_group'.

row_id

All tables by design use an row_id column as the primary key and do not rely on concatenated columns for primary key use. In most cases the row_id is a SERIAL type which provides for automatic indexing.

additional attributes/columns

To provide some flexibility for additional column type information, but without a dedicated table column, I've added support tables of '_attr' for sensor_attr and test_attr. These support tables provide a attribute string label and value which references a particular row_id.

-- JeremyCothran - 26 May 2025