makeAlltagsView: create a virtual table of tag detections linked with all...

In jbrzusto/motusClient: fetch data from the motus data server

Description

Creates a virtual table (really a 'view') in a motus database that links each tag detection to all metadata available for the tag and receiver.

Usage

makeAlltagsView(db, name = "alltags")

Arguments

db	dplyr src_sqlite to detections database
name	character scalar; name for the virtual table. Default: 'alltags'.

Details

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.

Value

a dplyr::tbl which refers to the newly-created virtual table. By default, the columns in the virtual table are:

• hitID (double) unique motus ID for this tag detection

• runID (double) unique motus ID for the run this detection belongs to

• batchID unique motus ID for the processing batch this detection came from

• ts timestamp, in seconds since 1 Jan, 1970 GMT

• sig signal strength, in dB (max) for SG; raw value for Lotek receiver

• sigSD sd among pulses of signal strength (SG); NA for Lotek

• noise noise strength, in dB (max) for SG; NA for Lotek

• freq offset in kHz from listening frequency for SG; NA for Lotek

• freqSD sd among pulses of offset in kHz from listening frequency for SG; NA for Lotek

• slop total absolute difference (milliseconds) in intrer-pulse gaps between registration and detection for SG; NA for Lotek

• burstSlop signed difference (seconds) between detection and registration burst intervals

• done logical: is run finished?

• motusTagID unique motus ID for this physical tag

• ant antenna number

• runLen length of run (# of bursts detected)

• bootnum boot session of receiver for SG; NA for Lotek

• tagProjID unique motus ID for project tag was deployed by

• id manufacturer ID

• tagType

• codeSet for coded ID tags, the name of the codeset

• mfg tag manufacturer

• tagModel manufacturer's model name for tag

• tagLifespan estimated tag lifespan

• nomFreq nominal tag frequency (MHz)

• tagBI tag burst interval (seconds)

• pulseLen tag pulse length (milliseconds) if applicable

• speciesID unique motusID for species tag was deployed on

• markerNumber number for additional marker placed on organism (e.g. bird band #)

• markerType type of additional marker

• depLat latitude of tag deployment, in decimal degrees N

• depLon longitude of tag deployment, in decimal degrees E

• depAlt altitude of tag deployment, in metres ASL

• comments additional comments or unclassified metadata for tag (often in JSON format)

• startCode integer code giving method for determining tag deployment start timestamp

• endCode integer code giving method for determining tag deployment end timestamp

• fullID full tag ID as PROJECT#MFGID:BI@NOMFREQ (but this is not necessarily unique over time; see motusTagID for a unique tag id)

• recv serial number of receiver; e.g. SG-1234BBBK5678 or Lotek-12345

• site short name for receiver deployment location

• isMobile logical; was this a mobile receiver deployment?

• projectID integer; unique motus ID for the project that deployed this receiver

• antType character; antenna type; e.g. "omni", "yagi-5", ...

• antBearing numeric; direction antenna main axis points in; degrees clockwise from local magnetic north

• antHeight numeric; height (metres) of antenna main axis above ground

• cableLen numeric; length (metres) of coaxial cable connecting antenna to radio

• cableType character; type of coaxial cable

• mountDistance numeric; distance (metres) between antenna mounting and receiver

• mountBearing numeric; bearing from receiver to base of antenna mounting, in degrees clockwise from local magnetic north

• polarization1 numeric; antenna polarization angle: azimuth component (degrees clockwise from local magnetic north)

• polarization2 numeric; antenna polarization angle: elevation component (degrees above horizon)

• spEN species name in english

• spFR species name in french

• spSci species scientific name

• spGroup species group

• tagProj short label of project that deployed tag

• proj short label of project that deployed receiver

• lat latitude of receiver at tag detection time (degrees North)

• lon longitude of receiver at tag detection time (degrees East)

• alt altitude of receiver at tag detection time (metres)

Note

The new virtual table replaces any previous virtual table by the same name in db. The virtual table is an SQL VIEW, which will persist in db across R sessions.

Author(s)

John Brzustowski jbrzusto@REMOVE_THIS_PART_fastmail.fm

jbrzusto/motusClient documentation built on May 30, 2019, 4:33 p.m.