1 / 15

OOSDB with PostgreSQL at CMOP

OOSDB with PostgreSQL at CMOP. Bill Howe, Ph.D. Affiliations. Oregon Graduate Institute merged with OHSU in 2001 to form “The OGI School of Science & Engineering at Oregon Health & Science University”. Senior Research Associate

forrester-brodie
Download Presentation

OOSDB with PostgreSQL at CMOP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. OOSDB with PostgreSQL at CMOP Bill Howe, Ph.D.

  2. Affiliations Oregon Graduate Institute merged with OHSU in 2001 to form “The OGI School of Science & Engineering at Oregon Health & Science University” Senior Research Associate One of 16 “Science and Technology Centers” funded by the National Science Foundation based at OGI @ OHSU DMAC Coordinator Northwest Association of Networked Ocean Observing Systems (NANOOS) Liaison with CMOP PIs in Computer Science

  3. Goals • Build an integrated repository for observations and, increasingly, model results • Remain source agnostic • fixed moorings, UUVs, (quantitative) biological samples • external websites, model results, • Leverage PostgreSQL’s sophisticated features • Incremental improvement • Testing of pre-release underway

  4. Notes • Vectors supported uniformly with scalars with Postgres arrays • Hierarchy of spatial and temporal containment • stationary platforms vs., e.g., UUVs • Nested “Missions” • Vessel cruise is a mission • CTD casts from the vessel are also missions • “Virtual Observations” • Model extractions captured uniformly with measurements • Quality control on a per-value basis • not yet deployed

  5. “red26 was active from 1/1/2005 to 12/31/2005” “The CTD with serial number 1234 was deployed for red26’s 2005 mission with this specific configuration” “X11 is a UUV”; “red26 is a fixed station” “The CTD with serial number 1234 can be configured to report temperature measurements in degrees celsius, at a given sample rate, etc.” “On 1/1/2005 at 13:01, this deployment reported the temperature to be ‘19’” OOSDB: Schema (1/2) instrument capability units qc_value qc_assessment

  6. “CTD”; “ADP” “A specific CTD with serial number 1234 exists” “Ocean”; “Atmosphere” “A CTD is capable of measuring salinity, conduct., temperature, pressure, depth” “The temperature of the ocean can be measured” “The CTD with serial number 1234 can be configured to report temperature measurements in degrees celsius, at a given sample rate, etc.” “Temperature is a quantity that can be measured” “Temperature can be measured in degrees Celsius” OOSDB: Schema (2/2)

  7. Other Features: Unit Conversions T = a*F + b SELECT v.time, to.a*v.value + to.b as value FROM configuration d, linearconversion c, value v WHERE d.datasetid = v.datasetid AND d.variable = c.fromvariable AND d.units = c.units AND c.tovariable = ‘temp’ AND c.tounits = ‘C’

  8. Defaults cascade upwards Other Features: Containment in SpaceTime • Platform may have a fixed location • If not, the mission may be to a fixed location • If not, the deployment may be at a fixed location • If not, each measurement may be at a different location e.g., permanent moorings e.g., ORCA e.g., Casts e.g., UUVs Parent location contains its children’s locations if not null. PostgreSQL provides an elegant solution: Rules on Insert

  9. Example: Fixed Stations • Platform • One per station, e.g., ‘red26’ • Mission • One begins at each visit • Stores a spacetime ‘Envelope’; a single point in this case. • Configuration • One per (instrument, variable) • Can be reused on multiple missions • Deployment • assignment of a configuration to a mission • Value • measurements redundantly store the location information add another picture

  10. Example: Vessel • Platform • The forerunner itself, • maybe the cast devices are separate • Mission • One for each cruise • Envelope is the cruise plan as a PostGIS linestring • Configuration / Deployment • Sample rate, cast protocol, etc. • Value • Time/Location changes with each measurement

  11. Example: Oceanic Remote Chemical-optical Analyzer • Platform • The ORCA mooring itself • Mission • One for each of 3 long-term deployments • Envelope is a single point • Configuration / Deployment • Sample rate, cast protocol, etc. • Value • Location: local drift is ignored • Time: estimated from sample rate and profile time • Depth: estimated from pressure and latitude with a postgres UDF image source: ORCA (http://www.ocean.washington.edu/research/orca/)

  12. Example: Umanned Underwater Vehicles • Platform • The vehicle • Mission • Each excursion is a mission • Envelope is a linestring • Configuration / Deployment • Frequent changes • Value • Location, depth, and time all vary image source: MBARI (http://www.mbari.org/auv/)

  13. Example: External Data • Platform • Each site or service • Mission • Fine-grained: each request • Coarse-grained: each feed • Instrument • Program used for retrieval • Configuration / Deployment • Command-line arguments • Value • Location, depth, and time may vary

  14. Example: Model Extractions • Platform • Each model: ELCIRC, SELFE • Mission • A sequence of related runs • e.g, “Tilamook forecast” • Instrument • “Virtual Instruments” • Configuration / Deployment • Command-line arguments • Value • Location, depth, and time may vary

  15. Interfaces to OOSDB Apps need not understand database organization Legacy and application-specific RDBMS Standards and external applications Stations SOS SOS SOS “Logical Data Independence” Modeling Integrated Schema Ingest via Postgres Rules Egress via Views Cruises OpenDAP Enables agile development of the schema External

More Related