# Presentation: 233212

## Show and Tell2006 Data Management ConferenceFt. Collins, CO

## Cape Cod PrototypeVelma Decker

## Tips for Time Series Analysis with NRDT

**Notes:**
Velma Decker
The NRDT does a great at storing data and metadata.  
You can easily enter and retrieve sampling data and observations on the sampling events.  
However, it is difficult to compare results from different sampling events.

## Why We Need to be Thinking Ahead About How to Calculate Differences

- Monitoring data is intended to detect changes in vital signs over time.
- Analysis of variance often requires the difference between two sets of measurements that are then averaged.

## Three Methods to Try

- Pure SQL
- Access VBA
- Punt to Excel

-   The first two methods are not easy or foolproof.  We will speed through some slides here.  You can look back at the slides and the demo code later.

## SQL Method The Microsoft Mileage Example

- Calculating mileage is a simple example similar in technique to looking for changes in vital signs over time.
- The microsoft website has an example calculation from a prior row at _[http://support.microsoft.com/?kbid=208953](http://support.microsoft.com/?kbid=208953)__ _

| Date | Mileage | Gallons |
| --- | --- | --- |
| 7/8/1999 | 12340 | 14.8  |
| 7/13/1999 | 12700  | 12.6 |
| 7/18/1999 | 13090  | 13.7  |
| 7/25/1999 | 13425  | 11.9  |

**Notes:**
The table here is the mileage table created by Microsoft.  To calculate mileage, you must look at each row and subtract the prior row.  If there were row numbers you could subtract the row where the row number was one less than the current row.  When there are no row numbers, you must find the maximum mileage that is less than the current row.

## Using a Subquery to Calculate Mileage

- SELECT mile1.Date, mile1.Mileage,
- 	(Select Max(Mileage) from MileageRecord
- 		Where Mileage < Mile1.[Mileage]) AS PrevMileage,
- 	[Mileage]-[PrevMileage] AS Elapsed,
- 	([Mileage]-[PrevMileage])/[Gallons] AS MPG
- FROM MileageRecord AS mile1;

**Notes:**
This query demonstrates the use of Aliases to start a self join.  The Previous Mileage is the maximum of all of the previous mileage measurements that is less than the current one.  This needs an increasing value like mileage.

## Difficulties with the Pure SQL Method

- Requires a row ID that is strictly ascending. (Location Name, Date in “yyyymmdd” format, and whatever.)
- Queries for the NRDT are complex, slow, and may time out with relatively small datasets.
- Requires a confusing self join on Location_ID in addition to the already confusing syntax seen in the mileage example.
- Crosstab queries can not reference queries with table aliases.  This limits subsequent processing of the data.
- *See qry_Avg_SED_Diffs for an example in the SET DEMO Database.

## Using Temporary Tables and VBA

- Recordsets and arrays can be used for time series analysis but are two dimensional.
- Creating temporary tables on the fly using VBA allows you to leverage relational data structures and SQL.
- You can check the temporary tables to see how complex calculations are working as you go.
- You can drop temporary tables as a form closes or a calculation ends.
- See the next few slides and the example code if you are interested.

**Notes:**
Skip the next three slides in the presentation since some people will be bored.

## Temporary Tables Step 1Get Set Up

- Create a form or code block to start your code.
- In a VBA sub, create connection, command, and recordset objects.
- Create an SQL String to drop any existing temporary tables.
- Set the command text of the command object equal to the SQL string.
- Execute the command.
- Trap errors for tables that do not exist.

## Temporary Tables Step 2Create Temp Tables for Working Data 

- Create an SQL string that selects the data you want in the strict sort order you need.
- Include “Select 1 as RowNumber,” in your SQL statement to add a RowNumer attribute.
- The SQL statement should select the data into a new temporary table.
- Open the temporary table into a recordset.
- Loop through the recordset and increment the row number by one each time.
- Create another SQL string to create a second temporary table that is offset from the first by adding 1 to RowNumber.

## Temporary Tables Step 3Do the Calculations and Present Results 

- Create another SQL string to join the two temporary tables to get the information you need.
- Create more temporary tables as needed.  Use the DoCmd Object to display the tables to the user. (Other ideas?)
- Drop the temporary tables at some point.
- Remember to compact your database as needed.  See the SET Demo Database for code examples.

## Reference the SET Test Database for Example Code

**Notes:**
The Sediment Elevation data (SET) is the best code to view.  
The SED cryocore data uses some stored queries and mixed techniques at this time.

## What We May Need to Consider

- Difficulties in calculating changes over time may suggest a data table design in slightly non-normal form.
- For example, having many attributes like Pin1, Pin2, Pin3 may be much faster than using one column.

## When to Punt to Excel

- If queries are so complex that you are not sure they are always working all of the time.
- If your staff wants to understand or be able modify all computations.
- When you want a visual representation of your data to better handle anomalies or large gaps in the data.
- See the Excel HAQBook.xls for ideas

## Appalachian Highlands NetworkPatrick Flaherty

## Fish Sampling Partnerships

- Kentucky Dept. of Fish and Wildlife Resources

- Conservation Fisheries Inc.

- NPS

- Herp Inventory Cooperator, Retired KY Fish and Wildlife

- TVA

- APPALACHIAN HIGHLANDS INVENTORY AND MONITORING NETWORK

**Fish Sampling Partnerships**

**Notes:**
APHN
Presenting: Patrick Flaherty, APHN Data Manager
Electroshocking and Snorkeling Fish Inventory: BISO float trip September 2005, two nights, three days: Canoes, backpack shocker, dipnets, snorkeling around T&E species areas:
The slide is to highlight the cooperation with state, federal, and private cooperators, other trips had Tennessee Wildlife Resources Agency.  State of VA provided extra boat shocker for James River on BLRI.  At no additional expense we received expertise, man hours, equipment for shared resource management data.  

**Notes:**
TVA Cooperator with clear glass, holding tank for photo vouchering, then releasing fish unharmed.  Label on glass has EventID information for linking to the database.
Highlights:- Johnny darter (Etheostoma nigrum nigrum) - first record for BISO and first record for the middle Cumberland River system
- Pumpkinseed (Lepomis gibbosus) - BLRI: first record from the French Broad River drainage, and a fish never before documented in western North Carolina (this is a common species elsewhere; may have been a bait bucket introduction).
- ashy darter (Etheostoma cinereum) - BISO:  G2G3 species
- spotfin chub (Cyprinella monacha) - Federally-listed Threatened species, documented spawning at OBRI with underwater videography
- duskytail darter (Etheostoma percnurum) - Federally-listed Endangered species, documented at BISO (it was previously known from there, but inventories provided verification of this very rare fish's continued existence in the park).

## tbl_Location

- tbl_Event

- Raw Fish Data

- Specimens

- Photos

**Notes:**
Received an existing database from CFI/TVA, adjusted that one, didn’t change tbl names for NRDT naming because of all the code incorporated in existing forms, GUIDs added later, Specimen table added later for Curators and Repository Information, added Photo link table tbl_SiteVisits_Images(not every photo is a voucher). Has basic NRDT structure, three core tables and added new tables to accommodate all the information gathered for this inventory.  Revised database after involvement with contractors.

## WASO – Ft. CollinsSimon Kingston

- Comment TaggerTest Data

- Comment TaggerTool

**Notes:**
Using the Comment Tagger Tool (version 1.1.)
Created by: Simon Kingston 9/16/2005
Last Modified by: Simon Kingston 3/10/2006
Purpose
The Comment Tagger tool is an MS Access file designed to concatenate (put together) values from many fields in a single table into a new memo field in that table with tags.  The user can add an optional leading tag, name the new field, and choose the delimiter that will be used.
This tool is useful when processing pre-Oracle files that contain many fields with useful data that doesn’t match well to current NPSpecies fields.  Typically the data from those fields is concatenated, tagged, and put into the Comments field in either tblNPSpecies, tblVouchers, or tblObservations.
Requirements
MS Access 97
CommentTaggerTool.mdb (found at P:\NPSpp\Tools\CommentTagger)
MS Access 97 file with data (must not be read-only)
Important Notes
This tool will modify the selected table in your MS Access file by adding a new memo field.
This tool will not make any other changes to your MS Access file.
This tool will only tag and concatenate fields for records that have values in the selected fields; fields with nulls will not be tagged and concatenated for a particular record.
Instructions
Copy CommentTaggerTool.mdb from the P drive to your local working directory.
Open CommentTaggerTool.mdb.
Click the button for “Step #1: Select Database with Table to Link.”
Select an MS Access file that contains fields you wish to concatenate with tags and click the Open button.
Under “Step #2: Select Table”, select the table you wish to work with from the drop-down list.
Under “Step #3: Select Fields and Edit Tags...”, check the box next to each field you wish to concatenate and tag.
For each field selected in the previous step, choose the order that the fields will be added to the new field.
For each field selected in the previous step, make any changes you desire to the Tag that goes with the field.
Under “Step #4: Type New Field Name...”, type a name for the new memo field that will be created in the selected table.
Under “Step #5: Enter Leading Tag”, enter a label to precede the tagged concatenated values in your new memo field, or clear the default value to leave out a leading tag.
Under “Step #6: Enter Delimiter...”, enter the delimiter that you wish to use.  The default is “;“.
If you wish for a space after each delimiter, check the “Add space after delimiter?” box.
If you wish for a final delimiter at the end of the data, check the “Include trailing delimiter at end of data?” box..  
Click the “Step #7: Run the Comment Tagger” button.  When the Comment Tagger tool is done, you will an “Operation Complete” message.
To run the Comment Tagger tool again on another table, click the “Clear Form” button and start again at step #3 of these instructions.

## Southwest Alaska NetworkDorothy Mortenson

## Lake Inventory Database

- Throughout SWAN there were various small studies done related to lakes.
- Needed to get a handle on what it is we knew or needed to know.

## GIS Hydrography 

- Lakes hydrolayer had many problems.
- Fixed these and gave them a unique ID.
- Still waiting for clean NHD data.

## Created a “best available” lake bathymetry GIS layer 

## Created a Lake/River Photo Library 

- Contractors are from out of state.
- Photos and maps help to get a better understanding of what is going on in the lake.

## 
Currently a directory of PDFs.
Would like to add a bibliography link.

- Currently a directory of PDFs.
- Would like to add a bibliography link.

- Collected Documents related to the lakes, rivers, and fish

## Basic Queries 

- Lake size, depth, etc.
- Determined the threshold of lake size to start collecting more detailed information (about 100 hectares)
- Then started to build a database compiling what we know about the lakes.

## Slide 28

## Added data, as discovered 

- Qualities of the lake (clear, glacial, brown)
- Misc. water quality data
- Fish species found in lake, from the fish inventories and other documentation
- Tributaries

## Where do we go from here? 

- Inventory helps us provide basic information for sample selection
- Identifies where other information needs to be collected, when the opportunity arises (LACL will collect better bathymetry data).
- Provides the foundation for developing the Surface Water Vital Signs Monitoring protocols.
- Would like to
-  Convert this to a geo-database;
-  Link to NPSTORET
-  Build the application on the Web

## Slide 32

## Central Alaska NetworkDoug Wilder

## Programming Access databases for Field Data Collection

**Survey Timer**
**Save to Jump**
**Capture GPS**
**Num. Keypad**

## Programming Access databases for Field Data Collection

| Tbl_FD_Loc |  |  |
| --- | --- | --- |
|  |  |  |
|  |  |  |
|  |  |  |
|  |  |  |
|  |  |  |
|  |  |  |

| .dbf |  |  |
| --- | --- | --- |
|  |  |  |
|  |  |  |
|  |  |  |
|  |  |  |
|  |  |  |
|  |  |  |

**Access and ArcPad **
**sharing data **

**Programming Access databases for Field Data Collection**

## Programming Access databases for Field Data Collection

**Programming Access databases for Field Data Collection**

**Synchronize**

**Field Unit 1**

**Field Unit 2**

**Field Unit 3**

**Generate Field DB**

**Mother**

**Collect Data**

## Computer Kiosks Provide

**Park Visitors with CAKN **
**Information and Data**

**Introductory page allows users to quickly navigate & learn about CAKN vital signs **

**Notes:**
During 2005 the CAKN developed an interactive computer kiosk program that has been installed in 5 park visitor centers.  The kiosk is a stand-alone application that is driven by a touchscreen and allows visitors to easily learn about monitoring and the CAKN specifically.  As monitoring data become available, users will be able to plot data and see how monitoring data are being used by the parks. Total cost for each kiosk was approximately $4,000.  In 2006, 2 more units will be placed in network park visitors centers.

## Sonoran Desert NetworkKristin Beaupre

## RESEARCH ATLAS

**RESEARCH ATLAS**
- A Data Sharing Application for the Sonoran Desert Regional Ecosystem Monitoring Project

- Promote interagency data sharing
- Organize and catalog research efforts
- The web application can pull data from other Government developed databases as well. (NatureBib)
- 2 main elements:  What and When (bibliography information) and Where (ArcIMS)

- Sonoran Desert Network, April 2006

**Notes:**
Kristin Beaupre
The Research Atlas is a web-based application to query content and location of research projects conducted in the Sonoran Desert region. The research atlas prototype is being developed as a functional system for the eleven parks in the Sonoran Desert Network and will be expanded to the Huachuca Mountains Grassland Valley Complex to test its functionality as an interagency “data switchboard.”
The application contains two linked components: a relational database of research report citations (Literature Only Query link) built in Microsoft SQL Server and a GIS database of research plot locations (Spatial Query link), which are displayed in dynamic maps on the Internet via ArcIMS software.
Authorized users with login privileges will be allowed to add new or edit existing data by selecting the corresponding links (Add Data or Edit Data) on the home page. 

## Click hotlink to view a scanned image of original document 

**Click hotlink to view a scanned image of original document**

**Click button to see the data collection sites for the research documents shown on the Literature Search page **

- Sonoran Desert Network, February 2006

**Notes:**
Users will be able to search the Research Atlas using specified criteria and be presented with a list of research documents and the option to view a map showing where the research was conducted.
The Literature Search screen shows the results returned when the specified search criteria is “fire.” The Literature Search query is designed to draw data from multiple sources, so not only will the application search the SQL Server database, it will also search existing external databases such as NatureBib. The title of each citation is hot-linked to a scanned image of the original document (generally an Adobe pdf file).
When the “Map It!” button on the Literature Search page is clicked, the map of related research locations is displayed. Reference layers such as highways, cities, and NPS boundaries and trails, as well as DRGs and DOQs as backdrop options, may also be displayed. 

## Northeast Temperate NetworkFred Dieffenbach

## ATOM

- <date/time>

- Aggregators

- XML

- RSS

- NEWS Feeds

**Notes:**
A NEWS, or web feed is a specially formatted file that is interpreted by an application known as an aggregator.  An aggregator can be a stand-alone program (e.g., feed demon), may be web based (e.g., Google Reader) or can be an add-in to another program like Internet Explorer (e.g., Lektora).  The content of the feed is dictated by the originator of the feed and can be updated frequently or not.  A feed is really just one additional way to communicate with an audience, and does not replace a well designed web page, periodic email messages, or other forms of  interaction.  However, publishing a feed offers an advantage over other electronic forms of communication because it does not require the originator to know the identity of the end user, and it does not require the end user to visit the originators web site to search for ‘breaking news.’  For example, when communicating by email, the names of the intended recipients must be known – if not, the message may not reach its destination.  Further, relying on the audience to periodically visit a web site in search of new content is not a viable strategy for organizations that release information on a sporadic basis.  Users quickly learn whether a site is likely to change, and will cease to visit those that are typically “static.”  Creating a feed ‘pushes’ the new content out to the audience and keeps those with an interest in the program informed of recent activities.
Assembling a collection of ‘feeds’ in an aggregator gives the user the ability to create a web-based newspaper that addresses their interests, and keeps the user apprised of up-to-date information.

## 	From Wikipedia “. . .Aggregators reduce the time and effort needed to regularly check websites of interest for updates, creating a unique information space or "personal newspaper." An aggregator is able to subscribe to a feed, check for new content at user-determined intervals, and retrieve the content. The content is sometimes described as being "pulled" to the subscriber, as opposed to "pushed" with email or IM. Unlike recipients of some "pushed" information, the aggregator user can easily unsubscribe from a feed. . .”

- <date/time>

- 	From Wikipedia *“. . .Aggregators reduce the time and effort needed to regularly check websites of interest for updates, creating a unique information space or "personal newspaper." An aggregator is able to subscribe to a feed, check for new content at user-determined intervals, and retrieve the content. The content is sometimes described as being "pulled" to the subscriber, as opposed to "pushed" with *_[email](http://en.wikipedia.org/wiki/Email)_* or *_[IM](http://en.wikipedia.org/wiki/Instant_messaging)_*. Unlike recipients of some "pushed" information, the aggregator user can easily unsubscribe from a feed. . .”*

**What do Aggregators do?**

**Notes:**
Aggregators reduce the time and effort needed to regularly check websites of interest for updates, creating a unique information space or "personal newspaper." An aggregator is able to subscribe to a feed, check for new content at user-determined intervals, and retrieve the content. The content is sometimes described as being "pulled" to the subscriber, as opposed to "pushed" with email or IM. Unlike recipients of some "pushed" information, the aggregator user can easily unsubscribe from a feed.
Aggregator features are gradually being built into portal sites such as My Yahoo! and Google, Web browsers such as Mozilla Firefox, Safari, Opera, e-mail programs like Microsoft Outlook, and other applications, including Apple's iTunes, which serves as a podcast aggregator.
The aggregator provides a consolidated view of the content in a single browser display or desktop application. Such applications are also referred to as RSS readers, feed readers, feed aggregators or news readers, although in Internet communication, the latter term was first used for programs that read Usenet newsgroups.
A website may incorporate aggregator features by republishing syndicated content on one or more of its pages. Aggregator features also may be incorporated in other client software, including Web browsers, e-mail clients, weblog creation programs, or media player programs. Devices such as mobile phones or Tivo video recorders (already aggregating television programs) may incorporate XML aggregators.
The syndicated content an aggregator will retrieve and interpret is usually supplied in the form of RSS or other XML-based data, such as RDF or Atom formats.

## 	Communication is a fundamental element of a comprehensive monitoring program, and being able to deliver information to an audience in more than one format is vital.  

- <date/time>

- 	Communication is a fundamental element of a comprehensive monitoring program, and being able to deliver information to an audience in more than one format is vital.

**How is this useful to an I&M Network?**

**Notes:**
One of the problems we must solve is knowing our audience and how best to communicate with it.  In traditional forms of communication, the audience is clearly known.  It could be the person to whom you are writing a letter or an email, or members of a group that you are addressing as in a presentation.  However, there is a broader audience that is more difficult to identify.  Developing a web page, and generating a new feed is one way to communicate with a perceived audience.

## <footer>

- <date/time>

**Notes:**
Publishing a NEWS feed helps you get the ‘word-out.’  This is an example of a news aggregator used by the Northeast Temperate Network.  The screen shot shows the feed published by the Network in early January 2006.  In this view, all headlines are depicted, but the user can configure the application to show only ‘unread’ links, or links newer than a specified time period.  Though it is not shown on this screen shot, this application allows the user to add as many feeds as desired and organize them according to subject, thereby creating a user defined on-line newspaper with a number of different ‘sections.’

## ESPN Feed

- <date/time>

- NETN Feed

- List of feeds built into “Virtual Newspaper”
- Number represent the number of unread stories

**Notes:**
This image shows an aggregator that functions through Internet Explorer and possibly other browsers.  Multiple feeds are integrated into a single window, or “virtual newspaper.”  One key feature to this application is that it enables the user to limit the display to only those news stories that have NOT been read.

## <footer>

- <date/time>

**Notes:**
News feeds can provide timely information from external sources on topics of interest to Network staff.  This slide depicts the NPSpecies page from the intranet version of the Northeast Temperate Network data management plan.  The pane on the right (circled) contains a web feed from ScienceDaily, a syndicated news source for information on plants and animals.   Each page of the Network intranet data management plan incorporates a news feed that is targeted to the subject area encompassed by the chapter.  Incorporating news feeds from other sources helps to make the page more dynamic and brings useful information into the hands of users.

- <date/time>

**Notes:**
In addition to an aggregator, a feed originator needs to use a “feed burner” that creates the xml feed file.  The image is of an application called FeedForAll.  The application lets the user easily manage existing news items, and incorporates an ftp posting routine that makes the process of editing and posting a feed a quick and efficient process.

## 	Stale Content

- <date/time>

- 	Stale Content

- There Must Be a Down Side!

**Notes:**
Interest in a subject fades quickly if the information never changes.  This is clearly a problem with web pages.  Imagine what would happen to USA Today’s circulation if yesterday’s stories were included in today’s paper!  Even when content on a page does change, this mode of communication relies upon the end user to re-visit a site to find the new information.  A news feed solves this by delivering the new information TO the audience.

- <date/time>

**Notes:**
The circled area is a news feed embedded into the NETN home page.  This is a concept page that is not currently available on-line.  The content scrolls continuously every few seconds and the headline is a link to a full document.  In addition to Network news, this approach enables us to merge multiple feeds into a single xml file.  One of the advantages of merging multiple sources into a single feed is being able to ensure that new content is always present even when the Network does not have “new” news to report.

- <date/time>

- Products Discussed or Displayed

- Feed Demon		-	http://www.newsgator.com/Home.aspx
- Lektora			-	http://www.lektora.com/index_en.html
- RSS-to-Javascript	-	http://www.rss-to-javascript.com/p/138.html
- FeedForAll		-	http://www.feedforall.com/
- CoffeeCup 	RSS News Flash	-	http://www.coffeecup.com/news-flash/

**Notes:**
These represent a small number of the many applications that are available to create and display a news feed.

- <date/time>

- Discussion

## Glacier National ParkRichard Menicke

## Glacier National Park

**2006 I&M Data Management Conference**

- Park Research Operation:
- Managing and Delivering Content

## Park Research Operation Management

- SOP to articulate the “operation”
- 		- permit evaluation and administration
- 		- information management protocols
- 		- roles & responsibilities defined
- Goal: correct loss of research knowledge and information
- Focus on information management protocols, support from I&M Network staff
- Research Learning Center component
- 		- outreach to managers, educational publics

**Notes:**
NOTE: Park research permitting operations run somewhat independent of I&M and vital signs, though very much related.  Parks/networks left to own devices to establish connection to park-based program.

1) Permit evaluation – considers operational park issues, compliance, scientific merit.  Defines priorities, by park management need/criteria (T&E, knowledge gaps) and pure science needs.
2) Long chronicled history in NPS of research efforts and information mismanagement => knowledge sink
3) NPS servicewide databases offer tools to capture much information; support from Brent/David to
	* develop databases for high priority projects,
	* assure NPSpecies, ANCS+ requirements met 
4) RLC in place to extend information to various publics through brochures, etc.

## ISSUES in Managing Park Research Information

- No park base funding for data management
- Tasks can be cumbersome, redundant
- I&M network staff support is (technically) outside the VSM box

## VISION for Managing Park Research Information

- RPRS database speaks to NPS information management needs
- Park load is shifted from data entry to management/application
- Researchers have available web-based tools that....
- 	are easy to use
- 	eliminate redundancy in data entry
- 	inform NPS information management needs
- Research Learning Center, Interpretation informed
- 	packaging, delivery
- Managers and various publics are happy recipients of information

**Notes:**
RPRS application asks 2-3 focused data management q’s, programming to create 
	- initial metadata citation
	- provide tracking info (anticipated products, data formats, long-term storage)
	- initiate additional tasks (e.g. ANCS+).  
	- upload of reports, which filter through NatureBib and receive a citation.

2) Park energy spent helping researchers, ensuring compliance with proposed work, and getting information to publics.

3) Eliminate as much NPS touching of data as possible through programming, automation – while maintaining controls.