motus.org

Description Usage Arguments Value Author(s)

Creates a view (called 'bfj' for 'Big Fat Join') of tag detections from a motus database and associated metadata for projects, tags and receivers, where available. The view is wrapped in a dplyr::tbl to ease use with the dplyr package, but is also available directly from the underlying SQLite connection, where it can appear in sql statements such as select * from bfj.

1	tagview(db, dbMeta = db, mobile = NULL, keep = FALSE)

`db`	dplyr src_sqlite to detections database, or path to .sqlite file. The database must have tables batches, hits, runs.
`dbMeta`	dplyr src to database with "tags", "projects", "species", and "recvDeps" tables. Default: `db`
`mobile`	logical or NULL (the default); determines the source of GPS fixes for tag detections. Possible values are: NULL: use whatever GPS records are available in the recvGPS meta-data table. For tagProject databases, this will be a nominal lat/lon for fixed deployments, and a time series of GPS fixes for mobile deployments. This is usually what you want. FALSE: use only the nominal deployment lat/lon. This runs faster, but will give incorrect lat/lon for mobile deployments. TRUE: if this is a receiver motus database, as opposed to a tagProject database, then use the most recent fix table from the receiver's GPS table, regardless of whether the receiver deployment is considered "mobile". Use this if you are looking at data from a single receiver and you want to treat it as mobile, even if motus.org does not think it was "mobile". For a tagProject database, this values is treated the same as NULL.
`keep`	should temporary tables be saved permanently in the detections database? Default: FALSE. If true, subsequent calls to this function for the same detection database won't need the `dbMeta` database, because all metadata will have been copied to `db`.

a read-only dplyr::tbl of tag detections. This tbl is an SQLite VIEW wrapped in a dplyr::tbl(), and "lives" in the db object.

John Brzustowski jbrzusto@REMOVE_THIS_PART_fastmail.fm

Implementation details:

For both tags and receivers, deployment meta-data has to be looked up by detection ("hit") timestamp; i.e. we need the latest deployment record which is still before the hit timestamp. So we are joining the hit table to the deployment table by a timestamp on the hit and a greatest lower bound for that timestamp in the deployment table. It would be nice if there were an SQL "LOWER JOIN" operator, which instead of joining on exact key value, would join a key on the left to its greatest lower bound on the right. (and similary, an "UPPER JOIN" operator to bind to the least upper bound on the right.) For keys with B-tree indexes, this would be as fast as an exact join.

We can instead code this as a subquery like so:

CREATE TABLE hits (ts double, tagID integer); CREATE TABLE tagDeps (tsStart double, tsEnd double, tagID integer, info char);

SELECT t1.*, t2.info from hits as t1 left join tagDeps as t2 on t2.tagID = t1.tagID and t2.tsStart = (select max(t3.tsStart) from tagDeps as t3 where t3.tagID=t2.tagID and t3.tsStart <= t1.ts and t3.tsEnd >= t1.ts)

This will yield NA for the 'info' field when there is no tag deployment covering the range. Running EXPLAIN on this query in sqlite suggests it optimizes well.

GPS fixes

If this is a receiver database (with detections from a single receiver), it will contain a table called GPS with receiver GPS fixes, and these are used in the view to provide lat/lon/elevation.

Otherwise, lat/lon/elevation come from the recvGPS table. For most receiver deployments, that table will contain only one fix, but for mobile deployments, the table will contain a time series of fixes. The view will use this table to provide lat/lon/elevation.

jbrzusto/motus-R-package documentation built on May 18, 2019, 7:03 p.m.