StreamNet On-line Data Query
USER GUIDE

April 2003

 

The StreamNet data query system is a powerful tool for locating, displaying and downloading data from the StreamNet database, a complex relational database with multiple ways to sort and access information. The best way to learn how to use the query is to try it. This User Guide is intended to walk you through an example of how to use the system to find information. Be sure to check out the General Guidelines and Tipsat the end of this guide. Once you become familiar with the basic operation of the query system, we think you will find it useful for fulfilling your data needs.

If you get lost or make a mistake you can always use your browser's Back function. Or, by selecting New Query at the top of the page, any existing query will be deleted and you will be ready to start afresh. You can click on the StreamNet Home link to exit the system and start over or move on to something else.

If you experience problems in either accessing or using the StreamNet data, please contact StreamNet via email by clicking the "Please report any problems here" link at the bottom of any query page, by clicking the "Feedback" link at the top of most pages, or by phone at the Pacific States Marine Fisheries Commission, (503) 595-3100, and ask for the StreamNet office. We are happy to help!

 

Getting Started

Access the query system by clicking the Fish Data link on the StreamNet home page www.streamnet.org, then click On-line Data Query. Begin by clicking the Standard Query Method link. The Select Criteria box comes up containing all of the criteria available. The user may choose any combination of criteria for which information is available. The criteria are of four broad types:

Data Category (such as fish distribution, adult returns, maps)
Species/Run (such as chinook salmon, spring run)
Geographic Areas (such as state, subbasin, or stream)
Point Features (such as a dam or hatchery)

If you are unsure of which criteria are appropriate for your needs, you can click on the Info question mark beside each criterion for more information about that criterion.

The system queries the database each time a criterion is selected and returns only those remaining criteria that contain data. This prevents building a query that results in no information. If a criterion does not appear at a subsequent step, that means that there are no data for that criterion for the criteria you have already selected (for example, chum salmon will not appear in the species list if you have already selected State = Idaho, where chum salmon are not found).

The only criterion that is required is the Data Category, but it need not be selected first. (This is the key thing you will need to know to use this system). The order in which you select criteria depends on the information you wish to access. In general, select the criterion that is of primary concern first and then narrow your search from there. For example, if you are interested in a specific kind of information, then choose Data Category first. If you are most interested in a specific species, select the Species of interest and then proceed to a geographic area, a data category, a year range, or other criterion. Similarly, if you wish to view data for a given location, select the geographic area first. We recommend that you select no more criteria than you really need.

EXAMPLE

Starting with a geographic area: In the following step-by-step example we are going to perform a data query starting with a geographic area. Let's say we know we want information for a Columbia River Subbasin ("subbasin" as defined by NWPPC in 2001 for subbasin planning purposes).

1. First we click on the criterion "Columbia Subbasin 2001."

To limit the number of subbasins from which to select, you can first select a State or Region and then Subbasin 2001. This limits the list of subbasins to only those that fall within your area of interest.

Note: At the bottom of each page you can provide feedback on any errors that may occur during your query. StreamNet staff would like to hear about any problems so they can be fixed as rapidly as possible.

Note: The Data Holdings link at the bottom left of the query box provides an overview of all the data in the StreamNet database based on the criteria selected up to that point. As more criteria are selected, the available information is narrowed. This summary can help you decide what additional criteria may be useful in your query. The summary consists of a large table organized by data category and, for time series data, by year. Within each time series data category, the table lists the number of time series available in each year and assigns a color to represent the relative amount of data available.

 

2. Second, we select a specific subbasin - you can also click directly on the specific subbasin on the map. Here we choose the John Day subbasin in central Oregon.

When querying by geographic area (state, county, region, province, subbasin, or HUC) a map of the geographic units is presented along with a list of the units. Geographic areas that are colored green indicate that at least some data exist for that unit, while blank (cream colored) areas indicate that there are no data in the database for that particular area. If you believe that some data should exist for an area that is shown as blank, contact StreamNet via the Report problems link to find out if we have data that are not yet loaded into the online database.

 

3. The subbasin we selected is shown on the right side of the query box as we build our query. We see from the highlighted area under Your Criteria that we still must choose a Data Category before viewing any data, so let’s do that now.

Note: As each criterion is selected, it appears in the right side of the query box. You can remove a criterion by clicking the X for that criterion under Clear on the far right side of the query box.

 

4. The data category list contains only those categories for which some data exist for the criteria already selected (in this case, for subbasin = John Day). We see there are 12 kinds of data available. We next click on the Data Category for which we wish to obtain information. Let's examine an index of spawning population size by selecting Redd Counts. After selecting Data Category = Redd counts, we will also select Species = chinook salmon and Run = spring.

 

5. After choosing our data category, species, and run, we see there are 6 "trends" available for spring run chinook salmon redd counts in the John Day subbasin. (In the StreamNet query system, the term "Trend" is used informally to refer to a time series data set.) We decide we want to see in which counties these data sets are located, so we click on County in the left-hand column.

 

6. Spring chinook redd count data in the John Day subbasin lie within 7 counties. We will click on Grant, Oregon.

Note: The previous screen indicated 6 trends (time series) existed for the criteria chosen so far, but when looking for a county, it appears 12 trends are listed (6 in Grant County, one in each of the others). This is because the sampled reaches for two trends cross county lines and thus occur in more than one county. StreamNet data are stored so that they are associated with any political or geographic unit where field sampling took place at least in part. When data are selected in the StreamNet query system by geographic area, they include all data that exist within the boundaries of the area and those that occur only partially within the area. Thus a data record can legitimately occur within two different geographic areas. Users must be aware of the possibility of duplicate records when totaling results from adjacent geographic areas. See Data crossing boundaries in the General Guidelines and Tips at the end of this guide for more information.

7. There are 6 trends for the combination of criteria we have chosen -- spring chinook redd counts in Grant County and in the John Day subbasin. We now click on View Available Data to see a our list of available data.

Note: The order of criterion selection has no effect on the data ultimately returned. In this example we began with a subbasin, then chose data category, species, run, and finally a county. But we could have proceded in any order and arrived at this same spot. All paths to the same set of criteria will produce identical results.

 

8. The page that comes up is the Trend List page. This page shows the basics for each trend such as location, species, and year range covered. We can view our data in a variety of ways. View information for an individual trend (time series) by clicking on it’s link. We can also see a Summary table for all the trends listed; see a Summary Graph for all the trends listed; or Download the data for all trends listed for import into a spreadsheet. These last three options are shown on the gray toolbar. First, let's click on Trend Number 52055.

 

9. After clicking on a trend number, in this case 52055, we see detailed information for that data set only, including each year's redd count. Several items are of interest on this page. The number of redds (in the Count column), number of redds per mile (in the Count Per Mile column), and the length of the reach surveyed (in the Miles Surveyed column) are shown. Some trends, such as this one, show both Count and Count per mile, while others show only one of these, depending on the information provided by the data collecting agency.

Note: Inconsistent information can show on this page. In this example the Location in the header says this redd count occurs from river mile 0 to 9.3, the Trend Comment in the header says the reach is 2.5 miles, and the Miles Surveyed in the table ranges from 0.5 to 3.0 miles. This is instructive in several ways. 1) Sometimes the area surveyed really is inconsistent among years. This was the case for this survey, where in the early years the best survey location was being determined -- all surveys after 1966 are 2.5 miles. 2) Because the exact 2.5 mile reach has not yet been determined we purposely avoid misleading precision in the Location and define it as the entire stream length. Precision will be added as we further develop the database. 3) To help sort out such issues, we provide links to the source documents for all data. Clicking the number in the Reference column takes you to the list of references used for the trend. Click the reference title to see detailed information on a reference. A copy of the report or any reference can be obtained from the StreamNet Library.

Another inconsistency you may encounter is a Count Per Mile that does not match the Count column divided by the Miles Surveyed column. These cases occur when the source document is inconsistent. We try to provide all the information available; in these cases the Count Type indicates which parameter is primary, as reported by the agency that conducted the survey.

 

10. Clicking on Counts Graph on the gray toolbar produces a line graph displaying the counts by year for this particular trend. Though not presentation quality, this graph provides a quick visualization of the data.

Note: This screen always graphs the "counts," even when a "count per mile" is the primary variable of interest (as indicated next to Count Type in the header). In this example we see that although "Redds per mile" is the primary parameter reported by the data collector, the number of redds is graphed, not redds per mile. You should also be aware that the graph does not take sample distance into consideration. In this example, because Miles Surveyed varied before 1967 those values are not strictly comparable on the graph even though they appear on it.

Note: Let's take a detour: Two screens back, on the Trend List page's gray toolbar, was a link to a Summary Graph. This returns a line graph of the summed counts for all the listed trends, superimposed on a bar chart showing number of observations contributing to each value, as shown below. When the same set of time series contribute to a range of years the summary graph can be a useful visualization of the combined data sets. But use great caution in interpreting summary graphs. Varying numbers of observations per year, varying survey lengths, and other complications can result in misleading graphs.

 

 

Now let's return to where we were. Click your browser's Back button, then select Trend Number 52055.

11. If we would like to obtain the data for analysis in our own spreadsheet or database, we click Download on the gray toolbar. The download option appears at several levels in the query system. The data downloaded will reflect the data set(s) currently within the scope of the query. On the previous page we were examining a single trend, which means the download would be only for that trend. For this example we will hit our browser's Back button twice to return to the Trend List page, and then click the Download link. This will allow us to download the data for all six spring chinook redd count trends in the John Day subbasin and Grant County at once.

There are several options for constructing the download file. The StreamNet query system creates the file to be downloaded as a text (ASCII) file, with the fields delimited by either commas or tabs. We can choose to have the column headings included in the first row. In this example we choose to include column headings, use commas as delimiters, and to identify text within double quotations, along with a bibliographic reference file. Once the options are chosen, click the Create Zip File button.

Note: The download file is zipped to compress the data and save space, allowing the download to run faster. The file must be unzipped before use. If you have questions about doing this, please contact StreamNet staff via the Report problems link or at 503-595-3100.

 

12. After creating the zip file, we download it from the StreamNet site to our computer by clicking on the file link. Give it an appropriate file name to remember what it is.

Note: Once the file is downloaded to your computer, it can be unzipped and then used in any spreadsheet or database program. If you need to analyze data from several locations or of related types, it may be necessary to download several individual data files and then combine them in a database or spreadsheet for analysis. As we saw when we were selecting a county, the same data can be associated with more than one geographic area. So if you do combine data sets, be sure to look for duplicate data. It is possible to download large portions of or even the entire StreamNet database. However, the size of the download files may be too large to perform in a reasonable time. If you need a large file, contact StreamNet staff by using the Report problems link or by phone at 503-595-3100 and we will be happy to run the query and provide the file.

 

Other Query Approaches

Besides the Standard Query, you can also use the Interactive Map Query Method, available at http://www.streamnet.org/mapper.html. This method lets you work interactively with a map to select your area of interest and then obtain the data available for that area. This query approach is much different and has it's own help pages available at http://map.streamnet.org/website/snetmapper/HelpFile.htm.

 

General Guidelines and Tips

Be patient. The system queries the database each time a criterion is selected, which takes time. This is done to streamline the query at subsequent steps, as a smaller data set is queried after each criterion is selected. It also prevents building a query that results in no data. Results will be returned in a few seconds or up to a minute or so. The occasionally "long" waits are the result of the many kinds of information contained in a complex database. We hope that you find the availability of the data worth a short wait compared with trying to obtain them through other means.

Beware of huge downloads. A little forethought will give you a hint of how large a download you are requesting. If you select Stream or Dam as the first criterion, you will get a list of thousands of items, which can take minutes to download and much longer for your web browser to render. For this reason we limit to 1000 the number of items that can be shown on screen. Thus, narrowing your search strategically will pay off greatly because you will not run into these roadblocks. No matter how many records you select, though, you can always create a downloadable ascii file of all records in the set.

Narrow your search criteria as quickly as possible. In general, use the fewest criteria necessary. For example, there is no need to click State then Subbasin then HUC 4 if you know what HUC you want data for. You can go to the HUC 4 criterion directly.

Multiple data. The query system currently does not allow locating data for multiple streams, locations or species at the same time. If you need such data, you will need to download the individual data sets and combine them in a database or spreadsheet. (But, see "Data crossing boundaries" below.) Alternatively, you can download a superset of the items of interest and then cull the data that are not needed.

Contact us! Because of the complexity of the StreamNet database, it can require some experience to become proficient at using the query system. We find that too many people give up when they are not certain what to do next. We are happy to answer questions or to provide guidance on how to meet your objectives. We can even run queries for you and provide the results! Just use the Report problems link to email us at project@streamnet.org, or phone us at the Pacific States Marine Fisheries Commission, (503) 595-3100, and ask for the StreamNet office. We are usually able to respond to email questions and requests within one business day or less.

Data crossing boundaries. The data in the StreamNet database are biological in origin and generally relate to natural features such as streams or subbasins. Political boundaries generally do not coincide with natural boundaries, or in other cases the natural feature is the boundary and it is thus in two jurisdictions. This results in some waters and associated data occurring in two political or hydrologic units. This creates a potentially difficult situation for managing these data. For example, if a count transect on a stream crosses a county line it would be possible to double count the data if queried separately for each county. Please be aware that as a general convention, the StreamNet database links data with a political subdivision if any portion of the data set was located in the political unit. Though less common, the same occurrence will happen if a data set crosses subbasin or HUC boundaries. Thus, it is imperative to be aware of the possibility of double counting if data are totaled for several political (county, state) or geographic (HUC, subbasin) units. Also, because some sample sites may cross unit lines, the total for a unit might also include some data obtained from outside that unit. It may be difficult or impossible to accurately split data that cross unit boundaries if the sample site boundaries do not coincide with the unit boundaries. We can provide assistance and advice if you are faced with these problems. Please contact us.

Glossary. Look up any unfamiliar technical and fisheries terms in the glossary. This is accessed from the "quick list" on the StreamNet home page.

 

Conclusion

We hope this User’s Guide was helpful in familiarizing you with StreamNet's Standard Query Method. It may take several tries to become proficient in using the system. If you need further assistance, please contact StreamNet by email at project@streamnet.org, use the Report problems button on any page of the StreamNet query system, the feedback button at the top of most StreamNet pages, or call us at (503) 595-3100. We are located at the Pacific States Marine Fisheries Commission -- ask for the StreamNet project when you call.

Portions of the StreamNet project are located within the state fish and wildlife agencies, the U.S. Fish and Wildlife Service, and the Columbia River basin and the Columbia River Intertribal Fish Commission. For questions about information specific to those entities, you can locate phone numbers and email addresses for the individual project leaders on the StreamNet web site on the Contact us page.