Blank receiver IDs in .motus data

[zcrysler]

Some detections have NA for receiver serial number, motus receiver ID, and motus receiver deployment ID, eg.

Pam's data has a lot of these - how does receiver serial number not get included in raw detection files? Sometimes there are GPS coordinates to guess which receiver, but often lat/lon is 0

[jbrzusto]

Can you give an example?
It's probably easiest to just specify hitID, as this is an identifier that won't change (although some hits will be "retired" when some batches are rerun).

[zcrysler]

From Pam's data, some example hitID's: 275621962-275621993, 275723034 (no lat/lon), 143761621-14376136

[jbrzusto]

Just confirming this behaviour, at least for recv and site:

> s = tagme(14, update=FALSE)
> t = tbl(s, "alltags")
> eg = t %>% filter(hitID==275621962) %>% select(hitID, ts, runID, batchID, motusTagID, recv, site) %>% collect
> eg
# A tibble: 1 x 7
      hitID         ts    runID batchID motusTagID  recv  site
      <int>      <dbl>    <int>   <int>      <int> <chr> <chr>
1 275621962 1479417632 20072899  100488      20692  <NA>  <NA>

Digging into the underlying tables:

> Q = safeSQL(s)
> b = Q("select * from batches where batchID=%d", eg$batchID)
> b
  batchID motusDeviceID monoBN    tsStart      tsEnd numHits         ts
1  100488           114     54 1479396988 1479941161      42 1505338896
  motusUserID motusProjectID motusJobID
1          NA             NA     101475

which shows a motusDeviceID is associated with the batch this hit comes from. So why doesn't the receiver serial number (recv) show up? Looking at the portion of the alltags view defined in makeAlltagsView.R, recv and site come from this portion of the select statement:

   ...
   t6.serno as recv,
   t6.name as site,
   ...

where t6 is the recvDeps table joined like so:

...
LEFT JOIN
   recvDeps AS t6 ON t6.deviceID = t3.motusDeviceID AND
      t6.tsStart =
         (SELECT
             max(t6b.tsStart)
          FROM
             recvDeps AS t6b
          WHERE
             t6b.deviceID=t3.motusDeviceID
             AND t6b.tsStart <= t1.ts
             AND (t6b.tsEnd IS NULL OR t6b.tsEnd >= t1.ts)
         )
...

i.e. this is the latest record in recvDeps for the given deviceID which starts before the hit (whose timestamp is t1.ts) but is either unterminated or ends after the hit. (The nested query on a joined second copy of recvDeps selects the "closest but not later" deployment start timestamp, and the outer query grabs the corresponding deployment record; sqlite provides a cleaner way of doing this, which I should be using!)

This is meant to pull in the most relevant lat/lon for a given receiver, since it might have been deployed several times at different sites.

Unfortunately, the deployments listed for this receiver:

Q("select * from recvDeps where deviceID=%d", b$motusDeviceID)
  deployID           serno receiverType deviceID macAddress   status    name
1     2352 SG-3214BBBK6435  SENSORGNOME      114       <NA> deployed    MISP
2     2592 SG-3214BBBK6435  SENSORGNOME      114       <NA> deployed Bivalve
  fixtureType latitude longitude isMobile    tsStart      tsEnd projectID
1       Tower  38.9479  -75.3154        0 1430784000 1434672000        47
2        <NA>  39.2344  -75.0147        0 1435795200 1451260800        47
  elevation
1        NA
2        NA

don't overlap the timestamp 1479417632.

So there are two problems:

• metadata problem: receiver deployment records don't seem to cover the full extent of data

• database query problem: receiver serial number should be fetched by deviceID without regard for date, since it is invariant. The remaining fields fetched from recvDeps are features of the deployment itself.

I've fixed the query problem like so:

• create a new table in the user's .motus DB called recvs, with just deviceID and serno columns
• recvs will be updated automatically when metadata are updated using tagme()
• the alltags view will join to recvs to grab serno (and any other deployment-independent receiver information we might choose to add later); this won't depend on receiver deployment metadata

This will provide recv (i.e. serial number), but not the remaining receiver deployment info, which will require changes to metadata.

(this is another demonstration of why we should have the data processing stage automatically generate receiver deployment records)

[zcrysler]

Thanks John! I'll sort out that metadata so that end is also cleaned up

[jbrzusto]

I'll post a new version of motusClient shortly, with a note on how to fix the above in an existing .motus database