Use new detections view / sql function for `get_detections()`

[PietrH]

There has been quite a bit of work in the backend to increase performance on the detections view. This includes running queries via a new sql function and using pagination:

 query <- glue::glue_sql(
    .con = my_con,
    "SELECT * FROM acoustic.ltdd_get_detection_ids(
    {next_id_pk},
    {limit},
    true,
    _projectcode := '2013_albertkanaal',
    _network_project_code:= null,
    _start_date :=  null,
    _end_date:= null, _acoustic_tag_id:= null
    , _scientific_name:= null
    , _receiver_id:= null
   );"
  )

Stijn email thread: detections view functie test

Implementation should happen in this package, and come upstream after [inbo/etn]: v2.3.1 is merged, or as part as via the etnservice dependency.

2 Views

There are two views:

• detections_animal
• detections_network

I understand that based on a condition either one or the other view is required.

Switching views

When there is a query on animal, use acoustic.detections_animal, otherwise use acoustic.detections_network

Progress bar

It's possible to do a COUNT on the query beforehand to see what the number of returned records will be, and use this to provide some sort of progress reporting.

Proposed approach

We are running up to the OpenCPU Serializer limitations, thus client side pagination is the solution. The client fetches the pages one by one.

• COUNT to get total number of records: progress reporting -> Need to know the correct view?
• Fetch pages one by one
• Combine them

[PietrH]

Also found this snippet:

query <- glue::glue_sql(
  .con = my_con,
  "SELECT * FROM acoustic.ltdd_get_detection_ids(
    0,
    10000,
    true,
    _projectcode := '2013_albertkanaal',
    _network_project_code:= null,
    _start_date := '2016-05-12 04:07:44.000',
    _end_date:= null --'2016-05-12 04:18:27.000', _acoustic_tag_id:= null
    , _scientific_name:= 'Salmo salar'
    , _receiver_id:= 1074
   );"
)

DBI::dbGetQuery(my_con, query)

[PietrH]

snippet from Stijn's script: test_detections_r_script.R:

  next_id_pk <- 0
  combined_results <- list()
  page <- 1
  run = TRUE
  while(run==TRUE){
    query <- glue::glue_sql("\n    SELECT\n  det.detection_id_pk FROM acoustic.detections_animal AS det\n    WHERE\n      {start_date_query}\n      AND {end_date_query}\n      AND {acoustic_tag_id_query}\n      AND {animal_project_code_query}\n      AND {scientific_name_query}\n      AND {acoustic_project_code_query}\n      AND {receiver_id_query}\n      AND {station_name_query}\n and det.detection_id_pk > {next_id_pk} limit 1000000  ", 
                                   .con = connection)
    subresult <- DBI::dbGetQuery(connection, query)
    combined_results[[page]] <- subresult
    page <- page + 1
    if(nrow(subresult) == 1000000){
      next_id_pk <- max(subresult$detection_id_pk, na.rm = TRUE)
      print(next_id_pk)
    } else {
      run = FALSE
    }
  }
  result <- do.call(rbind, combined_results)
  dplyr::as_tibble(result)

[PietrH]

acoustic_project_code == network_code

If we have a network_code, use the acoustic.detections_network view, otherwise use the acoustic.detections_animal_view.

The detections network view has much less detections, I'm not a 100% why but I believe this is the logic. I'll check with Stijn via email.

[PietrH]

I was wrong! Stijn:

Vanaf dat er een animal opegeven is of iets specifiek voor een animal, kijken we naar detections_animal.
De query voor animals is efficiënter, want kijkt enkel naar detecties die gelinkt werden aan een dier (meer inner joins).

[PietrH]

Are there other columns for which I should switch to acoustic.detections_animal ?

acoustic_tag_id?

[PietrH]

The acoustic.detections_animal view has one column that the network view has not: group_tag

So as long as I don't map that field, I can use the query object to map my database fields to the fields the client package expects.

[PietrH]

Are there other columns for which I should switch to acoustic.detections_animal ?

acoustic_tag_id?

No, but we should for scientific_name

Ook voor scientific_name zou ik direct detections_animal nemen.

Dat is een filter op det.transmitter dus die kan best op netwerk als er niets anders gegeven is.
Nu de where clause met moratorium restricties gaat daar dan nog veel in filteren.