ODP Development

The concept of an information system for organizing and sharing data between TRRP partners was conceived in approximately 2003. Following the Trinity River Record of Decision (ROD) it became clear to the agencies working on the river that a considerable amount of data would be collected over the following years and that an effective means of sharing these data between organizations was needed.

In 2004, Colin Daniel of ESSA Technologies Ltd. performed a user needs assessment, visiting most of the TRRP partner organizations to review their current and future data holdings. The report reiterated that a large amount of valuable information was silo-ed at various organizations and that collaboration would benefit from a mechanism for standardizing and sharing it.

In 2005 members of the TRRP, ESSA and the Utah State University Utah Water Research Laboratory (UWRL) were successful in securing funds from the Bureau of Reclamation Science and Technology program to start a research development program that would explore the potential for an “integrated information management system” (IIMS).

The initial vision involved a database that was designed to handle all manner of field data that was involved in making decisions at the TRRP. The intent was to support scientific analyses within the TRRP and partner organizations, and to ultimately improve the decisions that these analyses feed into.

InformationFlow

In addition, the IIMS as it was was originally envisioned was deemed to have widespread value to restoration programs beyond just the TRRP. The concept of standardizing, centralizing and sharing important scientific data could easily be leverage by other reclamation projects. With this in mind a central design principal of the IIMS was scalability, both in terms of the types of compatible data and also in the geographic setting. The San Joaquin and Klamath River Bureau of Reclamation projects were seen as potentially benefiting from the IIMS development.

jigsaw

IIMS Development started as a proof of concept using a Microsoft Access database, focusing on streamflow data from the USGS NWIS. Access was chosen because of its rapid and simple deployment. Streamflow data were high priority and just complex enough to prove the value of the database. (The Trinity River gauges had changed over the years both in terms of their ownership and which gauges were online.)

The IIMS rapidly outgrew the size limitations of Access and development shifted to use Oracle¬†instead,¬†the Bureau of Reclamation database standard. The Oracle database was centralized in the Reclamation regional office in Sacramento and accessed via custom desktop client software installed on each TRRP staff member’s computer. This strategy proved ill-fated as connectivity problems, combined with the challenges of keeping a constantly evolving database design updated in Sacramento began to overwhelm the effort to keep the IIMS advancing.

At the same time, challenges were emerging with the data proposed for inclusion in the IIMS. The prioritized list of potential data had quickly grown to include:

  • Streamflow
  • Temperature
  • Lake releases
  • Flow schedules
  • Diversions
  • Cross sections
  • Water quality
  • Bedload samples
  • Suspended sediment samples
  • Screw traps
  • Bird observations
  • Herpitudes (reptiles)
  • Realty licenses
  • Construction projects
  • Documents & meeting minutes
  • Meeting schedules and calendars
  • TRRP email management

Each of these types of information possessed their own bespoke formats. Moreover, most possessed historical data sometimes ranging back several decades with considerable variations in file format, quality control, nomenclature, units of measurement. After spending nearly a year attempting to standardize much of these data it was decided that the task was simply too large for one initiative to tackle. The very different formats were also causing the database design and user interface costs to balloon. The lack of tangible progress with the overall database had also taken its toll on the confidence of TRRP partners that the IIMS might not deliver on its original goal.

A new strategy was adopted in 2010. Instead of designing individual database tables specifically for each type of data, a generic concept of a “data package” was introduced. These packages could include any type of data, typically compressed into a single zip file and uploaded into the database. Together with a new unified search for finding all types of information in the database, the new system was renamed as the Online Data Portal (ODP). This also coincided with a shift to Microsoft SQL Server as the underlying database platform that was more closely supported by the programming language of the web user interface.

Search

In 2012 the ODP was enhanced to support the TRRP investigation plan protocol. Partner organizations could log in and create an online plan describing their proposed research activities for the upcoming year. This process was supported within the ODP by a fully interactive editing suite with dedicated sections for each section of the investigation plan proposal. Finally, in 2013 a move to the Cloud saw the ODP database and hosting migrated to Amazon EC2 cloud hosting. This enables users to host their entire ODP in a completely manageable and virtual environment, with minimal manual maintenance. New instances of the ODP can be spawned on demand.

The Final ODP is a fully operational, flexible database platform for supporting scientific programs. It can store and share practically any type of data, integrate with online maps, graph tabular data and download information in a variety of formats.