The different challenges of integrating data from many sources

By Juliette Bryan, Data Analyst at the ALA

One of the challenges the Atlas of Living Australia faces is the integration of biodiversity occurrence data in many different forms. Most of our data comes from museums, herbaria, other biological collections, State conservation agencies and BirdLife Australia. While these data are generally well structured, data from other sources may be inconsistent in format – and can be from both amateurs and professionals.

Some of the data we receive has been recorded over many years or decades, by a variety of individuals from a regional group or nature club, so will be of high value for certain research questions. However, even within a single club, members have often adopted their own method for recording the data and, while there are some standards, there is often great inconsistency in the format.

Data from government agencies or organisations with a background in biodiversity data often require very little restructuring before the data can be loaded in to the Atlas of Living Australia.
Before any data is loaded into the ALA it must be mapped to Darwin Core terms. The Darwin Core is a body of standards primarily based on taxa and their occurrence in nature as documented by observations, specimens, and samples, and related information. Darwin Core terms include the basis of record, location, event date, sampling protocol, recorded by, identified by, species common and scientific names and associated media.

When reading and cleaning the more difficult, unstructured data, we use a variety of tools. Open source tools such as MySQL, Talend and Pentaho are all useful in mapping the data to Darwin Core terms. The more structured data usually only require changing the column names to match their corresponding Darwin Core terms and then the data is ready to load.

Example 1: Raw data which will be mapped to Darwin Core terms

Below is the data mapped to Darwin Core terms. As we were given the locality in the original data we used the ALA Spatial Portal (http://spatial.ala.org.au/) to create the columns: coordinatePrecision, coordinateUncertaintyInMeters, decimalLatitude and decimalLongitude.

Below is the same data displayed in the ALA

Example 2: Data digitised from a series of publications

Additionally, we were provided with a lookup table of 259 stations and their locations. As we were given the locality of the station number in the original data, we used the ALA Spatial Portal (http://spatial.ala.org.au/) to create the columns:  Uncertainty in Km, Latitude and Longitude.  A sample of the data is shown below.  The Location Remarks column is used to identify if a particular station is a dredging station or not.

The data was transposed by Station number in order to create 1 row per station. This data was then joined to the Station lookup table to pick up the locality and mapping coordinates. Below is the final data mapped to Darwin Core terms.

Below is the same data displayed in the ALA