In 2004, the National River Restoration Scientific Synthesis (NRRSS) database was compiled by a team of ~20 scientists. This was the first-ever comprehensive database on stream and river restoration projects. It includes basic information on more than 37,000 projects across the U.S. It resulted in many publications with the broad national-level results published in Science. Lead scientists for the project were: M.A. Palmer (Univ of Maryland), J.D. Allan (Univ Michigan), and E.S. Bernhardt (postdoc at Univ of Maryland; now faculty at Duke University). The project included a collaboration with American Rivers and benefitted from funding from the National Center for Ecological Analysis and Synthesis, the Packard Foundation, the C.S. Mott Foundation; cyber support was provided by the U.S. Geological Survey.
Bernhardt, Emily S.; Palmer, Margaret A.; Allan, J. David; Alexander, Gretchen; Barnas, Katie; Brooks, Shane; Carr, Jamie; Clayton, Steve; Dahm, Clifford; Follstad-Shah, Jennifer; Galat, David L.; Gloss, Steven P.; Goodwin, Peter; Hart, David; Hassett, Brooke; Jenkinson, Robin; Katz, Stephen L.; Kondolf, G. Mathias; Lake, P. Sam; Lave, Rebecca; Meyer, Judy; O’Donnell, T. Kevin; Pagano, Laura; Powell, Bruce; Sudduth, Elizabeth. 2005. Synthesizing US river restoration efforts. Science. Vol: 308(5722). Pages 636-637.
Palmer, Margaret A.; Bernhardt, Emily S.; Allan, J. David; Lake, P. Sam; Alexander, Gretchen; Brooks, Shane; Carr, Jamie; Clayton, Steve; Dahm, Clifford; Follstad-Shah, Jennifer; Galat, David L.; Loss, S. G.; Goodwin, Peter; Hart, David; Hassett, Brooke; Jenkinson, Robin; Kondolf, G. Mathias; Lave, Rebecca; Meyer, Judy; O’Donnell, T. Kevin; Pagano, Laura; Sudduth, Elizabeth. 2005. Standards for ecologically successful river restoration. Journal of Applied Ecology. Vol: 42. Pages 208-217.
Palmer, Margaret A.; Allan, J. David; Meyer, Judy; Bernhardt, Emily S. 2007. River restoration in the Twenty-First Century: Data and experiential knowledge to inform future efforts. Restoration Ecology. Vol: 15(3). Pages 472-481
Bernhardt, Emily S.; Sudduth, Elizabeth; Palmer, Margaret A.; Allan, J. David; Meyer, Judy; Alexander, Gretchen; Follstad-Shah, Jennifer; Hassett, Brooke; Jenkinson, Robin; Lave, Rebecca; Rumps, Jeanne M.; Pagano, Laura. 2007. Restoring rivers one reach at a time: Results from a survey of U.S. river restoration practitioners. Restoration Ecology. Vol: 15(3). Pages 482-493.
Barnas, Katie; Katz, Stephen L. 2010. The Challenges of tracking Habitat Restoration at Various Spatial Scales. Fisheries. Vol: 35(5). Pages 232-241.
Brooks, Shane; Lake, P. Sam. 2007. River restoration in Victoria, Australia: Change is in the wind, and none too soon. Restoration Ecology. Vol: 15(3). Pages 584-591.
Follstad-Shah, Jennifer; Dahm, Clifford; Gloss, Steven P.; Bernhardt, Emily S. 2007. River and riparian restoration in the southwest: Results of the National River Restoration Science Synthesis Project. Restoration Ecology. Vol: 15(3). Pages 550-562.
Hassett, Brooke; Palmer, Margaret A.; Bernhardt, Emily S. 2007. Evaluating stream restoration in the Chesapeake Bay watershed through practitioner interviews. Restoration Ecology. Vol: 15(3). Pages 563-572.
Katz, Stephen L.; Barnas, Katie; Hicks, Ryan; Cowen, Jeff; Jenkinson, Robin. 2007. Freshwater habitat restoration actions in the Pacific Northwest: A decade’s investment in habitat improvement. Restoration Ecology. Vol: 15(3). Pages 494-505.
Kondolf, G. Mathias; Anderson, Shannah; Lave, Rebecca; Pagano, Laura; Merenlender, Adina M.; Bernhardt, Emily S. 2007. Two decades of river restoration in California: What can we learn?. Restoration Ecology. Vol: 15(3). Pages 516-523.
Rumps, Jeanne M.; Katz, Stephen L.; Barnas, Katie; Morehead, Mark; Jenkinson, Robin; Clayton, Steve; Goodwin, Peter. 2007. Stream restoration in the Pacific Northwest: Analysis of interviews with project managers. Restoration Ecology. Vol: 15(3). Pages 506-515.
Sudduth, Elizabeth; Meyer, Judy; Bernhardt, Emily S. 2007. Stream restoration practices in the southeastern United States. Restoration Ecology. Vol: 15(3). Pages 573-583.
Tompkins, Mark R.; Kondolf, G. Mathias. 2007. Systematic postproject appraisals to maximise lessons learned from river restoration projects: Case study of compound channel restoration projects in northern California. Restoration Ecology. Vol: 15(3). Pages 524-537.
NRRSS is available as an sqlite database (13.7 MB) that you can download by clicking this link. The NRRSS is a relational database, organized into 45 tables with pre-defined relationships between them. The primary method for interacting with a relational database is by using Structure Query Language (SQL). Information about development of the original database is available in the Supporting Online Material of the original Science publication here.
Here are some tools for viewing information once you have downloaded the file:
The RSQLite package provides an interface to working with .sqlite files in R. Create a connection object using the dbConnect() function and specifying the location of the file:
# install.packages("RSQLite")
library(RSQLite)
db <- dbConnect(SQLite(), "nrrss.sqlite")List all tables in the database:
dbListTables(db)## [1] "activities_other_table" "area_unit_table"
## [3] "country_table" "dollar_table"
## [5] "flow_num_nrrss_num_table" "flow_table"
## [7] "geo_indicator_table" "geographic_table"
## [9] "linear_unit_table" "memo_table"
## [11] "misc_act_num_nrrss_num_table" "miscellaneous_activities_table"
## [13] "monitor_num_nrrss_num_table" "monitoring_focus_table"
## [15] "new_user_table" "nrrss_node_table"
## [17] "nrrss_record_table" "nrrss_user_table"
## [19] "p_table" "phone_conversations_table"
## [21] "prev_rem_inp_num_nrrss_num_table" "prev_rem_inputs_table"
## [23] "proj_activities_table" "proj_contact_table"
## [25] "proj_ident_table" "proj_intent_num_nrrss_num_table"
## [27] "proj_intent_table" "proj_year_cat_table"
## [29] "record_last_updated_table" "shape_num_nrrss_num_table"
## [31] "shape_table" "source_data_cit_num_nrrss_num_table"
## [33] "source_data_cit_table" "source_data_type_table"
## [35] "species_table" "state_terr_num_nrrss_num_table"
## [37] "state_territory_table" "structure_num_nrrss_num_table"
## [39] "structure_table" "user_manager_table"
## [41] "utm_table" "vegetation_num_nrrss_num_table"
## [43] "vegetation_table" "water_num_nrrss_num_table"
## [45] "waterway_table"List the column names in a specific table:
dbListFields(db, "nrrss_record_table")## [1] "nrrss_number" "nrrss_archived"
## [3] "node_number" "reg_vs_nat"
## [5] "source_data_type_number" "geo_indicator"
## [7] "created_by_user_num" "nrrss_created_at"
## [9] "nrrss_manually_updated" "proj_intent_other_text"
## [11] "waterway_other_text" "source_data_type_text"
## [13] "numeration_within_host_doc" "location_with_host_doc"Use SQL queries to view information in a table:
dbGetQuery(db, "SELECT * FROM nrrss_record_table LIMIT 10")## nrrss_number nrrss_archived node_number reg_vs_nat source_data_type_number
## 1 1 Y 10 N 1
## 2 2 N 8 R 7
## 3 3 N 8 R 5
## 4 4 N 8 R 5
## 5 5 N 8 R 5
## 6 6 Y 8 R 5
## 7 7 Y 8 R 5
## 8 8 N 8 R 4
## 9 9 Y 8 R 5
## 10 10 Y 8 R 5
## geo_indicator created_by_user_num nrrss_created_at nrrss_manually_updated
## 1 8 2003-07-14 13:18:02 Y
## 2 23 2003-07-14 14:13:41 Y
## 3 23 2003-07-15 09:14:09 Y
## 4 23 2003-07-15 09:34:57 Y
## 5 23 2003-07-15 10:20:25 Y
## 6 23 2003-07-15 10:49:35 Y
## 7 23 2003-07-16 11:29:14 Y
## 8 23 2003-07-16 11:56:06 Y
## 9 23 2003-07-16 12:30:18 Y
## 10 23 2003-07-16 12:38:37 Y
## proj_intent_other_text waterway_other_text source_data_type_text
## 1
## 2 webpage
## 3
## 4
## 5
## 6
## 7
## 8
## 9
## 10 Prong
## numeration_within_host_doc
## 1
## 2
## 3
## 4
## 5
## 6
## 7
## 8
## 9
## 10
## location_with_host_doc
## 1 Grant #001781960
## 2 webpage
## 3 www.ncsu.edu/SRI>>Watauga>>Cove Creek
## 4 www.ncsu.edu/sri>>Watauga>>Dutch and Clark Creeks
## 5 www.ncsu.edu/sri>>Watauga>>Shawneehaw
## 6 www.ncsu.edu/sri>>Watauga>>Worley Creek
## 7 www.ncsu.edu/sri>>Pine Valley
## 8 whole document
## 9 http://h20.enr.state.nc.us>>Projects>>Payne Dairy
## 10 http://h2o.enr.state.nc.us/wrp>>Projects>>Stone MountainRead an entire table into R:
recs <- dbReadTable(db, "nrrss_record_table")
head(recs)## nrrss_number nrrss_archived node_number reg_vs_nat source_data_type_number
## 1 1 Y 10 N 1
## 2 2 N 8 R 7
## 3 3 N 8 R 5
## 4 4 N 8 R 5
## 5 5 N 8 R 5
## 6 6 Y 8 R 5
## geo_indicator created_by_user_num nrrss_created_at nrrss_manually_updated
## 1 8 2003-07-14 13:18:02 Y
## 2 23 2003-07-14 14:13:41 Y
## 3 23 2003-07-15 09:14:09 Y
## 4 23 2003-07-15 09:34:57 Y
## 5 23 2003-07-15 10:20:25 Y
## 6 23 2003-07-15 10:49:35 Y
## proj_intent_other_text waterway_other_text source_data_type_text
## 1
## 2 webpage
## 3
## 4
## 5
## 6
## numeration_within_host_doc location_with_host_doc
## 1 Grant #001781960
## 2 webpage
## 3 www.ncsu.edu/SRI>>Watauga>>Cove Creek
## 4 www.ncsu.edu/sri>>Watauga>>Dutch and Clark Creeks
## 5 www.ncsu.edu/sri>>Watauga>>Shawneehaw
## 6 www.ncsu.edu/sri>>Watauga>>Worley CreekUse join or filter statements to use information linked to other tables by foriegn keys. For example, node_number refers to different geographic regions and the codes are defined in the table nrrss_node_table.
dbGetQuery(db, "SELECT * FROM nrrss_node_table")## node_number node_abbrev node_name
## 1 1 AU Australia
## 2 2 CA California
## 3 3 LC Lower Cheaspeake
## 4 4 UC Upper Cheaspeake
## 5 5 CG Central Great
## 6 6 UM Upper Midwest
## 7 7 PN Pacific NW
## 8 8 SE Southeast
## 9 9 SW Southwest
## 10 10 IN Internode