PSMFC Homepage
HOME
StreamNet's home page StreamNet's mission, services, and participants Locate fish-related data StreamNet's largest data resource, regularly updated ("integrated" interface). StreamNet's largest data resource, regularly updated (traditional interface). Locate data from a map interface. Obtain maps and GIS layers. Locate static data sets. Also, add your data sets to the archive. A full service library containing fish and wildlife literature and reports. Various other useful materials Links to other fisheries related resources on the Web 2004 U.S. District Court ruling on pesticides near salmon streams Stream reaches where hydroelectric development is discouraged Information and data in support of the NPCC Fish and Wildlife program Documents, reports, and newsletters produced by the StreamNet project Recommendations on how to cite data and information obtained from StreamNet Various other useful materials The latest data, but in the old look.









NOTES ON STREAMNET DATABASE DUMPS


Notes on StreamNet Database Data Dump Dated 20140610

This edition of the StreamNet database contains updates to Trend and count data in Idaho, Oregon, Washington, Montana and California, and updates to many tables. New to this edition are four Coordinated Assessments tables: NOSA (Natural Origin Spawner Abundance), SAR (Smolt to Adult Ratios), RperS (Recruits per Spawner) and Populations (NMFS/TRT populations), though the first three tables are empty at this time. The LocMaster table contains location names and location type coding for all LocationIDs that are used in any table and reflects the latest Mixed Scale Hydrography v3.1, which is available at http://old.streamnet.org/mapping_apps.cfm. Each LocMaster record includes additional georeferencing fields identifying the ANSI StateCode and CountyCode, the NPCC Province and Subbasin (2001), the StreamNet Region, and all Hydrologic Unit Codes from HUC_2 to HUC_12. An X embedded in any of these georeferencing fields indicates more than one code is applicable, and all are listed in the related LocX* tables, which detail the begin and end measures in feet on each stream that pertain to each georeferenced code, e.g. County borders.

The database includes eleven example queries that demonstrate how to dereference coded information with lookup tables, but users will typically want to customize these queries for their own interests and for building specific reports. It does not include the static maps, photographs, or maps on the fly functionality that you get using StreamNet's online system. It also does not include a user interface at this time, but someone with a little experience with Access should be able to formulate queries to analyze and export desired datasets. None of the tables in this database are protected against changes.

StreamNet periodically provides this database to assist our power users who have broad basin-wide analyses to perform. But remember that the StreamNet online query system does all of the complicated georeferencing behind the scenes, allowing you to download the final result set into applications such as Excel or Access.



Notes on StreamNet Database Data Dump Dated 20121205

This edition of the StreamNet database contains updates to Trend and count data in Idaho, Oregon, Washington and California, and updates to many tables. The LocMaster table contains location names and location type coding for all LocationIDs that are used in any table and reflects the latest Mixed Scale Hydrography v3.1 updates. Each LocMaster record includes additional georeferencing fields identifying the ANSI StateCode and CountyCode, the NPCC Province and Subbasin (2001), the StreamNet Region, and all Hydrologic Unit Codes from HUC_2 to HUC_12. An X embedded in any of these georeferencing fields indicates more than one code is applicable, and all are listed in the related LocX* tables, which detail the begin and end measures in feet on each stream that pertain to each georeferenced code, e.g. County borders.

The database includes eleven example queries that demonstrate how to dereference coded information with lookup tables, but users will typically want to customize these queries for their own interests and for building specific reports. It does not include the static maps, photographs, or maps on the fly functionality that you get using StreamNet's online system. It also does not include a user interface at this time, but someone with a little experience with Access should be able to formulate queries to analyze and export desired datasets. None of the tables in this database are protected against changes.

StreamNet periodically provides this database to assist our power users who have broad basin-wide analyses to perform. But remember that the StreamNet online query system does all of the complicated georeferencing behind the scenes, allowing you to download the final result set into applications such as Excel or Access.



Notes on StreamNet Database Data Dump Dated 20120118

This edition of the StreamNet database contains updates to Trend and count data in Idaho, Oregon, Washington and California, and updates to many tables. The LocMaster table contains location names and location type coding for all LocationIDs that are used in any table and each record includes additional georeferencing fields identifying the ANSI StateCode and CountyCode, the NPCC Province and Subbasin (2001), the StreamNet Region, and all Hydrologic Unit Codes from HUC_2 to HUC_12. An X embedded in any of these georeferencing fields indicates more than one code is applicable, and all are listed in the related LocX* tables, which detail the begin and end measures in feet on each stream that pertain to each georeferenced code, e.g. County borders.

The database includes eleven example queries that demonstrate how to dereference coded information with lookup tables, but users will typically want to customize these queries for their own interests and for building specific reports. It does not include the static maps, photographs, or maps on the fly functionality that you get using StreamNet's online system. It also does not include a user interface at this time, but someone with a little experience with Access should be able to formulate queries to analyze and export desired datasets. None of the tables in this database are protected against changes.

StreamNet periodically provides this database to assist our power users who have broad basin-wide analyses to perform. But remember that the StreamNet online query system does all of the complicated georeferencing behind the scenes, allowing you to download the final result set into applications such as Excel or Access.



Notes on StreamNet Database Data Dump Dated 20110503

This edition of the StreamNet database contains updates to Trend and count data in Oregon, Idaho and California, an updated Reference table, and a corrected Age table (last edition included an incorrect table key which omitted some records). The LocMaster table contains location names and location type coding for all LocationIDs that are used in any table.

The database includes twelve example queries that demonstrate how to dereference coded information with lookup tables, but users will typically want to customize these queries for their own interests and for building specific reports. It does not include the static maps, photographs, or maps on the fly functionality that you get using StreamNet's online system. It also does not include a user interface at this time, but someone with a little experience with Access should be able to formulate queries to analyze and export desired datasets. None of the tables in this database are protected against changes.

StreamNet periodically provides this database to assist our power users who have broad basin-wide analyses to perform. But remember that the StreamNet online query system does all of the complicated georeferencing behind the scenes, allowing you to download the final result set into applications such as Excel or Access.



Notes on StreamNet Database Data Dump Dated 20101229

This version is the first to use StreamNet's latest Mixed Scale Hydrography Version 2. It contains many updates to Trend and count data, as well as updates to georeferencing codes called LocationIDs (some of which have changed). The LocMaster table contains location names and location type coding for all LocationIDs that are used in any table. This is the first edition of LocMaster to include fields that identify the georeference codes applicable for each location, including HUC2 through HUC12 values (X's indicate multiple values are applicable to the location code).

The database includes twelve example queries that demonstrate how to dereference coded information with lookup tables, but users will typically want to customize these queries for their own interests and for building specific reports. It does not include the static maps, photographs, or maps on the fly functionality that you get using StreamNet's online system. It also does not include a user interface at this time, but someone with a little experience with Access should be able to formulate queries to analyze and export desired datasets. None of the tables in this database are protected against change.

StreamNet periodically provides this database to assist our power users who have broad basin-wide analyses to perform. But remember that the StreamNet online query system does all of the complicated georeferencing behind the scenes, allowing you to download the final result set into applications such as Excel or Access.



Notes on StreamNet Database Data Dump Dated 20090512

This version is the first to use StreamNet's new Mixed Scale Hydrography. It contains many updates to Trend and count data, as well as updates to georeferencing codes called LocationIDs (some of which have changed). The LocMaster table contains location names and location type coding for LocationIDs that are used in many tables. The LocationID field is StreamNet's current location identifier code, and the LocMaster table includes all location code types.

The database now includes ten example queries that demonstrate how to dereference coded information with lookup tables, but users will typically want to customize these queries for their own interests and for building specific reports. It does not include the static maps, photographs, or maps on the fly functionality that you get using StreamNet's online system. It also does not include a user interface at this time, but someone with a little experience with Access should be able to formulate queries to analyze and export desired datasets. None of the tables in this database are protected against change.

StreamNet periodically provides this database to assist our power users who have broad basin-wide analyses to perform. But remember that the StreamNet online query system does all of the complicated georeferencing behind the scenes, allowing you to download the final result set into applications such as Excel or Access.

Only a few georeferencing problems remain in this version of the database. In the past there have been issues related to distance measures along streams that cross the California Oregon border due to inconsistent hydrographies. The remaining problems are being worked on, and the database will be updated when they are resolved.



Notes on StreamNet Database Data Dump Dated 20080208

This version contains many updates to Trend and count data, Barrier, Dam and Hatchery facilities, and Reference information, as well as updates to georeferencing codes called LocationIDs. The LocMaster table contains location names and location type coding for LocationIDs that are used in many tables. The LocationID field is StreamNet's current location identifier code, and the LocMaster table includes all location code types.

The database does not include the Static Maps, Photographs, or Maps on the fly functionality that you get using StreamNet's online system. It also does not include a user interface at this time, but someone with a little experience with Access should be able to formulate queries to analyze and export desired datasets. None of the tables in this database are protected against change.

By relating LocMaster.LocationID to the LocationID field in the tables named LocxState, LocxCounty, LocxHUC, LocxProvince, LocxSubbasin2001 or LocxRegion2001, you will be able to select only data that pertains to the extent of your interest, as long as you include logical criteria using the BegFt and EndFt fields. E.g., "SELECT DamID, Dam_Name FROM (Dam LEFT JOIN LocxCounty ON Dam.LocationID = LocxCounty.LocationID) WHERE ((LocxCounty.BegFt <= Dam.EndFt) AND (LocxCounty.EndFt >= Dam.BegFt)) and CountyID = 126 Order by Dam_Name"; to retrieve all Dam records within Multnomah County (see County table for appropriate code for other counties). If this sort of query is scary for you, remember that the StreamNet online Fish Data query system does all of the complicated georeferencing behind the scenes, and allows you to download the final result set into applications such as Access.

Some problems exist in this database version regarding routed hydrography being used for California that has created some issues pertaining to distance measures along streams that cross the California Oregon border. These will be completely corrected when StreamNet begins using an improved mixed scale hydrography that has undergone extensive review, but some queries that include cross-border streams may return data from more states, counties, etc. than were used in the query to filter data.



Notes on StreamNet Database Data Dump Dated 20050930

This version contains many updates to Trend and count data, Barrier, Dam and Hatchery facilities, and Reference information, as well as updates to georeferencing codes called LocationIDs. The LocMaster table contains location names and location type coding for LocationIDs that are used in many tables. The LocationID field is StreamNet's current location identifier code, and the LocMaster table includes all location code types.

By relating LocMaster.LocationID to the LocationID field in the tables named LocxState, LocxCounty, LocxHUC, LocxProvince, LocxSubbasin2001 or LocxRegion2001, you will be able to select only data that pertains to the extent of your interest, as long as you include logical criteria using the BegFt and EndFt fields. E.g., SELECT DamID,Dam_Name FROM (Dam LEFT JOIN LocxCounty ON Dam.LocationID = LocxCounty.LocationID) WHERE ((LocxCounty.BegFt<=[Dam].[EndFt]) AND (LocxCounty.EndFt>=[Dam].[BegFt])) and CountyID = 126 Order by Dam_Name; to retrieve all Dam records within Multnomah County (see County table for appropriate code for other counties). If this sort of query is scary for you, remember that the StreamNet online Fish Data query system does all of the complicated georeferencing behind the scenes, and allows you to download the final result set into applications such as Access.

A problem continues to exist in this database version regarding routed hydrography being used for California that has created some issues pertaining to distance measures along streams that cross the California Oregon border. These will hopefully be reviewed and corrected by ODFW, but in the meantime, queries that include some bordering streams may return data from more states, counties, etc. than were used in the query to filter data. Please ignore those extra returned data until we can resolve the border issues and please be aware that the location of some data in Oregon near the southern border needs to be assigned new measures where the stream originates in California due to the stream's starting measure formerly beginning at the border instead of the stream mouth.



Notes on StreamNet Database Data Dump Dated 20040917

This version contains many updates to Trend and count data, Dam and Hatchery facilities, and Reference information, as well as updates to georeferencing codes called LocationIDs. The LocMaster table contains location names and location type coding for LocationIDs that are used in many tables. Many tables in the past have included LLID or QID fields, as well as SuperCode, WaterBody and PointID. Most of those fields are no longer in use, although some remain in certain tables, and some remain in use for certain data categories. The LocationID field is StreamNet's current location identifier code, and the LocMaster table includes all location code types.

By relating LocMaster.LocationID to the QID field in the tables named QxState, QxCounty, QxHUC, QxProvince, QxSubbasin2001 or QxRegion2001, you will be able to select only data that pertains to the extent of your interest, as long as you include logical criteria using the BegFt and EndFt fields. E.g., SELECT DamID,Dam_Name,Owner FROM (Dam LEFT JOIN QxCounty ON Dam.LocationID = QxCounty.QID) WHERE ((QxCounty.BegFt<=[Dam].[EndFt]) AND (QxCounty.EndFt>=[Dam].[BegFt])) and CountyID = 126 Order by Dam_Name; to retrieve all Dam records within Multnomah County (see County table for appropriate code for each county). If this sort of query is scary for you, remember that the StreamNet online Fish Data query system does all of the complicated georeferencing behind the scenes, and allows you to download the final result set into applications such as Access.

A problem is known to exist in this database version. New routed hydrography is being used for California that has created some issues pertaining to distance measures along streams that cross the California Oregon border. These are currently being reviewed and corrected by ODFW, but in the meantime, queries that include some bordering streams may return data from more states, counties, etc. than were used in the query to filter data. Please ignore those extra returned data until we can resolve the border issues and please be aware that the location of some data in Oregon near the southern border needs to be assigned new measures where the stream originates in California due to the stream measure formerly beginning at the border instead of the stream mouth.



Notes on StreamNet Database Data Dump Dated 20030313

This version still contains very few changes to HatchRelData, Reference and Habitat Restoration tables, but does include many updates to Trend and count data, Dam and Hatchery facilities, and includes updates to georeferencing codes for Provinces and Subbasins adopted in 2001 by the NWPPC for Subbasin Planning.

You won't find details about QIDs and Qx* tables in the Data Exchange Standard, so here's a brief explanation. Many tables include a QID field which contains the appropriate location code identified by the LocTypeID, i.e. LLID or SuperCode or WaterBody or PointID. This QID field will link easily to the QMaster table to provide a name for any location, the QxState table to return the State, and the QxCounty, QxRegion, QxSubbasin and QxHUC tables to return those values that are appropriate for each QID. The Qx tables were created because of the need to have a single field for the Web Query System to query for these lookups. StreamNet is considering eliminating the various LLID, SuperCode, WaterBody and PointID for many tables, and renaming the QID field in these tables to LocationID, while retaining the functionality of the current QID field. There is a Sample Query in the database that demonstrates the logic of relating location codes in data to the Qx* tables; the example allows you to query the Trend table for data in any particular Subbasin.



Notes on StreamNet Database Data Dump Dated 20020702

This version still contains very few changes to HatchRelData, Reference and Habitat Restoration tables. This edition does include many updates to Trend and count data, Dam and Hatchery facilities, and includes additional georeferencing codes for Provinces and new Subbasins recently adopted by the NWPPC for Subbasin Planning.

You won't find details about QIDs and Qx* tables in the Data Exchange Standard, so here's a brief explanation. Many tables include a QID field which contains the appropriate location code identified by the LocTypeID, i.e. LLID or SuperCode or WaterBody or PointID. This QID field will link easily to the QMaster table to provide a name for any location, the QxState table to return the State, and the QxCounty, QxRegion, QxSubbasin and QxHUC tables to return those values that are appropriate for each QID. The Qx tables were created because of the need to have a single field for the Web Query System to query for these lookups.



Notes on StreamNet Database Data Dump Dated 20010801

This version contains very few changes to HatchRelData, Reference and Habitat Restoration tables , all of which are high priorities to work on. The database has actually shrunk in the 13 months since it was last posted on-line, largely due to the removal of many tables such as the AIWP projects (now available at the CBFWA web site) and tables supporting the old river reach system or early attempts at 100K georeferencing. This edition does include many updates to Trend and count data, as well as more complete population of tables such as Waterbody.

One major change is the addition of the Qx tables, as we call them around here. While the Trend table mirrors the Data Exchange Standard, it also includes a QID field at the end which contains the appropriate location code identified by the LocTypeID, i.e. LLID or SuperCode or WaterBody or PointID. The QID and LocTypeID fields will link easily to the QMaster table to provide a name for any location, the QxState table to return the State, and the QxCounty, QxRegion, QxSubbasin and QxHUC tables to return those values. The Qx tables were created because of the need to have a single field for the Web Query System to query for these lookups. Though the QID field represents some redundancy within the Trend table, it eliminates the need to have LLIDxState, WaterbodyXState, CountyXState, etc. Given that we already have the LocTypeID coding and only one logical location per TrendID, we may want to remove some of the redundancy in the future, after careful assessment of any required reprogramming costs with user interface tools and agency database structures. For many needs, we find the Qx tables and the QMaster table much easier to use than linking to various location tables, so we've included them for you to use as well. If you need an LLIDxState table, etc., you can create one with the Build an LLIDxState table example "make table query" included. If you would want just one state, add that StateID to the criteria. Additional LLIDx tables such as LLIDxCounty would be made in a similar way using the QxCounty table.