telemetR: Importing New Data- 14 mins
- Upload New GPS Data
- Wrap Up
- telemetR Series
I’ve left out the problem of importing data. Right now the only way to import data is to insert data into the
deployments tables manually. I wanted to use R to deal with data input, however R lacks a flexible method for getting data into a database. It is much easier to use the SQL methods we’ve already learned than to write all the boilerplate code required in R. In this post I’ll talk about a few ways of importing new device deployments.
In order to deploy a device an animal needs to be captured. During a capture data such as sex, age, samples, etc. are recorded and later transcribed (generally to spreadsheets). We can create a table to store this capture related data then push pertinent information to the appropriate tables in the database.
I mentioned that before entering animal capture data we should enter new devices. I like to enter new GPS devices as the come in from the vendor that way I don’t have to worry about it when entering animal data.
Here are GPS collar devices that are going to be deployed over the next few sections. This is copy and pasted from a CSV.
For the first example we are going to deploy the collar with serial number GSM01438.
Insert Capture Data
The captures table has one new record, a female roe deer captured on October 16, 2005. This date is also the deployment date for this animal.
Parsing Capture Data
The capture data needs to pushed into the
deployments table so that GPS data can be imported. First we will insert data to
animals then to
Now the tricky part, grab
captures.inservice and INSERT them into the
deployments for each captured animal. First lets write the SELECT query to get the right data. Once we are certain we have the correct data we can INSERT. Be sure to double check for sanity 1.
Before we automate this process let’s think about the order of the steps involved.
- New devices are added to
- Capture data is added to
- Animal data from
capturesin INSERTed into
- perm_id, sex, age, species, notes
- For each new record in
captures.cap_dateare inserted into
Let’s test the function by inserting a new capture into
Everything works3! Let’s enter the rest of the capture data for the collars that have been deployed.
From now on all we need to do is continue entering data into
captures then we are ready to enter GPS data into
A deployment is in service as long as the collar is on the animal and is transmitting data. A collar can fall off (or removed from) the animal, stop transmitting data, or the animal can be killed. The date that any of these events occur is the outservice date and should be recorded in the database. Below are the outservice dates for the 5 collars we just deployed.
We need to UPDATE
deployments.outservice to these dates for each deployments. Like before, first we will make sure we select the correct data, then run the UPDATE query.
Repeat this process for each device and outservice date. Again, check that the updates worked properly by running the SELECT query without the where clause. This may now be the most difficult part of managing collar data. Every time a collar goes out of service the date needs to be inserted with this UPDATE statement.
Upload New GPS Data
Here is a CSV file of GPS locations for the collars we just deployed in the database. Let’s upload the collars so that we can use them in a future post.
In the last post we extended the database using PostGIS. We wrote a trigger that created
telemetry.geom when new data is added to the table. Instead of checking the data is properly inserted with a boring SQL statement we can use PostGIS to build an MCP then calculate the home range. This homerange isn’t accurate. There are some points that are unrealistic. In a later post we will review methods for removing these points from
telemetry. Before we can do that we should figure out how to visualize the data.
Below is the output of the SELECT statement above. I’ve deleted the two initial animals (4416 and 4584) from the telemetry table. That is why they are excluded from this query.
Here is a map the data.
We can now easily manage data moving from
devices into the
deployments tables by entering capture data in a spreadsheet. This is probably the most common practice for most wildlife researchers. In my experience I’ve seen wildlife data managed in a variety of ways. Hopefully this series will start pushing wildlife data management out of Excel and into relational database management systems.
In the next post I will talk about using R to get data out of the database and do some basic animal movement data munging and visualization.
- Creating an Animal Movement Database
- Extending the Database with PostGIS
- Importing New Data
- QA/QC with Spatial Attributes
- Connecting to the Database with R
- Adding More SQL Functionality
- Shiny Web Application
- A Simple RESTful API
- … more tbd
Remember that INNER JOINS can be done two way. One, using the INNER JOIN and ON keywords. Two, in the WHERE clause. ↩
Adding new collars should always be the first step. Before any collar is deployed and new capture data is added to the database the collar data must be in
If the trigger didn’t work for you be sure to double check that all the semi-colons and fields are present in the function. Then check that the trigger is on the correct table (