Re-­engineering the Business Process of Desert Tortoise Data Collection

John Donoghue, Former Technology Manager at Ironwood Consulting

Ironwood Consulting Inc. (Ironwood) is a biological resources consulting firm in Southern California, and specializes in biological resource management, including desert tortoise permitting and mitigation. Ironwood is known for its successful work on large-­scale solar development projects in the Mojave Desert. Most recently, Ironwood was responsible for the pre-­project surveys, permitting, relocation, and monitoring of tortoises on four large-­scale solar energy projects: Desert Sunlight (3,000 acres), Silver State South (2,400 acres with 104 tortoises), Stateline (1,500 acres with 41 tortoises), and Dry Lake SEZ (serving three solar energy developer clients over a combined 2,000 acres with 45 tortoises). These projects combined required the successful translocation and monitoring of over 200 desert tortoises.

The problem
The large-­scale solar development projects Ironwood was managing required collecting a great deal of field data. During active desert tortoise field seasons, over 70 biologists would be collecting location, health, and other data on desert tortoises moving throughout project sites and offsite mitigation areas. This data was used to determine each tortoise’s territory, ascertain its health and identify other tortoises it may be interacting with. The information was necessary to help plan and ensure the successful translocation of tortoises from the project development site to nearby mitigation preserves, where tortoises would continue to be monitored to confirm they were adjusting to their new homes after the move.

Field biologists worked in remote locations that were often out of cellular voice and data access most of the time. Historically, field biologists collected data on paper forms that were later transcribed into Excel for analysis and reporting. Later, Ironwood adopted a system that used Pendragon Forms for offline field-­based digital data entry. This data was later synced through a server into a Microsoft Access database for storage and use. While the Pendragon Forms system accelerated the process of collecting and digitizing field data, biologists were aggravated by its antiquated interface and wanted forms that worked like the other iOS and Android applications they regularly used, with features that included scrolling pages, advanced data entry constraints on fields, photographs, GPS support and more.

Problems

  • Previous interface lacked modern features and user experience
  • Some data was entered into more than one form to record a single activity
  • Data entry errors were common
  • Data required time-­consuming and expensive manual QA/QC
  • Data available to system users was not relational and could not easily address questions asked by project managers
  • Projects hosted in separate databases with different schemas
  • Lack of standard database schema hampered efforts to gain efficiencies and prevented meta-­analyses across projects

The Pendragon/Microsoft Access system also required field biologists to enter some of the same data into different Pendragon forms, which resulted in many data entry errors and required a great deal of oversight and time-­consuming manual QA/QC. With Ironwood simultaneously managing multiple large-­scale solar projects, the time consuming manual processes that the Pendragon/Microsoft Access system required became very costly. Furthermore, the system wasn’t designed to support multiple end users, and the data products produced by the system were not designed to quickly answer the questions project managers were regularly asked by the solar development clients and agencies. Since each project was hosted in its own separate Access database with varying schemas to accommodate the peculiarities of each project, the absence of a standardized multi-­project database made analyses across projects impractical.

Developing a New Solution
To address these limitations, Ironwood decided to design an entirely new system that could support hosting multiple projects with multiple simultaneous users, while providing a greater variety of data products to address the different needs of field-­biologists, project managements, clients, and agencies. Ironwood began by meeting with their biologists in charge of desert tortoise management to review the shortcomings of the current system and discuss the envision the opportunities a new system could generate. The information from this and subsequent meetings resulted in a new database design that comprised a core set of tables and table fields that were designed to manage the data for multiple projects, and could accommodate all previously collected tortoise data, as well as incoming data for all upcoming desert tortoise projects. To ensure the database could support multiple simultaneous internal and external users working on different projects, the new database was implemented in Microsoft SQL Server. This also provided a foundation to support integrations with GIS other business systems.

iFormBuilder
In looking to replace Pendragon Forms, Ironwood reviewed a number of alternative iOS and Android applications for field-­based data entry. Ultimately, iFormBuilder was selected because it provided several benefits: there were iFormBuilder applications for both iOS and Android; the form building process in iFormBuilder was very intuitive, and the resulting forms had the modern user interface and user experience the field biologists desired. iFormBuilder forms also offered many features Ironwood needed, such as data entry constraints, data input masks, context sensitive field visibility, field validation, GPS support and in-form photos. Finally, the field biologist workflows were often best implemented as sub-­forms, and iFormBuilder sub-­forms were more intuitive to field biologists than the previous Pendragon Forms.

Integrating iFormBuilder and SQL Server
A fundamental goal for Ironwood’s new system was automating previously manual processes, and automating the transfer of data from iFormBuilder’ forms to Ironwood’s SQL Server database was a critical component. Fortunately, iFormBuilder offered an XML Post Data feature that pushes a copy of each record’s data from iFormBuilder to a user defined web page every time a new record is successfully uploaded to the iFormBuilder server.

Ironwood developed an ASP.net web application that contained a set of URL endpoints to receive XML data posted from iFormBuilder. The ASP.net application parsed the incoming XML data and inserted data as new records into Ironwood’s SQL Server database. During this process, the ASP.net application also performed QA/QC on incoming data, standardized data formats, and transformed the location information contained in the posted data into Open Geospatial Consortium (OCG) compliant spatial data for use in Ironwood’s ArcGIS system and other applications. Finally, the ASP.net application sent each user synching data to iFormBuilder an email that informed them of their successful data sync and identified any potential data errors found by the QA/QC processes.

Another goal of the new system was to re-­engineer the data collection business processes to remove duplicate data entry processes and reduce the number of digital forms needed. The previous Pendragon forms were designed to populate single database tables. Field biologists often had to enter some of the same data into different Pendragon forms as they performed a single activity, such as a health assessment – in which biologists completed one Pendragon form to record a tortoise location, and another Pendragon form to record tortoise health data.

In contrast, Ironwood designed its iFormBuilder forms around the activities biologists performed during their tortoise survey and monitoring work. As the data was received by Ironwood’s ASP.net application during syncing, the ASP.net application would automatically populate the appropriate tables. Therefore, a field biologist could use an activity specific iForm to record data for multiple associated activities, and the ASP.net application would sort the received data and populate the data into each relevant table.

Leveraging Better Data
To help provide more expedient and broader access to the data gathered in iFormBuilder, Ironwood also developed an ASP.net website that served as a central hub for field biologists, project managers, clients, and agencies. The resulting web portal supported multiple users and projects, and provided access to data as soon as it was synced from iFormBuilder and pushed into Ironwood’s SQL Server database. The web portal provided dashboards where users could view tortoise statistics and interactive maps of tortoise locations that were dynamically generated from the database using data from the most recent iFormBuilder application sync.

The web portal provided data in different formats for the various types of users the system supported. Field biologists and agencies accessed the portal to download dynamically generated Excel and GPX files that contained the most recently recorded tortoise locations and other information. Project Managers and Clients downloaded dynamically generated KML data for mapping in Google Earth, and viewed dynamic charts and statistics to review project’s status and look for potential issues. Unpublished URL endpoints allowed the data to be brought into ArcGIS Online, Google Earth and other systems.

In addition, by storing the location data as OGC compliant spatial points, the database could be directly accessed by GIS systems, and was persistently linked to Ironwood’s esri ArcGIS system where Python scripts automated daily cartographic quality maps used by field biologists, project managers, and clients.

Results
The better data input constraints provided in iFormBuilder significantly reduced Ironwood’s QA/QC needs. Despite having developed an automated backend QA/QC process, Ironwood later found that 95% of the data pushed into its database had no potential QA/QC issues identified by that process as the errors were constrained during data-­entry in iForms.

Field biologists were especially pleased with the new system. The iFormBuilder forms provided a modern user interface and user experience they expected, and iFormBuilder features such as designating the default device keyboard for fields, data input masks, and context sensitive field visibility, significantly increased the speed with which biologists could enter data into iForms, while being confident that data was accurate.

Results

  • Considerably fewer data errors
  • Increased adoption of digital forms
  • Greater trust in data
  • Much faster data turn-­around
  • Significantly lower data management costs
  • Improved client confidence in data management process
  • Increased client satisfaction
  • Improved agency cooperation
  • Stronger client relationships

The iFormBuilder XML Push workflow has greatly benefited field biologists by ensuring that the data the biologists recorded and synched was instantly added to Ironwood’s SQL Server database and biologists were immediately informed of its availability.

By developing a data portal website, Ironwood was able to leverage iFormBuilder and SQL Server to provide biologists, project managers, clients and agencies with access to all data anytime they needed it. Clients have praised the system and are using it regularly to keep informed on the progress of the mitigation programs for their projects. By making data collection processes transparent and providing clients with readily accessible data in the formats they needed, Ironwood was able to better communicate the data collection effort with its clients which reinforced the client’s trust in Ironwood’s data collection processes.

Though considerable effort was involved in reaching agreement with on a new database design and data input forms that bridged the gap between the needs of the field biologists and Ironwood’s end-­users, by implementing an integrated iFormBuilder, SQL Server and ASP.net solution, Ironwood was able to reengineer the business process of desert tortoise data collection. While the entire system took time to develop, the major benefits were realized when the system was fully implemented on multiple projects, where it essentially ran itself with very little intervention, resulting in significantly reduced data management and reporting costs, ultimately transforming a once cost-­intensive activity into a financial and practical benefit to Ironwood’s clients.

Revisiting the North Etiwanda Preserve

Sage Scrub Chaparral

Sage Scrub Chaparral

When I studied population biology in junior college we conducted our field work in a large expanse of land along the alluvial fan that drained the San Gabriel Mountains to the Inland Empire valley below. Our specific field site was a 750+ acre area containing coastal sage scrub and coastal sage chaparral plant communities that were intersected by riparian corridors of alder, willow and occasionally sycamore trees.

Coastal sage scrub is a type of ecoregion that is located along the southern and central coast of California. It is important because is an endangered ecosystem that contains many unique species that occur nowhere else in the world (endemic), a number of which are endangered species. Coastal sage scrub is imperiled because it is also located on highly valued coastal real estate and threatened by human development.

Coastal sage scrub occurs from sea level to 1500 feet in elevation, along the coastal and inland valley foothills where coastal fog moderates the climate. Habitat contains sparse, low-growing soft, aromatic shrubs that range from dull-green to gray-green in color. In hot, dry summer months, shrubs often lose their leaves and become dormant as an adaptation to drought tolerance. They become green and vibrant again when winter rains arrive.

Some characteristics species in coastal sage scrub communities include:

Federally recognized threatened and endangered species include the California gnatcatcher (Polioptila californica), the San Diego banded gecko (Coleonyx variegatus abbottii ), the cactus wren (Campylorhyncus brunneicapillus), Merriam kangaroo rat (Dipodomys merriami), flannel-mouthed sucker (Catostomus latipinnis), western patch-nosed snake (Salvadora hexalepis), and cheese-weed moth lacewing (Chrysoperla spp.). Thirteen plant species of the coastal sage scrub are also recognized as threatened or endangered. (1)

A few years later the area began rapidly developing and our field area was threatened by a series of housing developments. Through the efforts of many people who fought to see the area protected to stem the decline of chaparral and coastal sage communities, a 762 acre section was set aside as a reserve. The area lies between the developed portions to the south and the national forest to the north and is protected on the southern end by a very wide power transportation corridor that serves as a buffer between the two land uses.

Welcome Sign

Welcome Sign

After several years of being away from this site, I recently revisited it yesterday afternoon and was surprised to see signs pointing the way to the preserve as I drove to the parking area. I was further surprised to see a dozen cars in the parking area at 3 pm on a Tuesday. When I last visited the site, it was protected, but still the completely undeveloped area that few people ever ventured into.

Trail Map

I quickly saw that the preserve has since been enhanced with formal trails, interpretive signs, and a few scattered picnic sites. It was now a popular place for hiking due to its cultural and recreational significance. Many people were taking a new trail that lead to a side canyon that contained a waterfall and shade beneath the alder trees. Others hiked around the lower portion of the preserve stopping at gazebos and interpretive signs.

While it was strange to see so many people in a place I used to walk around without seeing a soul, I was thrilled to see that the plant communities I fondly remembered were still there and appeared to be thriving. Walking through the scrub and chaparral I was transported back in time to my first population biology class, awestruck by the sight and smell of white sage and asking myself the same questions about the distributions of the plants I encountered along my hike.

Walking along one of the trails I saw massive shifts in the vegetation communities along a 1-mile length. Vegetation changed from stands that were dominated by white sage, to stands co-dominated by chemise and buckwheat containing little white sage. Walk a little further and the vegetation became co-dominated by black sage and sagebrush. Along this stretch I could not detect a difference in soil texture or aspect. So the sudden shifts of vegetation may the result of secondary successional processes where fire and floods disturb the vegetation and a different plant community begins growing in the areas that were disturbed. The overall pattern looked like waves of different vegetation along the hillside that was fascinating and worthy of more examination. Some of those communities are shown in the photos below.

Preserve Facing North West

Preserve Facing NW

White Sage

White Sage

White Sage Community

White Sage Community

Chemise and Buckwheat

Chemise and Buckwheat

Black Sage and Sagebrush

Black Sage and Sagebrush

Alder Riparian Woodland

Alder Riparian Woodland

Intersecting what I used to think was scorching hot chaparral (I’ve since lived in Arizona, so I have a new definition of hot) are wonderfully different riparian habitats containing cool, lush, shade tolerant species and – water. Water is a powerful force in these regions, winter rains and spring meltwater can move large amounts of water down the mountain through these canyons. Occasional fires remove vegetation and later rains bring floods that break trees and push large granite boulders downstream too.  These processes cause regular disturbances in the canyons and adjacent lands, and the vegetation is always in some state of recovery. The dense canopy of alder trees in the canyon bottoms is evidence of that those areas were previously disturbed, new trees grew to replace those that were killed, but self-thinning has not yet completed restructuring the tree stand.

Riparian Waterfall

Riparian Waterfall

Alder Woodland

Alder Woodland

The Southern California coastal sage scrub and chaparral communities are a wonderful place to explore with very interesting ecology that is worthy of protection and study. I’m happy that the North Etiwanda Preserve exists and look forward to continued visits.

 

 

For more information about the North Etiwanda Preserve see: http://sbcnep.org/

Photos of common coastal sage scrub and chaparral plants (from the nearby Cal State San Bernardino campus) can be seen at: http://biology.csusb.edu/PlantGuideFolder/

Finally, more information about coastal sage scrub and chaparral can be found at: http://www.eoearth.org/article/California_coastal_sage_and_chaparral

Happy exploring !

(1) From: http://www.eoearth.org/article/California_coastal_sage_and_chaparral