# Presentation: 544522

## MI3 Station History Information Management System

- Jeff Arnfield
- Station History Program Manager
- National Climatic Data Center, Asheville, NC
- National Oceanic and Atmospheric Administration

- Design, Details and Directions

**Notes:**
NCDC

## MI3: Presentation Roadmap

- Background
- System Overview and Walkthrough
- Enhancements Underway
- Challenges
- Questions

**Notes:**
Station Histories have some unique attributes and challenges.  

## The Ultimate Metadata Problem

**42**

**Notes:**
A recent movie, based on a book based on a radio play, provides a wonderful example of the importance of metadata.  The Hitch Hiker’s Guide to the Galaxy involved a race of hyper-intelligent beings who built a supercomputer to answer “the ultimate question of life, the universe and everything.”  

After several million years, the computer eventually determined that the answer was “42.”

Unfortunately, the beings lacked sufficient metadata to interpret the answer, and that problem was beyond the capabilities of the computer.

## Metadata: Our Big Picture

- Observing
- Systems

- Standards

- Datasets

- Inventories

- Station
- Histories

- Satellite
- Granule

**Notes:**
Our metadata needs are more clearly defined.  The metadata we deal with can be grouped many ways, but this not-to-scale diagram helps partition it. 

Existing standards are applicable to some, but not necessarily all, metadata.  As standards evolve, coverage should increase.

Station Histories intersect with most other areas of metadata. 

## MI3: Goals

- Integrate, enhance & increase access
- Initial focus: manage station histories
- Widely accessible
- Support data ingest and access needs
- Accommodate NOAA and non-NOAA stations
- Contain wide variety of station details
- Handle new observing systems, programs and phenomena without recoding
- Track information sources, log all changes
- Integrate with inventories, other details

## First Step: Document Imaging

- Much station info available only on paper
- Reviewed, collated and imaged station info documents
- 500 different forms, 50 commonly used
- 450,000 documents
- 750,000 pages
- 37,000 stations
- Images available on the web using WSSRD
- Web Store Search Retrieve Display
- Commercial service of Information Manufacturing Corporation
- Security via individual user accounts, controlled by NCDC
- Used by NOAA, state climatologists, others
- Privacy concerns limit access
- Content will be incorporated into database

## WSSRD: Station History Images

## Starting State: Standalone Station Sets

- Variety of station repositories
- usually designed for specific task, project, system
- systems, spreadsheets, ASCII files
- Both formal and ad hoc, updated and static
- variety of information sources
- variable freshness, accuracy, detail
- some lacked historical values
- Varying accessibility and usability
- Updates did not propagate to all systems
- Systems contained conflicting values
- Seldom integrated with related information

## The nature of the subject matter

- Subject matter is challenging
- Each station has many, many details
- Each detail may vary independently
- Each detail has its own period of validity
- Variations in station management and identification practices
- No widespread standard for handling station information
- Station practices are challenging
- Stations may participate in multiple networks and programs
- Stations may be known by different names and identifiers
- There may be multiple information sources for a given station
- Few networks provided an automated station information feed
- Frequent historical information backfill and correction

## MI3: Design Inputs

- Existing NCDC systems
- Current and projected production requirements
- Existing and projected data holdings and sources
- Available station metadata sources
- US CRN metadata requirements team output
- NCDC subject matter expert workshops
- Projects with NOAA, national, international partners

## MI3: Database Design Options

- Synchronized entries in all tables when anything changes

- Easy to find latest value
- Easier to query
- May improve performance
- Simpler to develop
- Good for batch submission

- Greater redundancy
- More difficult to detect changes
- Changes may affect many versions
- Single change could require new version

- Independent atomic values with begin and end dates

- Fewer records
- Minimizes redundancy
- Changes easy to detect
- Single update is logically propagated

- Potential performance impact
- Date management more complex
- Development more difficult

## MI3: Development Environment

- Web-based user interface
- ColdFusion
- Javascript
- Oracle stored procedures
- Oracle database
- Separate end-user query instance minimizes impact on production, increases availability

## MI3: System Organization

- Information grouped into subject areas

- Identity
- Updates
- Location
- Involved Parties
- Data Programs
- Datasets / Products

- Equipment
- Phenomena / Observing Practices
- Location Map
- Remarks
- Administrative Options

## MI3: Capabilities and Features

- Flexible search options
- One interface for query and maintenance (privilege-based)
- Tabbed interface by subject area, easily expanded
- Overview grids showing all time periods
- Drill-down capability to an integrated form view
- Further drilldown to individual “fact” level
- Generates critical production reports
- Date management functions simplify views/reports
- Direct query, CGI access from other systems

## MI3: System Security

- Initial security at the subject area tab-level
- Can configure for no access, read, read/write
- At database level, it’s either read or read/write
- Security enhancements underway
- Control update privileges at the station group and data program level
- Provides more granular database security
- Users can maintain only “their” stations

## MI3: Content

- Initial content ported from legacy system
- Many issues discovered and corrected
- More than 33,300 stations, including:
- 27,250 Cooperative stations (11740 currently open)
- 886 ASOS
- 76 CRN stations
- 160 RADAR sites
- 534 AWOS sites (others in process)
- About 5200 other stations (mostly surface, includes historical)
- Associations with 17 different datasets

## MI3: Home Page

- http://mi3.ncdc.noaa.gov

## MI3: Search Options

## MI3: Search Operators

## MI3: Additional Search Parameters

## MI3: Advanced Search Options

## MI3: Search Results

## Station Details – Identity Grid

## Station Details – Identity Grid

## Station Details – Updates Grid

## Station Details – Location Grid

## Station Details – Location Form

## Station Details – Location Drilldown

## Station Details – Locking a station

## Station Details – Location Update

## Station Details – Personnel

## Station Details – Data Products

## Station Details – Equipment

## Station Details – Equipment  Update

## Station Details – Phenomena

## Station Details – Phenomena  Update

## Station Details – Phenomena  Update

## Station Details – Map

## MI3: Getting Information

## MI3: External Access via CGI

- MI3 station details window can be instantiated by any system using a CGI call to open a web browser
- MI3 opens a station details window as GUEST user
- If multiple stations have used that ID (it happens), a list is presented so user can select the station(s) of interest
- http://arachne.ncdc.noaa.gov/mi3qry/ displaystation.cfm?idtypeabbr=ICAO&idvalue=KAVL
- Idtypeabbr is an abbreviation for the ID type
- Idvalue is a valid station ID of the specified type

## MI3: Station ID Types

- Abbreviations for ID types currently supported:
**ICAO**** **- International Civil Aeronautics Organization: 4 alphanumerics
**WBAN** - Weather Bureau Army Navy: 5 digits
**COOP** - NWS Cooperative ID: 6 digits
**FAA** - Federal Aviation Administration Call Sign: 3 characters
**WMO** - World Meteorological Organization Index Number: 5 digits
**NWSLI** - NWS Location Identifier: 3-5 alphanumerics; inconsistent for older stations
**GOES** - Geosynchronous Orbiting Satellite: format varies; used for GOES data transmission, currently entered only for CRN stations
**CRN** - Internal CRN network station ID: 4 digits
**NCDCSTNID** - NCDC Station ID: 8 digits; internal management
- List is table driven, new types easily added

## MI3: Data Flows

## MI3: Enhancements Underway

- Enhanced security – by station group, data program
- Data inventories
- Will replace a current system, providing added functionality
- Different levels of granularity
- Stations and geographic areas
- Presentation options selectable by user
- Digital image management
- Simple map of query results
- Print / export search results
- Display and maintain related remarks by subject tab
- Revised, expanded documentation

## MI3: Planned Enhancements

- GIS interface
- Additional station reports and views
- Advanced station management utilities
- Generic auto-ingest for station information
- Data collection and QC workflow interface for other networks
- Links to external information
- Collection-level FGDC metadata
- Network reference information
- Station history document images

## MI3: Content Expansion Underway

- Research, correction of existing stations
- Automated update of WMO stations
- Update geographic details via GIS
- Add details to operational networks
- Data inventories

## MI3: Content Plans

- Other NERON stations
- Add historical details to current stations
- Upper Air Stations
- Air Force Master Station Catalog
- Other NOAA stations
- Other national, international stations

## MI3: Adding A New Station Group

- Identify information source(s)
- Identify affected systems and processes
- Quantify volume of information
- Station count
- Information volume per station
- Map information to MI3, identify gaps
- Identify overlap with current stations
- Define QC requirements/ develop QC processes
- Develop ingest processes
- Manual
- Automated (if warranted/feasible)
- Define QA/QC and data entry rules, workflow and personnel
- Develop critical reports & extracts
- For operational networks
- Ensure ongoing refresh from source agency(s)
- Identify anticipated update frequency & volume

## Station History Management Issues

- Dealing with multiple networks
- Tracking Changes
- Date management
- Logically period of validity for each detail
- Constructing coherent views of the data
- Maintaining logical integrity during update
- Station ID management through the years

## MI3: Dealing with dates

- Each station may have many, many details
- Details may vary independently
- Each detail has its own period of validity
- Relational design spreads details across many tables
- Some tables may contain no detail for a station for a given time period

## Producing a simple data view....

**Notes:**
Let’s consider a simple view or report showing a station’s name, Coop ID, and time of observation for temperature and precipitation. 

Such a report might look like this.  Pretty simple... until you begin querying the database!

## ... requires joining three tables

- Join on Station Key column
- Must also match by date

- Data change independently
- No table contains all dates

- A table may not contain data for a given period

**Notes:**
A station’s details are split across multiple tables, so the report requires that three tables be combined, or joined.  The actual structures are a bit more complicated than I have time to address here, so I’ve shown a simplified version of the three tables.  

The tables are linked using a common Station Key column that appears in each table.  Each row must only be matched with its contemporaries, though, so the tables must also be linked by date.  Each row in each table contains its own begin date and end date to signify its period of validity.  As we can see, the name, ID and times of observation may change independently, and none of the tables contains all the correct begin/end date pairs for the final report.   

Note also that a table may contain no data that falls within a date range appearing in another table.  For instance, Station Phenomenon Observing Protocol is only known as far back as 1973, while we have station name and ID information beginning in 1930.  Omissions and errors in station history over the years make this the rule, not the exception.

In Structured Query Language, or SQL, a join combines data from two or more tables, matching them on common key columns.  The result only includes rows that had matching keys in all tables, though.  

An outer join permits all rows in one table to be included in the output set, regardless of whether the other tables contain a matching row.  However, an outer join alone is insufficient here because it doesn’t give us a way to derive the master list of date pairs.

## We need the dates for this view

- Build a list of dates from all tables in the view, discarding duplicates

**Notes:**
Since no single table contains all the logical date pairs in our report view, the master date list has to be a composite of dates found in all the tables that make up the view.

We begin by selecting all beginning and ending dates from each table, discarding any duplicates.

## The Context Date Pair Algorithm

- Build date pairs from list, removing those pairs for which no table in the view contains data

- The final date pairs are used to drive the joins in our output view query.  All source tables may be outer-joined to the chronology table.

**Notes:**
Once we have a list of unique dates from all tables in view, we use it to generate date pairs for the view, removing those pairs for which no table in the view contains data 

This may happen if a station closes for a period of time and then is reestablished.  Using the basic algorithm we see here, each date in our list becomes both a begin and an end date.  

The ending date that signifies the station’s closing becomes a beginning date; the beginning date that signifies the station’s reestablishment then becomes the matching end date, and we have an invalid date pair.  Two of the gray shaded rows in the center illustrate this.  None of the tables contains data between 1956 and 1963.  We address this by removing these spurious pairs. 

The final date pairs for this station for this view are used to drive the joins in our output view.

## The Context Date Pair Schema

**Notes:**
We call this a “Context” date pair algorithm because the date pairs are derived from the tables in one specific view.  They are unlikely to be the proper date pairs for other views.  Thus, the date pairs are valid in this specific context.

Here’s an Entity Relationship Diagram, or ERD, showing the tables and their relationships.  Because few people use ERDs, and even fewer enjoy reading them, let’s take a look at these tables in situ, using our sample report layout as data.

## A Sample Context

**Notes:**
Now we can see the tables in action:
We define the context in the Chronology Context table.  If the context is used directly by the system’s user interface, a system flag is set to prevent  modification or deletion.  

The data tables contributing to the context view are noted in the Context Table table.  Our example requires 3 entries (Station Name, Station ID, Station Phenomenon Observing Protocol).

The Context Table Row Type table permits us to selectively use rows in a data table based on a “type” descriptor column.  We note the column containing the “type” descriptor and the type descriptor value to include in the view.  You can see there are two entries for the Station Phenomenon Observing Protocol table, one for the TEMPERATURE phenomenon and one for PRECIPITATION.  Dates found in rows for other phenomena observed at the station, like WIND and HUMIDITY, are ignored.

The Station Chronology Context  table merely links a station to a context.  The build date is used by stored procedures to determine whether a context must be rebuilt for a given station.

The logical begin/end date pairs we created go into the Station Chronology table. This table drives the query that produces our report. Each data table is outer joined to this one, so that any available data is pulled from all tables, but absence of data in any table does not cause entire composite rows to be omitted from the view.

Query performance is fast, and fortunately so is maintenance performance.  Building a context for a view containing 6 tables for 35,000 stations takes less than 15 seconds, untuned. Typically a context would be rebuilt for all stations only when it’s first defined, or after a bulk load.  For a single station, the rebuild is nearly instantaneous.

## Context Date Pair Benefits

- Enables ad hoc queries by power users
- End-users benefit because special data requests are easier to accommodate
- Common schema / body of code
- supports all system queries, views and reports
- easy to define new contexts as needed
- single maintenance point
- permits future enhancements

**Notes:**
We have a flexible system that resolved temporal problems for all SQL queries against the database.  Since the logic is contained in a common subsystem and schema, it’s easy to maintain or enhance down the line.

The technique was critical for us to present data within the system.  It also enables power users to develop ad hoc SQL queries against the system without having the solve the date problem all over again.

End users benefit too, because their special data extract requests can be accommodated using a straight SQL query, with no custom programming involved.

## Other Date Management Concerns

- Peer-level child records (siblings) should not have overlapping dates
- Context date pairs do not help with this parent/child and sibling record logical date consistency
- Currently enforced in user interface
- Developing stored procedures to handle at database level

**Notes:**
We have a flexible system that resolved temporal problems for all SQL queries against the database.  Since the logic is contained in a common subsystem and schema, it’s easy to maintain or enhance down the line.

The technique was critical for us to present data within the system.  It also enables power users to develop ad hoc SQL queries against the system without having the solve the date problem all over again.

End users benefit too, because their special data extract requests can be accommodated using a straight SQL query, with no custom programming involved.

## What, and where, is a station?

- An airport has surface, upper air and NEXRAD observing systems
- Is this one station or three?
- If it’s one station, what’s the location?
- We manage this case as three separate stations
- Each is a distinct observing system
- Any may change without affecting the others
- Equipment is different
- Locations usually differ, sometimes greatly

## A Station By Any Other ID....

- Station IDs have not been assigned and managed consistently over time
- In some networks, name changes may result in an ID change
- location and equipment may not change
- complicates data and metadata management and use
- IDs may be reassigned to other currently operating stations, leaving a fragmented record
- Sometimes there is no consistent, unchanging station identifier available.
- Stations are known by different IDs, and sometimes names, in different networks and programs

## Questions

**?**

**MI3 home page: **_[http://mi3.ncdc.noaa.gov](http://mi3.ncdc.noaa.gov/)_