Fields are empty in new detections view: `acoustic.detections_network`

[PietrH]

This test fails in ETN:

test_that("get_acoustic_detections() does not return duplicate detections when tags are reused", {
  # A69-1601-29925 (tag_serial_number = 1145373) is associated with two animals:
  # - 393 (2012_leopoldkanaal) from 2012-08-21 14:27:00 to 2012-12-10
  # - 394 (2012_leopoldkanaal) from 2012-12-14 13:30:00 to open
  # Detections should be joined with acoustic_tag_id AND datetime, so that they
  # are not duplicated. Note: for df_393 we use a start_date to limit records.
  df_both <- get_acoustic_detections(acoustic_tag_id = "A69-1601-29925")
  df_393 <- get_acoustic_detections(acoustic_tag_id = "A69-1601-29925",
                                    start_date = "2012-12-01",
                                    end_date = "2012-12-10")
  df_394 <- get_acoustic_detections(acoustic_tag_id = "A69-1601-29925",
                                    start_date = "2012-12-14")

  # Expect no duplicates
  expect_identical(nrow(df_both),
                   nrow(df_both %>% dplyr::distinct(detection_id)))

  # Return correct animal within range
  expect_identical(df_393 %>% dplyr::distinct(animal_id) %>% dplyr::pull(), 393L)
  expect_identical(df_394 %>% dplyr::distinct(animal_id) %>% dplyr::pull(), 394L)
})

Because 4 fields, including animal_id, are completely empty when using the new view:

> dplyr::glimpse(old)
Rows: 3,261
Columns: 21
$ detection_id          <int> 44376800, 44376819, 44376849, 44376860, 44376861, 44376863, 44376868, 4…
$ date_time             <dttm> 2012-12-01 00:15:18, 2012-12-01 00:51:49, 2012-12-01 01:40:02, 2012-12…
$ tag_serial_number     <chr> "1145373", "1145373", "1145373", "1145373", "1145373", "1145373", "1145…
$ acoustic_tag_id       <chr> "A69-1601-29925", "A69-1601-29925", "A69-1601-29925", "A69-1601-29925",…
$ animal_project_code   <chr> "2012_leopoldkanaal", "2012_leopoldkanaal", "2012_leopoldkanaal", "2012…
$ animal_id             <int> 393, 393, 393, 393, 393, 393, 393, 393, 393, 393, 393, 393, 393, 393, 3…
$ scientific_name       <chr> "Anguilla anguilla", "Anguilla anguilla", "Anguilla anguilla", "Anguill…
$ acoustic_project_code <chr> "leopold", "leopold", "leopold", "leopold", "leopold", "leopold", "leop…
$ receiver_id           <chr> "VR2W-115445", "VR2W-115445", "VR2W-115445", "VR2W-115445", "VR2W-11544…
$ station_name          <chr> "bh-36", "bh-36", "bh-36", "bh-36", "bh-36", "bh-36", "bh-36", "bh-36",…
$ deploy_latitude       <dbl> 51.33464, 51.33464, 51.33464, 51.33464, 51.33464, 51.33464, 51.33464, 5…
$ deploy_longitude      <dbl> 3.76826, 3.76826, 3.76826, 3.76826, 3.76826, 3.76826, 3.76826, 3.76826,…
$ depth_in_meters       <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ sensor_value          <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ sensor_unit           <chr> "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",…
$ sensor2_value         <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ sensor2_unit          <chr> "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",…
$ signal_to_noise_ratio <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ source_file           <chr> "VR2W_115445_20140610_1.csv", "VR2W_115445_20140610_1.csv", "VR2W_11544…
$ qc_flag               <lgl> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, F…
$ deployment_id         <int> 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811,…
> dplyr::glimpse(new)
Rows: 3,261
Columns: 20
$ detection_id          <int> 44376800, 44376819, 44376849, 44376860, 44376861, 44376863, 44376868, 4…
$ date_time             <dttm> 2012-12-01 00:15:18, 2012-12-01 00:51:49, 2012-12-01 01:40:02, 2012-12…
$ tag_serial_number     <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ acoustic_tag_id       <chr> "A69-1601-29925", "A69-1601-29925", "A69-1601-29925", "A69-1601-29925",…
$ animal_project_code   <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ animal_id             <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ scientific_name       <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ acoustic_project_code <chr> "leopold", "leopold", "leopold", "leopold", "leopold", "leopold", "leop…
$ receiver_id           <chr> "VR2W-115445", "VR2W-115445", "VR2W-115445", "VR2W-115445", "VR2W-11544…
$ station_name          <chr> "bh-36", "bh-36", "bh-36", "bh-36", "bh-36", "bh-36", "bh-36", "bh-36",…
$ deploy_latitude       <dbl> 51.33464, 51.33464, 51.33464, 51.33464, 51.33464, 51.33464, 51.33464, 5…
$ deploy_longitude      <dbl> 3.76826, 3.76826, 3.76826, 3.76826, 3.76826, 3.76826, 3.76826, 3.76826,…
$ sensor_value          <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ sensor_unit           <chr> "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",…
$ sensor2_value         <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ sensor2_unit          <chr> "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",…
$ signal_to_noise_ratio <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ source_file           <chr> "VR2W_115445_20140610_1.csv", "VR2W_115445_20140610_1.csv", "VR2W_11544…
$ qc_flag               <lgl> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, F…
$ deployment_id         <int> 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811, 2811,…

• tag_serial_number
• animal_project_code
• animal_id
• scientific_name

[PietrH]

SELECT * FROM acoustic.detections_network AS det
WHERE
  det.datetime >= '2012-12-01'
  AND det.datetime < '2012-12-10'
  AND 'True'
  AND det.transmitter IN ('A69-1601-29925')
  AND 'True'
  AND 'True'
  AND 'True'
  AND 'True'
  AND 'True'
  AND det.detection_id_pk > 0

[PietrH]

vroeger werden er tag devices gelinked waar helemaal geen animal aan hing.

voorbeeld van detectie die nu minder velden heeft: 44376800, nu hangt er geen animal release meer aan vast.

er is een tabel multiple tag device detections, waar ik niet aan kan, als die daarin zit

network geeft al de detecties die in dat network zijn gedetecteerd, met of zonder animal, de andere enkel die met animal.

deze detectie zit ook niet in multiple detection

er wordt mogelijks nu een value gebruikt: end_date_tag die voor de release date staat voor dit dier 393, mogelijks loopt er daar iets mis, vroeger werd dit veld niet gebruikt. Data issue in de databank?

deze animal 393 wordt niet aan detecties gelinkt door die end_date_tag

394, andere receiver, mogelijke verklaring: animal is recht na release opgepikt door 2 receivers. 2 opties: receivers stond op dek aan, en ze hebben die vis gevangen met de 2 receivers op dek, en dan de vis eerst op water, en dan de receivers pas in het water. Dat is verklaring van detecties voor de vis in het water zat.

De detectie koppeling is stricter als in het verleden gebeurde, waardoor er inderdaad mogelijks in het nieuwe systeem fouten in geslopen zijn. Die gaan wijzigen op data issues, ernstige data cleanup nodig