UPDATE: March 27, 2008 Xenia documentation is being migrated to http://code.google.com/p/xenia/wiki/XeniaHome

see also XeniaPackageSqlite and ObsKML and JCNotes

Changes

Changes to the schema from version 1 have been made to better support:

• vector type observations - every observation is a vector type of scalar combinations of at least 1 scalar
• quality control history - each observation is associated with a qc_id which has an associated lookup and the related who?, when?, what? metadata to the qc performed
• collections - platforms, sensors and observations can be grouped into collections which can be referenced later by their collection_id
• support tables - added a support table to help decide how observations should be listed when displayed and a support table to hold image timestamps and their associated product_id
• replication support via adding row_entry_date and row_update_date to all tables (default now()) for later surveys and differential copies of table data. I've added these columns as 'timestamp with time zone' since these timestamps are specific to the machine/timezone from which these records are generated. Note also that these row_ columns are used for purely system purposes and different from m_ (measurement) type columns which include their own m_date which is GMT and does not include a time zone.

Example Cases

• simple in-situ - sea surface temperature
• simple in-situ with datum - water level
• vector wind
• vector adcp profiler
• drifter
• species tagged
• species collection
• ctd

Source

The postgresql sql source can be downloaded here and data dictionary population here

These sql scripts are developed against a PostgreSQL^? version 8.0 or higher - you may encounter errors running against a PostgreSQL^? 7.x or previous db version.

Xenia Table Schema version 2

Schema changes

• March 7, 2008
  • on table platform made fields type_id,short_name optional, platform_handle NOT NULL
  • on table sensor made field type_id,short_name optional
  • essentially the type_id and short_name fields and '_type' tables are placeholder type fields and tables which I never really bother to utilize - things are geared off of platform_handle, sensor_id and m_type_id for the most part

Schema explanation

Vector datatype support

This schema version describes all data as a vector, with the simplest case of a single scalar being a vector with only 1 component. m_type.m_scalar_type_id describes the observation type expected in multi_obs.m_value

Simple case, vector of 1 component

For this simplest case, say air temperature in unit of measurement celsius(the specific row_id values are used just for example purposes):

obs_type.standard_name = air_temperature , uom_type.standard_name = celsius

m_scalar_type.row_id = 20 #identifies combination of obs_type = air_temperature uom_type = celsius

The table m_type would reference this simple scalar by associating

m_type.row_id = 30
m_type.num_types = 1 #a vector of 1 component
m_type.m_scalar_type_id = 20 #referencing the scalar_type combo of air_temperature in celsius

An inserted air temperature observation would be assigned the m_type = 30 with the value stored in the m_value field.

multi_obs.m_type = 30
multi_obs.m_value = 19 #degrees celsius

So multi_obs -> m_type -> m_scalar_type(s) -> obs_type(s) & uom_type(s)

Vector of 2 or more components

For vectors with 2 or more components, the 2-8th component values would be stored in the light blue section of the multi_obs table schema in the same order as their individual listing on the m_type table. Note that the column ordinal suffixes pairings below:

m_type.scalar_type_id describes multi_obs.m_value
m_type.scalar_type_id_2 describes multi_obs.m_value_2
m_type.scalar_type_id_3 describes multi_obs.m_value_3
m_type.scalar_type_id_4 describes multi_obs.m_value_4
m_type.scalar_type_id_5 describes multi_obs.m_value_5
m_type.scalar_type_id_6 describes multi_obs.m_value_6
m_type.scalar_type_id_7 describes multi_obs.m_value_7
m_type.scalar_type_id_8 describes multi_obs.m_value_8

So 'wind_speed' might be referenced as a simple scalar, but 'wind' might be referenced as a combination of 'wind_speed,wind_from_direction,wind_gust'. The m_type table would define the 'wind' vector with those associated individual scalar types(wind_speed,wind_from_direction,wind_gust) in that specific order and expected listing in the multi_obs table.

m_scalar_type.row_id = 100 #identifies combination of obs_type = wind_speed, uom_type = m_s-1
m_scalar_type.row_id = 243 #identifies combination of obs_type = wind_from_direction, uom_type = degrees_true
m_scalar_type.row_id = 123 #identifies combination of obs_type = wind_gust, uom_type = m_s-1

m_type.row_id = 40
m_type.num_types = 3 #a vector of 3 components
m_type.m_scalar_type_id = 100 #referencing the scalar_type combo of wind_speed in m_s-1
m_type.m_scalar_type_id_2 = 243 #referencing the scalar_type combo of wind_from_direction in degrees_true
m_type.m_scalar_type_id_3 = 123 #referencing the scalar_type combo of wind_gust in m_s-1

multi_obs.m_type = 40
multi_obs.m_value = 5 #wind_speed in m_s-1
multi_obs.m_value_2 = 240 #wind_from_direction in degrees_true
multi_obs.m_value_3 = 7 #wind_gust in m_s-1

Vectors can represent derived or support values

The m_type vector components need not reference only observations, they might also represent different units of measure of the same observation or other associated useful lookups derived from the observation.

The m_type table as noted above might also list the same type observation in a variety of different vector components. The idea is for the vector support to provide a quick single table lookup to whatever table query is needed with the definition of the vector captured in the data dictionary support tables.

Quality Control lookups

An optional support table lookup to keep a history of associated quality control changes to data.

Discussion1

Not shown(but should be) in the schema image is that each table has a row_id,row_entry_date and row_update_date for system tracking and replication purposes. But I would actually not use those system columns for tracking qc changes - what I think I need to do is add a 'collection_id' column to that table which would establish the qc history of records as part of the same 'collection' of records. Within a collection_id, records could be sorted by their begin_date,end_date.

Thanks
Jeremy

From: Tom Gale
Sent: Tuesday, May 01, 2007 1:34 PM
To: Cothran, Jeremy

One other issue we've had that doesn't seem like it's addressed in the simple or complex schemas is tracking not only the "realtime" that the measurement was associated with, but the last time this measurement value was updated. This is specifically so we can detect when an observation has changed due to a QA/QC process. Or can we just create another <TIMESTAMP> tag with semantically different uses?

---

more documentation later

Collections

An optional support table mechanism intended to support a collection or grouping of observations, sensors or platforms.

more documentation later

Support Columns / Tables / Triggers

An optional set of support tables to support display products, display listing order(m_type_display_order) or keeping track timestamps(timestamp_lkp) for remote sensing products or other irregularly timed products.

more documentation later

multi_obs

The following support columns have been added to the multi_obs table to support some common display needs. They are prefixed with 'd_' for 'display'. The are not currently pictured in the above schema diagram image but are part of the default schema sql download.

Note These columns are included with the version 2 schema, but if you are upgrading from something earlier and are missing these columns, the following alter sql statements should create the necessary columns.

d_label_theta integer

For MapServer^? directional font libraries, the direction is negative of the true direction(degrees from North), so 90 (degrees) becomes -90 for display purposes.

d_top_of_hour integer, d_report_hour timestamp without time zone

Note You must apply this database insert trigger and index for these columns to be active.

These two columns work together to label new observations as they are inserted with their d_report_hour and whether the new observation should be used for a top of the hour report(d_top_of_hour). The d_report_hour is associated with all observations a half hour before and after a given hour. So a d_report_hour of 12:00 would apply to all observations between 11:31 and 12:30. The d_top_of_hour indicator of '1' indicates that an observation is closest to the top of the hour among a group of observations for the same d_report_hour. Time comparisons between observations for the top of hour designation are made at the minute level, so 11:01:18 might be ignored for d_top_of_hour against an earlier 11:01:28.

The d_report_hour label can be used to group observations within the same corresponding hour period(no need to repeat select by time interval code, just use observations with the same given d_report_hour label). Within a d_report_hour grouping the d_top_of_hour flag can be used to identify a single measurement point closest to the top of hour for all given sensor_id's. This can be utilized to produce an hourly 'top of hour' product such as a shapefile/map of sst, etc.

mk_the_geom trigger for always populating the_geom column

If you've PostGIS^? enabled your multi_obs table with a geometry column(the_geom) similar to the below

select addgeometrycolumn('your_database_name_here,'multi_obs','the_geom',-1,'POINT',2);

Apply the following after insert trigger

-- JeremyCothran - 13 Dec 2006
to top