Spreadsheets, ArcGIS, and Programming! Oh My!

By Morgan O’Rourke-Liggett, Master’s Student, Oregon State University, Department of Fisheries, Wildlife, and Conservation Sciences, Geospatial Ecology of Marine Megafauna Lab

Avid readers of the GEMM Lab blog and other scientists are familiar with the incredible amounts of data collected in the field and the informative figures displayed in our publications and posters. Some of the more time-consuming and tedious work hardly gets talked about because it’s the in-between stage of science and other fields. For this blog, I am highlighting some of the behind-the-scenes work that is the subject of my capstone project within the GRANITE project.

For those unfamiliar with the GRANITE project, this multifaceted and non-invasive research project evaluates how gray whales respond to chronic ambient and acute noise to inform regulatory decisions on noise thresholds (Figure 1). This project generates considerable data, often stored in separate Excel files. While this doesn’t immediately cause an issue, ongoing research projects like GRANITE and other long-term monitoring programs often need to refer to this data. Still, when scattered into separate long Excel files, it can make certain forms of analysis difficult and time-consuming. It requires considerable attention to detail, persistence, and acceptance of monotony. Today’s blog will dive into the not-so-glamorous side of science…data management and standardization!

Figure 1. Infographic for the GRANITE project. Credit: Carrie Ekeroth

Of the plethora of data collected from the GRANITE project, I work with the GPS trackline data from the R/V Ruby, environmental data recorded on the boat, gray whale sightings data, and survey summaries for each field day. These come to me as individual yearly spreadsheets, ranging from thirty entries to several thousand. The first goal with this data is to create a standardized survey effort conditions table. The second goal is to determine the survey distance from the trackline, using the visibility for each segment, and calculate the actual area surveyed for the segment and day. This blog doesn’t go into how the area is calculated. Still, all these steps are the foundation for finding that information so the survey area can be calculated.

The first step requires a quick run-through of the sighting data to ensure all dates are within the designated survey area by examining the sighting code. After the date is a three-letter code representing a different starting location for the survey, such as npo for Newport and dep for Depoe Bay. If any code doesn’t match the designated codes for the survey extent, those are hidden, so they are not used in the new table. From there, filling in the table begins (Figure 2).

Figure 2. A blank survey effort conditions table with each category listed at the top in bold.

Segments for each survey day were determined based on when the trackline data changed from transit to the sighting code (i.e., 190829_1 for August 29th, 2019, sighting 1). Transit indicated the research vessel was traveling along the coast, and crew members were surveying the area for whales. Each survey day’s GPS trackline and segment information were copied and saved into separate Excel workbook files. A specific R code would convert those files into NAD 1983 UTM Zone 10N northing and easting coordinates.

Those segments are uploaded into an ArcGIS database and mapped using the same UTM projection. The northing and easting points are imported into ArcGIS Pro as XY tables. Using various geoprocessing and editing tools, each segmented trackline for the day is created, and each line is split wherever there was trackline overlap or U shape in the trackline that causes the observation area to overlap. This splitting ensures the visibility buffer accounts for the overlap (Figure 3).

Figure 3. Segment 3 from 7/22/2019 with the visibility of 3 km portrayed as buffers. There are more than one because the trackline was split to account for the overlapping of the survey area. This approach accounts for the fact that this area where all three buffers overlap was surveyed 3 times.

Once the segment lines are created in ArcGIS, the survey area map (Figure 4) is used alongside the ArcGIS display to determine the start and end locations. An essential part of the standardization process is using the annotated locations in Figure 4 instead of the names on the basemap for the location start and endpoints. This consistency with the survey area map is both for tracking the locations through time and for the crew on the research vessel to recognize the locations. The step assists with interpreting the survey notes for conditions at the different segments. The time starts and ends, and the latitude and longitude start and end are taken from the trackline data.

Figure 4. Map of the survey area with annotated locations (Created by L. Torres, GEMM Lab)

The sighting data includes the number of whales sighted, Beaufort Sea State, and swell height for the locations where whales were spotted. The environmental data from the sighting data is used as a guide when filling in the rest of the values along the trackline. When data, such as wind speed, swell height, or survey condition, is not explicitly given, matrices have been developed in collaboration with Dr. Leigh Torres to fill in the gaps in the data. These matrices and protocols for filling in the final conditions log are important tools for standardizing the environmental and condition data.

The final product for the survey conditions table is the output of all the code and matrices (Figure 5). The creation of this table will allow for accurate calculation of survey effort on each day, month, and year of the GRANITE project. This effort data is critical to evaluate trends in whale distribution, habitat use, and exposure to disturbances or threats.

Figure 5. A snippet of the completed 2019 season effort condition log.

The process of completing the table can be a very monotonous task, and there are several chances for the data to get misplaced or missed entirely. Attention to detail is a critical aspect of this project. Standardizing the GRANITE data is essential because it allows for consistency over the years and across platforms. In describing this aspect of my project, I mentioned three different computer programs using the same data. This behind-the-scenes work of creating and maintaining data standardization is critical for all projects, especially long-term research such as the GRANITE project.

Did you enjoy this blog? Want to learn more about marine life, research, and conservation? Subscribe to our blog and get a weekly message when we post a new blog. Just add your name and email into the subscribe box below.

Loading

The learning curve never stops as the GRANITE project begins its seventh field season

Clara Bird, PhD Student, OSU Department of Fisheries, Wildlife, and Conservation Sciences, Geospatial Ecology of Marine Megafauna Lab

When I thought about what doing fieldwork would be like, before having done it myself, I imagined that it would be a challenging, but rewarding and fun experience (which it is). However, I underestimated both ends of the spectrum. I simultaneously did not expect just how hard it would be and could not imagine the thrill of working so close to whales in a beautiful place. One part that I really did not consider was the pre-season phase. Before we actually get out on the boats, we spend months preparing for the work. This prep work involves buying gear, revising and developing protocols, hiring new people, equipment maintenance and testing, and training new skills. Regardless of how many successful seasons came before a project, there are always new tasks and challenges in the preparation phase.

For example, as the GEMM Lab GRANITE project team geared up for its seventh field season, we had a few new components to prepare for. Just to remind you, the GRANITE (Gray whale Response to Ambient Noise Informed by Technology and Ecology) project’s field season typically takes place from June to mid-October of each year. Throughout this time period the field team goes out on a small RHIB (rigid hull inflatable boat), whenever the weather is good enough, to collect photo-ID data, fecal samples, and drone imagery of the Pacific Coast Feeding Group (PCFG) gray whales foraging near Newport, OR, USA. We use the data to assess the health, ecology and population dynamics of these whales, with our ultimate goal being to understand the effect of ambient noise on the population. As previous blogs have described, a typical field day involves long hours on the water looking for whales and collecting data. This year, one of our exciting new updates is that we are going out on two boats for the first part of the field season and starting our season 10 days early (our first day was May 20th). These updates are happening because a National Science Foundation funded seismic survey is being conducted within our study area starting in June. The aim of this survey is to assess geophysical structures but provides us with an opportunity to assess the effect of seismic noise on our study group by collecting data before, during, and after the survey. So, we started our season early in order to capture the “before seismic survey” data and we are using a two-boat approach to maximize our data collection ability.

While this is a cool opportunistic project, implementing the two-boat approach came with a new set of challenges. We had to find a second boat to use, buy a new set of gear for the second boat, figure out the best way to set up our gear on a boat we had not used before, and update our data processing protocols to include data collected from two boats on the same day. Using two boats also means that everyone on the core field team works every day. This core team includes Leigh (lab director/fearless leader), Todd (research assistant), Lisa (PhD student), Ale (new post-doc), and me (Clara, PhD student). Leigh and Todd are our experts in boat driving and working with whales, Todd is our experienced drone pilot, I am our newly certified drone pilot, and Lisa, Ale, and myself are boat drivers. Something I am particularly excited about this season is that Lisa, Ale, and I all have at least one field season under our belts, which means that we get to become more involved in the process. We are learning how to trailer and drive the boats, fly the drones, and handling more of the post-field work data processing. We are becoming more involved in every step of a field day from start to finish, and while it means taking on more responsibility, it feels really exciting. Throughout most of graduate school, we grow as researchers as we develop our analytical and writing skills. But it’s just as valuable to build our skillset for field work. The ocean conditions were not ideal on the first day of the field season, so we spent our first day practicing our field skills.

For our “dry run” of a field day, we went through the process of a typical day, which mostly involved a lot of learning from Leigh and Todd. Lisa practiced her trailering and launching of the boat (figure 1), Ale and Lisa practiced driving the boat, and I practiced flying the drone (figure 2). Even though we never left the bay or saw any whales, I thoroughly enjoyed our dry run. It was useful to run through our routine, without rushing, to get all the kinks out, and it also felt wonderful to be learning in a supportive environment. Practicing new skills is stressful to say the least, especially when there is expensive equipment involved, and no one wants to mess up when they’re being watched. But our group was full of support and appreciation for the challenges of learning. We cheered for successful boat launchings and dockings, and drone landings. I left that day feeling good about practicing and improving my drone piloting skills, full of gratitude for our team and excited for the season ahead.

Figure 1. Lisa (driving the truck) launching the boat.
Figure 2. Clara (seated, wearing a black jacket) landing the drone in Ale’s hands.

All the diligent prep work paid off on Saturday with a great first day (figure 3). We conducted five GoPro drops (figure 4), collected seven fecal samples from four different whales (figure 5), and flew four drone flights over three individuals including our star from last season, Sole. Combined, we collected two trifectas (photo-ID images, fecal samples, and drone footage)! Our goal is to get as many trifectas as possible because we use them to study the relationship between the drone data (body condition and behavior) and the fecal sample data (hormones). We were all exhausted after 10 hours on the water, but we were all very excited to kick-start our field season with a great day.

Figure 3. Lisa on the bow pulpit during our first sighting of the day.
Figure 4. Lisa doing a GoPro drop, she’s lowering the GoPro into the water using the line in her hands.
Figure 5. Clara and Ale collecting a fecal sample.

On Sunday, just one boat went out to collect more data from Sole after a rainy morning and I successfully flew over her from launching to landing! We have a long season ahead, but I am excited to learn and see what data we collect. Stay tuned for more updates from team GRANITE as our season progresses!