telemetR: Importing New Data

- 14 mins

I’ve left out the problem of importing data. Right now the only way to import data is to insert data into the animals, devices, and 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.

Captures

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.

CREATE TABLE captures (
  id serial PRIMARY KEY,
  perm_id varchar(20),
  cap_date date,
  sex varchar(8),
  age varchar(10),
  species varchar(4),
  notes varchar,
  serial_num varchar(50),
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  deleted_at timestamp with time zone
);

COMMENT ON TABLE captures IS 'catalog of captures where a collar is deployed on an animal';

capture data

A data sheet from a bighorn sheep capture in Nevada from 2016. Every animal that is captured has data recorded then is entered into our database. The light grey ear tag is our permanent ID for all the animals we capture.

Device

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.

serial_num,vendor,device_type,mfg_date,frequency
GSM01438,vectronic,gps,2005-01-01,151.33
GSM01508,vectronic,gps,2005-01-01,151.01
GSM01511,vectronic,gps,2005-01-01,151.07
GSM01512,vectronic,gps,2005-01-01,151.089
GSM02927,vectronic,gps,2005-01-01,151.145

For the first example we are going to deploy the collar with serial number GSM01438.

INSERT INTO
  devices (serial_num, vendor, device_type, mfg_date, frequency)
VALUES
  ('GSM01438', 'vectronic', 'gps', '2005-01-01' ,151.33),
  ('GSM01508', 'vectronic', 'gps', '2005-01-01', 151.01),
  ('GSM01511', 'vectronic', 'gps', '2005-01-01', 151.07),
  ('GSM01512', 'vectronic', 'gps', '2005-01-01', 151.089),
  ('GSM02927', 'vectronic', 'gps', '2005-01-01', 151.145);

Insert Capture Data

INSERT INTO
  captures (perm_id, cap_date, sex, age, species, serial_num)
VALUES
  ('F09', '2006-10-16', 'female', 'adult', 'ROED', 'GSM01512');

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 animals and deployments table so that GPS data can be imported. First we will insert data to animals then to deployments

INSERT INTO
  animals (perm_id, sex, age, species, notes)
SELECT perm_id, sex, age, species, notes
FROM captures;

SELECT * FROM animals;

Now the tricky part, grab animals.id, devices.id, and 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.

SELECT
  animals.id AS animal_id,
  devices.id AS device_id,
  captures.cap_date AS inservice
FROM
  (captures INNER JOIN animals ON captures.perm_id = animals.perm_id)
   INNER JOIN devices ON captures.serial_num = devices.serial_num
WHERE captures.perm_id = 'F09';

-- insert
INSERT INTO deployments (animal_id, device_id, inservice)
SELECT
  animals.id AS animal_id,
  devices.id AS device_id,
  captures.cap_date AS inservice
FROM
  (captures INNER JOIN animals ON captures.perm_id = animals.perm_id)
   INNER JOIN devices ON captures.serial_num = devices.serial_num
WHERE captures.perm_id = 'F09';

-- double check
SELECT
  animals.perm_id,
  deployments.animal_id,
  devices.serial_num,
  deployments.device_id,
  deployments.inservice
FROM deployments, animals, devices
WHERE
  deployments.animal_id = animals.id AND
  deployments.device_id = devices.id;

Automation

Before we automate this process let’s think about the order of the steps involved.

  1. New devices are added to devices 2
  2. Capture data is added to captures
  3. Animal data from captures in INSERTed into animals
    • perm_id, sex, age, species, notes
  4. For each new record in captures the animals.id, devices.id, and captures.cap_date are inserted into deployments
CREATE OR REPLACE FUNCTION captures_to_deployments()
RETURNS trigger AS
$BODY$
BEGIN
  -- insert new capture into animal
  INSERT INTO animals (perm_id, sex, age, species, notes)
  SELECT
    NEW.perm_id,
    NEW.sex,
    NEW.age,
    NEW.species,
    NEW.notes
  FROM captures
  WHERE perm_id = NEW.perm_id;

  -- insert new deployment
  INSERT INTO deployments (animal_id, device_id, inservice)
  SELECT
    animals.id AS animal_id,
    devices.id AS device_id,
    captures.cap_date AS inservice
  FROM (captures
    INNER JOIN animals ON captures.perm_id = animals.perm_id)
    INNER JOIN devices ON captures.serial_num = devices.serial_num
  WHERE captures.perm_id = NEW.perm_id;

  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER trigger_captures
  AFTER INSERT
  ON captures
  FOR EACH ROW
  EXECUTE PROCEDURE captures_to_deployments();

Let’s test the function by inserting a new capture into captures.

INSERT INTO
  captures (perm_id, sex, age, species, cap_date, serial_num)
VALUES
  ('M03', 'male', 'adult', 'ROED', '2005-03-20', 'GSM01438');

-- check captures
SELECT * FROM captures;

-- check animals
SELECT * FROM animals;

-- check deployments
SELECT
  animals.perm_id,
  deployments.animal_id,
  devices.serial_num,
  deployments.device_id,
  deployments.inservice
FROM deployments, animals, devices
WHERE
  deployments.animal_id = animals.id AND
  deployments.device_id = devices.id;

Everything works3! Let’s enter the rest of the capture data for the collars that have been deployed.

INSERT INTO
  captures (perm_id, sex, age, species, cap_date, serial_num)
VALUES
  ('M06', 'male', 'adult', 'ROED', '2005-10-23', 'GSM01508'),
  ('F10', 'female', 'adult', 'ROED', '2005-10-21', 'GSM01511'),
  ('M10', 'male', 'adult', 'ROED', '2006-11-12', 'GSM02927');

From now on all we need to do is continue entering data into captures then we are ready to enter GPS data into raw_gps.

Outservice

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.

serial_num | outservice date
-----------+----------------
GSM01438   | 2006-05-27
GSM01508   | 2006-10-28
GSM01511   | 2007-02-09
GSM01512   | 2006-10-29
GSM02927   | 2008-03-15

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.

SELECT
  deployments.device_id,
  devices.id,
  devices.serial_num,
  deployments.inservice,
  deployments.outservice
FROM
  deployments INNER JOIN devices ON deployments.device_id = devices.id
WHERE
  devices.serial_num = 'GSM01438';

-- update
UPDATE deployments t
SET outservice = '2006-05-27'
FROM devices
WHERE t.device_id = devices.id AND
  devices.serial_num = 'GSM01438';

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.

COPY raw_gps
  (serial_num, acq_time, latitude, longitude, altitude,
   hdop, temperature, fix_type, gps_volts)
FROM
  '/Users/mitchellgritts/Dropbox/Data/telemetr/post-data/collars_gsm.csv'
WITH (FORMAT csv, DELIMITER ',', HEADER true);

SELECT
  animals.perm_id,
  ST_Area(
    ST_ConvexHull(
      ST_Collect(telemetry.geom)
      )::geography(Polygon, 4326)
  ) AS homerange
FROM
  telemetry
  INNER JOIN animals ON telemetry.animal_id = animals.id
GROUP BY
  perm_id;

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.

perm_id |    homerange     
---------+------------------
 F10     | 767046954.865112
 M06     | 14334337.6304932
 M03     | 180466181598.254
 M10     | 29118474.1977234
 F09     | 9030989.35189247

Here is a map the data.

map of gps locations

Map of the GPS locations for the five animals we imported. You can see that there is one outlier. Another outlier has been excluded from this photo.

Wrap Up

We can now easily manage data moving from captures and devices into the animals and 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.

telemetR Series

  1. Introduction
  2. Creating an Animal Movement Database
  3. Extending the Database with PostGIS
  4. Importing New Data
  5. QA/QC with Spatial Attributes

upcoming topics

Footnotes

  1. Remember that INNER JOINS can be done two way. One, using the INNER JOIN and ON keywords. Two, in the WHERE clause.

  2. 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 devices.

  3. 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 (captures).

rss facebook twitter github youtube mail spotify instagram linkedin google google-plus pinterest medium vimeo stackoverflow reddit quora