Xenia - DMAC standard submission
Jeremy Cothran
Suggested Name: Xenia Relational Database Schema
Submission Date: 05/27/2009 02:51 PM
Type of Standard, guidance or best practice (select one).
New
Data Management Function(s)
Archive, Metadata, Data Transport, Data Discovery, QA/QC, Other
Abstract:
Xenia is the name of a minimally common relational database schema which can be used for various geospatial observation oriented efforts with an in-situ datatype focus such as buoys,stations,ships,gliders (not recommended for densely gridded coverages such as hf radar, model output or satellite imagery). Such a common schema and associated controlled vocabularies can be used as an aggregation point from which a variety of other formats, services and products can be generated using a variety of openly shared scripts.
Purpose/Scope:
This is an implementation/infrastructure component - a minimally common and hopefully simple to populate and utilize relational database schema - which might be helpful to other data management efforts which utilize relational databases for observation oriented data either to adopt or adapt directly for their needs or in a conceptual/design comparison process with their own existing designs or needs.
Technical Description:
Database schema originally developed with in-situ buoys, wind and water-level station aggregations with an observation-centric/mapping focus. Initial SQL schema developed for PostgreSQL(http://postgresql.org) but most recent development against Sqlite(http://sqlite.org) to help simplify infrastructure needs further. Database focused around a singular observations collection table(multi_obs) which is supported by a few metadata tables (organization,platform,sensor). Currently partitioning observation collection table(multi_obs) by julian week into separate files/tables to help manage archival concerns separately from near real-time queries.
Database also supports a default XML format for import/export of data - ObsKML (Observations KML) in addition to a variety of other output formats and services detailed athttp://code.google.com/p/xenia/wiki/VMwareProducts .
Entire development stack is also available as a vmware image for adoption/adaption using virtualized server technologies, seehttp://code.google.com/p/xenia/wiki/VMwareHome
Initially script development is in perl, but other developer language script conversions/adaptations(python,java,c#,etc) are welcomed.
Additional Information:
Xenia project home
http://code.google.com/p/xenia/wiki/XeniaHome
Xenia Subversion(source code) folder
http://code.google.com/p/xenia/source/browse/#svn/trunk
Google group(Q&A)
http://groups.google.com/group/xeniavm
Simple schema diagram
http://code.google.com/p/xenia/wiki/XeniaPackageSqlite#Simplified_schema
Powerpoint poster
http://carocoops.org/documents/sccc_poster_JCothran.ppt
Powerpoint presentation
http://carocoops.org/documents/sccc_presentation_JCothran.ppt
Kiosk presentation
http://www.carocoops.org/documents/sccc/kiosk/cothran-usc-sc08-Kiosk-audio/
Youtube video
http://www.youtube.com/watch?v=_CO9o2DyVV0&fmt=6
Output formats, services
http://code.google.com/p/xenia/wiki/VMwareProducts
Virtualized server
http://code.google.com/p/xenia/wiki/VMwareHome
Statutory Requirement:
No
Relationship/Dependency/Conflicts:
Adopted for use within RA(Regional Associations) directly:
GLOS/GLIN Great Lakes Information Network (GLIN)
http://gis.glin.net
Developers Pete Giencke, Guan Wang
NANOOS
Developer Emilio Mayorga
SECOORA
http://secoora.org/data
Reviewed for schema consideration:
AOOS
Developer Rob Cermak
GCOOS
Developer Felimon Gayanilo
Dr. Matt Howard
Oostethys
http://www.oostethys.org/development/meetings/meetings
and utilized by several ongoing projects:
CarolinasRCOOS
http://carolinasrcoos.org
Developers Xiaoyan Qi,Dan Ramage,Jeremy Cothran
NWS Marine Weather Portal
http://forecast.weather.gov/mwp/
Developer Charlton Galvarino
Intelligent River
http://www.intelligentriver.org/data-products
Developer Charlton Galvarino
Dr. David White
The output scripts associated with this schema support a variety of already common interchange formats (CSV,shapefile,KML,JSON,GeoRSS) in popular usehttp://code.google.com/p/xenia/wiki/VMwareProducts and the SOS (Sensor Observation Service) web service interoperability efforts underwayhttp://code.google.com/p/xenia/wiki/VMwareProducts#SOS
| Name | URL |
| Name: | Jeremy Cothran |
| Organization Name: | SECOORA |
| Telephone: | 803-777-4469 |
| E-Mail Address: | jeremy....@gmail.com |
| Member | Organization | E-Mail Address | Telephone Number |
Emilio Mayorga
It was good to see that you've taken this step. As that process moves
forward, I think it'll be useful to add some discussion on how your
original xenia data model (or model*s*, as you've evolved it in
different versions) has been adapted by others. For example, I've
tried not to introduce too many changes in my implementation, but I
still ended up adding a good chunk of optional components, making some
small modifications, or using existing features in ways that may not
have been your original intent. The mere fact that you use xenia as a
temporary pass-thru repository of data from all sensors in your area
of interest is different from our usage, where it'll store only
NANOOS-managed platforms+sensors and will serve as a complete archive
with historical data (or at least until we hit limits in the future!).
BTW, I'm using python for all data loading, if you want to add that to
your docs. I'll eventually add all this info (and code) to the NANOOS
pages on the Xenia wiki. I'm not using your intermediate ObsKML; I go
straight from the original data files to the database. It was easier
to get going this way, though an intermediate, generic data model
(whether a file or just an on-the-fly representation in the data
loading code) makes a lot of sense.
Cheers,
-Emilio
On Wed, May 27, 2009 at 1:50 PM, Jeremy Cothran
<jeremy....@gmail.com> wrote:
> Hi all,
> Went ahead and wrote up the below initial submission for the Xenia
> relational database schema for the DMAC standard review process
> at http://ioosdmac.fedworx.org . Hopefully this will help bring further
> consideration and feedback to those using this tool in their toolbox of
> potential solutions. I mention several projects and names in the 'Current
> Usage' section, please let me know if there are any additions or edits that
> should be made regarding that content or other content in the submission.
> Thanks so much to everyone for your ideas and feedback with the Xenia
> related efforts and other efforts which we're all learning as we go. Really
> great to work with a community of so many talented people.
> Cheers
> Jeremy
>
> General
>
> Suggested Name: Xenia Relational Database Schema
>
> Submission Date: 05/27/2009 02:51 PM
>
> Type of Standard, guidance or best practice (select one).
> New
>
> Data Management Function(s)
> Archive, Metadata, Data Transport, Data Discovery, QA/QC, Othervisualization
> and mapping
>
>
> Summary
>
> Abstract:
>
> Xenia is the name of a minimally common relational database schema which can
> be used for various geospatial observation oriented efforts with an in-situ
> datatype focus such as buoys,stations,ships,gliders (not recommended for
> densely gridded coverages such as hf radar, model output or satellite
> imagery). Such a common schema and associated controlled vocabularies can be
> used as an aggregation point from which a variety of other formats, services
> and products can be generated using a variety of openly shared scripts.
>
> Purpose/Scope:
>
> This is an implementation/infrastructure component - a minimally common and
> hopefully simple to populate and utilize relational database schema - which
> might be helpful to other data management efforts which utilize relational
> databases for observation oriented data either to adopt or adapt directly
> for their needs or in a conceptual/design comparison process with their own
> existing designs or needs.
>
> Technical Description:
> Database schema originally developed with in-situ buoys, wind and
> water-level station aggregations with an observation-centric/mapping focus.
> Initial SQL schema developed for PostgreSQL(http://postgresql.org) but most
> recent development against Sqlite(http://sqlite.org) to help simplify
> infrastructure needs further. Database focused around a singular
> observations collection table(multi_obs) which is supported by a few
> metadata tables (organization,platform,sensor). Currently partitioning
> observation collection table(multi_obs) by julian week into separate
> files/tables to help manage archival concerns separately from near real-time
> queries.
>
> Database also supports a default XML format for import/export of data -
> ObsKML (Observations KML) in addition to a variety of other output formats
> and services detailed athttp://code.google.com/p/xenia/wiki/VMwareProducts .
>
> Entire development stack is also available as a vmware image for
> adoption/adaption using virtualized server technologies,
> seehttp://code.google.com/p/xenia/wiki/VMwareHome
>
> Initially script development is in perl, but other developer language script
> conversions/adaptations(python,java,c#,etc) are welcomed.
>
>
>
> Additional Information:
> Xenia project home
> http://code.google.com/p/xenia/wiki/XeniaHome
>
> Xenia Subversion(source code) folder
> http://code.google.com/p/xenia/source/browse/#svn/trunk
>
> Google group(Q&A)
> http://groups.google.com/group/xeniavm
>
> Simple schema diagram
> http://code.google.com/p/xenia/wiki/XeniaPackageSqlite#Simplified_schema
>
> Powerpoint poster
> http://carocoops.org/documents/sccc_poster_JCothran.ppt
>
> Powerpoint presentation
> http://carocoops.org/documents/sccc_presentation_JCothran.ppt
>
> Kiosk presentation
> http://www.carocoops.org/documents/sccc/kiosk/cothran-usc-sc08-Kiosk-audio/
>
> Youtube video
> http://www.youtube.com/watch?v=_CO9o2DyVV0&fmt=6
>
> Output formats, services
> http://code.google.com/p/xenia/wiki/VMwareProducts
>
> Virtualized server
> http://code.google.com/p/xenia/wiki/VMwareHome
>
>
>
> Statutory Requirement:
> No
>
>
>
> Relationship/Dependency/Conflicts:
>
>
>
> Current Usage
> The output scripts associated with this schema support a variety of already
> common interchange formats (CSV,shapefile,KML,JSON,GeoRSS) in popular
> usehttp://code.google.com/p/xenia/wiki/VMwareProducts and the SOS (Sensor
> Observation Service) web service interoperability efforts
> underwayhttp://code.google.com/p/xenia/wiki/VMwareProducts#SOS
>
>
> References
>
> NameURL
>
> Acronyms
>
>
> Contact Information
> Name: Jeremy Cothran
> Organization Name:SECOORA
> Telephone: 803-777-4469
> E-Mail Address:jeremy....@gmail.com
>
> Supporting Parties/Members
>
> MemberOrganization E-Mail AddressTelephone Number
>
> Comments History
>
jcothran
idea of how the database should be populated, but once those concepts
are in place going from raw data directly to SQL is fine.
On the output side I probably should focus more on skipping ObsKML as
an intermediate as well, just using a CSV resultset instead which
would probably be easier for Eric or others with a RDB to create a
similar output for processing (like the current IOOS DIF script).
My initial concept was to use ObsKML as an intermediate buffer layer
to the RDB import/export, trying to support those that might be more
familiar with XML instead of SQL/RDB, but I think enough folks utilize
the SQL/RDB,CSV approach where the XML layer just gets in the way.
Thanks
Jeremy
On Jun 3, 3:48 pm, Emilio Mayorga <emiliomayo...@gmail.com> wrote:
> Hi Jeremy,
>
> It was good to see that you've taken this step. As that process moves
> forward, I think it'll be useful to add some discussion on how your
> original xenia data model (or model*s*, as you've evolved it in
> different versions) has been adapted by others. For example, I've
> tried not to introduce too many changes in my implementation, but I
> still ended up adding a good chunk of optional components, making some
> small modifications, or using existing features in ways that may not
> have been your original intent. The mere fact that you use xenia as a
> temporary pass-thru repository of data from all sensors in your area
> of interest is different from our usage, where it'll store only
> NANOOS-managed platforms+sensors and will serve as a complete archive
> with historical data (or at least until we hit limits in the future!).
>
> BTW, I'm using python for all data loading, if you want to add that to
> your docs. I'll eventually add all this info (and code) to the NANOOS
> pages on the Xenia wiki. I'm not using your intermediate ObsKML; I go
> straight from the original data files to the database. It was easier
> to get going this way, though an intermediate, generic data model
> (whether a file or just an on-the-fly representation in the data
> loading code) makes a lot of sense.
>
> Cheers,
>
> -Emilio
>
> On Wed, May 27, 2009 at 1:50 PM, Jeremy Cothran
>