mwbugs_dir <- paste0(wd, "mwbugs/")
sql_drv <- RSQLite::SQLite()1 Introduction
Melbourne Water (MW) have been investing in biological monitoring and ecological research using stream macroinvertebrate assemblage composition since 1994. The resulting body of data is a rich resource of information on the biodiversity, ecology, and condition of Melbourne’s streams, which has been used in many studies that have advanced stream ecology and has provided a strong evidence base for decisions on the management of Melbourne’s streams. The data set has strong potential to contribute to further advances and to future management strategies. By collating the data into a database with an open-access web interface, we aim to maximise that potential.
The database includes macroinvertebrate assemblage composition data collected in the Melbourne Region since 1993. Most of the data were collected as part of monitoring projects commissioned by MW. The database also includes data collected from many research projects, most of which were, at least in part, funded by MW, and some data collected by the Victorian Environmental Protection Authority (EPA Victoria) in the region.
MW’s biological monitoring program began in 1994 as the Streamwatch program with the aim of
“using biological indicators (aquatic macroinvertebrates) to determine the health of streams throughout the Port Phillip and Westernport region, including determining broad-scale changes in the longer-term (5-20 years) based on infrequent surveys” (Butcher 2003).
The program began at a high point of interest in using macroinvertebrate assemblage composition as a biological indicator of stream health (Norris and Norris 1995). It co-incided with an expansion of the statewide monitoring program of the EPA, which led to biological objectives for the rivers and streams of Victoria (EPA Victoria 2004) to support the State Environment Protection Policy (Government of Victoria 2004). In 1994 and 1995, the efforts of MW’s and EPA Victoria’s biological monitoring programs and a research study by the Cooperative Research Centre for Freshwater Ecology (CRCFE, Walsh et al. 2001) led to 173 sites being sampled across the region in that year alone, including 30 sites being sampled independently by different sampling teams within weeks of each other. Since that enthusiastic start, monitoring expenditure has been lower and generally better co-ordinated.
Since then, MW’s annual monitoring program has continued will little interruption, albeit with occasional changes in focus and rationale. In most years since 1994, ~50 sites have been sampled using rapid bioassessment methods as part of the core monitoring program, with a largely different selection of sites each year. In the first decade, each year’s monitoring results were recorded in technical reports (Smith, Vertessy, and Hardwick 1997; Hardwick and Lewin 1999; Hardwick and Waller 1999a; Papas, Nicol, and Crowther 2000; Crowther, Canale, and Papas 2001; Papas et al. 2002; Crowther, Papas, and MacKay 2003; WSL 2004, 2005). From 2005, data from the monitoring program were delivered without formal reports.
In addition to the core monitoring program, monitoring studies of individual rivers or regions were conducted during the first decade: Diamond and Stony Creeks (Cameron and Vertessy 1995, 1999); Woori Yallock Creek (1997-1998, no report fond); Watts River (Coleman and Pettigrove 1998b); Andersons and Jumping Creeks (Coleman and Pettigrove 1998a); Dandenong Valley streams (Pettigrove and Coleman 1999); Lang Lang River (Coleman and Pettigrove 2001); Westernport and Mornington Peninsula streams (Hardwick 1998); Plenty River (Hardwick and Waller 1999b); Merri and Darebin Creeks (AWT 1999); Moonee Ponds Creek (2000, no report found); streams of the Werribee catchment (2005, no report found); streams of the Maribyrnong catchment (2006-2007, no report found).
From 2006 on, the monitoring program concentrated more on repeated monitoring of sites aimed at addressing specific concerns such as: continued monitoring of the near-pristine sites of the upper Yarra catchment (including two sites that had been sampled annually by EPA Victoria until that time); change in condition in sites downstream of urban growth areas in the Merri and Toomuc catchments, and of the experimental catchments of Little Stringybark and Dobsons Creek (see below); recovery of streams following bushfires; and repeated monitoring of several focus sites across the region. In 2018, a formal sampling design aiming to sample across primary environmental gradients in the region (rainfall, urban land use, and forest cover) was employed to permit a comparison of traditional sampling and processing methods with emerging genetic methods (Walsh 2018).
In subsequent years, MW allocated dedicated capital funding to expand that design to cover ~ 130 sites which have almost all been sampled twice annually since, including in spring 2021 when two sets of sample pairs were collected to assess the comparability of traditional processing methods with bulk processing for DNA metabarcoding identification. This work remains in progress in 2023.
Melbourne Water also conducted studies addressing particular management questions, such as: the effects of resnagging in the Little Yarra River (Coleman 2006; McCasker 2002) and Eummemmering Creek (2003, no report found); supporting the Woori Yallock streamflow management plan (2013, no macroinvertebrate-specific report found); the management of environmental flows from the Merrimu Reservoir (Walsh, Bloink, and Hehir 2014); and a broad-scale assessment of the capital works program from 2006 to 2015 (Water Technology, GHD, and Streamline Research 2014).
In addition to all of the above monitoring data collected by MW, the database includes data collected from 1992 to 2009 by EPA Victoria from 239 sites across the region as part of their statewide monitoring program.
The database also includes data collected as part of research programs conducted in the region: the CRCFE projects ‘Biological Assessment of Urban Streams’ (Walsh et al. 2001), experimental assessment of riffle restoration in urban streams (Walsh and Breen 2001; Walsh et al. 2023), the Yarra River ecological study (Walsh et al. 2007), assessment of the effects of stormwater treatment wetlands on stream ecology (Walsh 2004a); assessment of urban stormwater impacts on small stream ecosystems (Walsh 2004b, 2006). This last project led to the long-term monitoring of 11 sites on 7 streams to assess the effects of urban stormwater drainage retrofit on the stream health of Dobsons Creek, and Little Stringybark Creek and its tributaries [Walsh et al. (2015); Walsh, Burns, et al. (2022); Walsh, Imberger, et al. (2022); with further papers in preparation]. Associated with that project was a study of longitudinal variation in assemblage composition along streams of varying urban impact (White 2018), and an experiment on how urban impacts diminish the biodiversity benefit of increasing habitat complexity in streams (White and Walsh 2020).
Melbourne Water’s data was first compiled into a central database in 2002 (Walsh and McCasker 2002). GHD managed the database until 2018. This report details the compilation of a revised database that:
includes data not previously included; corrects errors; revises the site and sample-coding to integrate the data with new stream and catchment data (Walsh 2023) and more effectively capture differences in collection and processing methods;
revises the storage of taxonomic data in preparation for new data using identifications from genetic analyses; and
is publicly available, including through a searchable web-interface.
The aims of this document are to:
Describe the structure of the database, including instructions on how to use it;
Describe quality control protocols used to enter data into the database (the database is an active resource, and will be updated as new data are generated);
Describe the web interface for the database.
###FROM MWSTR..something similar here for this database?
1.1 Accessing the data
The examples in this manual access the database through the program R (R Core Team 2023) using the SQLite driver through the RSQLite package (Müller et al. 2022) to connect to the geopackage files, and the terra package (Hijmans 2023) to read the raster tif files. All database files should be downloaded to a single directory to permit connection to the data. Acessing the point, line and polygon tables through a postgreSQL database is substantially faster: see Appendix C for instructions for building a local version of a PostgreSQL database from the gpkg data.
In the following examples, the gpkg file of the database is in a subfolder of the working directory wd, named ‘mwbugs’, thus:
Rather than load the entire contents of the files, we create an SQLite database connection to the gpkg file.
db_m <- RSQLite::dbConnect(sql_drv, paste0(mwbugs_dir,"mwbugs_public.gpkg"))Spatial tables are opened with sf::st_read(), and non-spatial tables are opened with SQLite::dbReadTable()
# Spatial tables
sites <- sf::st_read(db_m, "sites")
# Non-spatial tables
samples <- DBI::dbReadTable(db_m, "samples")
# Note that dates may have been saved as numeric values in conversion to gpkg
samples$date <- as.Date(samples$date, origin = lubridate::origin)
# NOTE: database connections should be disconnected after data is extracted
DBI::dbDisconnect(db_m)Geopackage files can also be treated as databases in GIS software programs, and SQL queries applied in similar ways. Further examples of working with the database using SQL queries can be found in Chapters 2–5.