List queries, as views and what fields they should contain for Parquet building

[PietrH]

• provide a list of views and fields they should contain to Stijn. To be used for R package, parquet, etc

The parquet files and the package queries (etnservice) are built form different queries, we should provide the mechanics of the views in the R package to Stijn so we can build views that can replace the more complicated queries placed in etnservice (focus on get_ functions).

---

• Missing query parameters are replaced with TRUE, so AND det.datetime < {end_date} will become AND True if no end_dateis provided.
• Sorting is mostly done on the R side, because in previous testing this has proved to be much much faster.
• Some functions read a .sql file and use it in their queries, sometimes in joins. The queries in these sql files are sometimes reused.
• Sometimes significant postprocessing takes place on the client side, for example in get_animals() where rectangling takes place (one row = one animal)

get_acoustic_deployments

query

  SELECT
      dep.id_pk AS deployment_id,
      receiver.receiver AS receiver_id,
      network_project.projectcode AS acoustic_project_code,
      dep.station_name AS station_name,
      location_name AS station_description,
      location_manager AS station_manager,
      dep.deploy_date_time AS deploy_date_time,
      dep.deploy_lat AS deploy_latitude,
      dep.deploy_long AS deploy_longitude,
      dep.intended_lat AS intended_latitude,
      dep.intended_long AS intended_longitude,
      dep.mooring_type AS mooring_type,
      dep.bottom_depth AS bottom_depth,
      dep.riser_length AS riser_length,
      dep.instrument_depth AS deploy_depth,
      dep.battery_install_date AS battery_installation_date,
      dep.drop_dead_date AS battery_estimated_end_date,
      dep.activation_datetime AS activation_date_time,
      dep.recover_date_time AS recover_date_time,
      dep.recover_lat AS recover_latitude,
      dep.recover_long AS recover_longitude,
      dep.download_date_time AS download_date_time,
      dep.data_downloaded AS download_file_name,
      dep.valid_data_until_datetime AS valid_data_until_date_time,
      dep.sync_date_time AS sync_date_time,
      dep.time_drift AS time_drift,
      dep.ar_battery_install_date AS ar_battery_installation_date,
      dep.ar_confirm AS ar_confirm,
      dep.transmit_profile AS transmit_profile,
      dep.transmit_power_output AS transmit_power_output,
      dep.log_temperature_stats_period AS log_temperature_stats_period,
      dep.log_temperature_sample_period AS log_temperature_sample_period,
      dep.log_tilt_sample_period AS log_tilt_sample_period,
      dep.log_noise_stats_period AS log_noise_stats_period,
      dep.log_noise_sample_period AS log_noise_sample_period,
      dep.log_depth_stats_period AS log_depth_stats_period,
      dep.log_depth_sample_period AS log_depth_sample_period,
      dep.comments AS comments
      -- dep.project: dep.project_fk instead
      -- dep.check_complete_time
      -- dep.voltage_at_deploy
      -- dep.voltage_at_download
      -- dep.location_description
      -- dep.date_created
      -- dep.date_modified
      -- dep.distance_to_mouth
      -- dep.source
      -- dep.acousticreleasenumber: cpod
      -- dep.hydrophonecablelength: cpod
      -- dep.recordingname: cpod
      -- dep.hydrophonesensitivity: cpod
      -- dep.amplifiersensitivity: cpod
      -- dep.sample_rate: cpod
      -- dep.external_id
    FROM
      acoustic.deployments AS dep
      LEFT JOIN acoustic.receivers AS receiver
        ON dep.receiver_fk = receiver.id_pk
      LEFT JOIN common.projects AS network_project
        ON dep.project_fk = network_project.id
    WHERE
      dep.deployment_type = 'acoustic_telemetry'
      AND dep.id_pk IN ({deployment_id*})
      AND receiver.receiver IN ({receiver_id*})
      AND LOWER(network_project.projectcode) IN ({acoustic_project_code*})
      AND dep.station_name IN ({station_name*})

postprocessing

# Filter on open deployments
  if (open_only) {
    deployments <- filter(deployments, is.na(.data$recover_date_time))
  }

  # Sort data
  deployments <-
    deployments |>
    dplyr::arrange(
      .data$acoustic_project_code,
      factor(.data$station_name, levels = list_station_names(credentials)),
      .data$deploy_date_time
    )

get_acoustic_detections

A selection is made between two views based on what fields are queried.

if (!is.null(animal_project_code) || !is.null(scientific_name)) {
    # If either animal_project_code or scientific_name are provided, use the
    # animal view
    view_to_query <- "acoustic.detections_animal"
  } else {
    view_to_query <- "acoustic.detections_network"
  }

count_acoustic_detections

This is used to adjust the page size, to create an early return if no detentions are found, and finally to display a progress bar.

nb: This is actually the same query from get_acoustic_detections, and uses the same endpoint. Just slightly changing the sql query depending on what function is called.

SELECT COUNT(*)
FROM {view_to_query} AS det
WHERE
det.datetime >= {start_date}
      AND det.datetime < {end_date}
      AND det.tag_serial_number IN ({tag_serial_number*})
      AND det.transmitter IN ({acoustic_tag_id*})
      AND LOWER(animal_project_code) IN ({animal_project_code*})
      AND animal_scientific_name IN ({scientific_name*})
      AND LOWER(network_project_code) IN ({acoustic_project_code*})
      AND det.deployment_fk IN ({deployment_id*})
      AND det.receiver IN ({receiver_id*})
      AND deployment_station_name IN ({station_name*})
      AND det.detection_id_pk > {next_id_pk}
    LIMIT ALL

query: paginated

SELECT *
FROM {view_to_query} AS det
WHERE
det.datetime >= {start_date}
      AND det.datetime < {end_date}
      AND det.tag_serial_number IN ({tag_serial_number*})
      AND det.transmitter IN ({acoustic_tag_id*})
      AND LOWER(animal_project_code) IN ({animal_project_code*})
      AND animal_scientific_name IN ({scientific_name*})
      AND LOWER(network_project_code) IN ({acoustic_project_code*})
      AND det.deployment_fk IN ({deployment_id*})
      AND det.receiver IN ({receiver_id*})
      AND deployment_station_name IN ({station_name*})
      AND det.detection_id_pk > {next_id_pk}
    LIMIT {page_size}

postprocessing

# Combine pages and sort on acoustic_tag_id
  detections <-
    arrow::open_dataset(tmp_pagedir, format = "feather") |>
    # perform a natural sort via Arrow C++ mapping
    dplyr::mutate(
      text_part = stringr::str_remove_all(.data$acoustic_tag_id, "[0-9]"),
      num_part = as.numeric(
        stringr::str_remove_all(.data$acoustic_tag_id, "[^0-9]")
      )
    ) |>
    # Arrange by the text part, then the numeric part, then deployment_id to
    # ensure the same result regardless of protocol
    dplyr::arrange(.data$text_part, .data$num_part, .data$deployment_id) |>
    dplyr::select(-dplyr::all_of(c("text_part", "num_part"))) |>
    # Set the column classes explicitly
    dplyr::mutate(
      qc_flag = as.logical(.data$qc_flag)
    )

  # Return single detections table
  dplyr::collect(detections)

get_acoustic_projects

query

project.sql

https://github.com/inbo/etnservice/blob/31d1a8e24b8567c617172a5b2086231295141082/inst/sql/project.sql

/* Projects with controlled type */
SELECT
  project.id AS project_id,
  project.projectcode AS project_code,
  CASE
    WHEN project.type = 'animal' THEN 'animal'
    WHEN project.type = 'network' AND project.context_type = 'acoustic_telemetry' THEN 'acoustic'
    WHEN project.type = 'network' AND project.context_type = 'cpod' THEN 'cpod'
  END AS project_type,
  project.telemtry_type AS telemetry_type,
  project.name AS project_name,
  -- ADD coordinating_organization
  -- ADD principal_investigator
  -- ADD principal_investigator_email
  project.startdate AS start_date,
  project.enddate AS end_date,
  project.latitude AS latitude,
  project.longitude AS longitude,
  project.moratorium AS moratorium,
  project.imis_dataset_id AS imis_dataset_id
  -- project.mrgid
  -- project.mda_folder_id
FROM
  common.projects AS project

query

SELECT
      project.*
    FROM
      ({project_sql}) AS project
    WHERE
      project_type = 'acoustic'
      AND LOWER(project.project_code) IN ({acoustic_project_code*})

postprocessing

 # Sort data
  projects <-
    projects |>
    dplyr::arrange(.data$project_code)

# Set the column classes explicitly
dplyr::mutate(moratorium = as.logical(as.integer(.data$moratorium)))

  # Optionally add citation information from IMIS/MarineInfo
  if(citation){
    imis_dataset_ids <- unique(dplyr::pull(out, "imis_dataset_id"))
    citation_df <- cite_imis_dataset(imis_dataset_ids)

    out <- dplyr::full_join(out,
                            citation_df,
                            by = dplyr::join_by("imis_dataset_id"))
  }

get_acoustic_receivers

receiver.sql

https://github.com/inbo/etnservice/blob/31d1a8e24b8567c617172a5b2086231295141082/inst/sql/receiver.sql

/* Receivers with controlled status */
SELECT
  *,
  CASE
    WHEN status = 'Active' THEN 'active'
    WHEN status = 'Available' OR status = 'available' THEN 'available'
    WHEN status = 'Broken' THEN 'broken'
    WHEN status = 'Inactive' THEN 'inactive'
    WHEN status = 'Lost' THEN 'lost'
    WHEN status = 'Returned to manufacturer' THEN 'returned'
  END AS controlled_status
FROM
  acoustic.receivers_limited

acoustic_tag_id.sql

https://github.com/inbo/etnservice/blob/31d1a8e24b8567c617172a5b2086231295141082/inst/sql/acoustic_tag_id.sql

/* Unified acoustic_tag_id and acoustic_tag_id_alternative */
  SELECT
    tag_device_fk,
    tag_full_id AS acoustic_tag_id
  FROM acoustic.tags
  WHERE tag_full_id IS NOT NULL
UNION
  SELECT
    tag_device_fk,
    thelma_converted_code AS acoustic_tag_id
  FROM acoustic.tags
  WHERE thelma_converted_code IS NOT NULL
UNION
  SELECT
    device_tag_fk AS tag_device_fk,
    sensor_full_id AS acoustic_tag_id
  FROM archive.sensor
  WHERE sensor_full_id IS NOT NULL

query

SELECT
      receiver.receiver AS receiver_id,
      manufacturer.project AS manufacturer,
      receiver.model_number AS receiver_model,
      receiver.serial_number AS receiver_serial_number,
      receiver.modem_address AS modem_address,
      receiver.controlled_status AS status,
      receiver.expected_battery_life AS battery_estimated_life,
      CASE
        WHEN receiver.owner_group_fk_limited IS NOT NULL THEN owner_organization.name
        ELSE NULL
      END AS owner_organization,
      financing_project.projectcode AS financing_project,
      acoustic_tag_id.acoustic_tag_id AS built_in_acoustic_tag_id,
      receiver.ar_model_number AS ar_model,
      receiver.ar_serial_number AS ar_serial_number,
      receiver.ar_expected_battery_life AS ar_battery_estimated_life,
      receiver.ar_voltage_at_deploy AS ar_voltage_at_deploy,
      receiver.ar_interrogate_code AS ar_interrogate_code,
      receiver.ar_receive_frequency AS ar_receive_frequency,
      receiver.ar_reply_frequency AS ar_reply_frequency,
      receiver.ar_ping_rate AS ar_ping_rate,
      receiver.ar_enable_code_address AS ar_enable_code_address,
      receiver.ar_release_code AS ar_release_code,
      receiver.ar_disable_code AS ar_disable_code,
      receiver.ar_tilt_code AS ar_tilt_code,
      receiver.ar_tilt_after_deploy AS ar_tilt_after_deploy
      -- receiver.id_pk
      -- receiver.external_id
    FROM
      ({receiver_sql}) AS receiver
      LEFT JOIN common.manufacturer AS manufacturer
          ON receiver.manufacturer_fk = manufacturer.id_pk
      LEFT JOIN common.etn_group AS owner_organization
        ON receiver.owner_group_fk_limited = owner_organization.id_pk
      LEFT JOIN common.projects AS financing_project
        ON receiver.financing_project_fk = financing_project.id
      LEFT JOIN ({acoustic_tag_id_sql}) AS acoustic_tag_id
        ON receiver.built_in_tag_device_fk = acoustic_tag_id.tag_device_fk
    WHERE
      receiver.receiver_type = 'acoustic_telemetry'
      AND receiver.receiver IN ({receiver_id*})
      AND receiver.controlled_status IN ({status*})

postprocessing

# Sort data
  receivers <-
    receivers |>
    dplyr::arrange(.data$receiver_id)

get_animal_projects

project.sql

https://github.com/inbo/etnservice/blob/31d1a8e24b8567c617172a5b2086231295141082/inst/sql/project.sql

/* Projects with controlled type */
SELECT
  project.id AS project_id,
  project.projectcode AS project_code,
  CASE
    WHEN project.type = 'animal' THEN 'animal'
    WHEN project.type = 'network' AND project.context_type = 'acoustic_telemetry' THEN 'acoustic'
    WHEN project.type = 'network' AND project.context_type = 'cpod' THEN 'cpod'
  END AS project_type,
  project.telemtry_type AS telemetry_type,
  project.name AS project_name,
  -- ADD coordinating_organization
  -- ADD principal_investigator
  -- ADD principal_investigator_email
  project.startdate AS start_date,
  project.enddate AS end_date,
  project.latitude AS latitude,
  project.longitude AS longitude,
  project.moratorium AS moratorium,
  project.imis_dataset_id AS imis_dataset_id
  -- project.mrgid
  -- project.mda_folder_id
FROM
  common.projects AS project

query

SELECT
      project.*
    FROM
      ({project_sql}) AS project
    WHERE
      project_type = 'animal'
      AND LOWER(project.project_code) IN ({animal_project_code*})

postprocessing

# Sort data
  projects <-
    projects |>
    dplyr::arrange(.data$project_code)
``

```r
 # Set the column classes explicitly
    dplyr::mutate(moratorium = as.logical(as.integer(.data$moratorium)))

  # Optionally add citation information from IMIS/MarineInfo
  if(citation){
    imis_dataset_ids <- unique(dplyr::pull(out, "imis_dataset_id"))
    citation_df <- cite_imis_dataset(imis_dataset_ids)

    out <- dplyr::full_join(out,
                            citation_df,
                            by = dplyr::join_by("imis_dataset_id"))
  }

get_animals

tag.sql

https://github.com/inbo/etnservice/blob/31d1a8e24b8567c617172a5b2086231295141082/inst/sql/tag.sql

/* Unified tags with controlled tag_type, tag_subtype */
SELECT
  tag_device.serial_number AS tag_serial_number,
  CASE
    WHEN tag_type.name = 'id-tag' THEN 'acoustic'
    WHEN tag_type.name = 'sensor-tag' AND acoustic_tag_id IS NOT NULL THEN 'acoustic-archival'
    WHEN tag_type.name = 'sensor-tag' THEN 'archival'
  END AS tag_type,
  CASE
    WHEN tag_subtype.name = 'animal' THEN 'animal'
    WHEN tag_subtype.name = 'built-in tag' THEN 'built-in'
    WHEN tag_subtype.name = 'range tag' THEN 'range'
    WHEN tag_subtype.name = 'sentinel tag' THEN 'sentinel'
  END AS tag_subtype,
  tag_union.*
FROM
  common.tag_device_limited AS tag_device
    LEFT JOIN common.tag_device_type AS tag_type
      ON tag_device.tag_device_type_fk = tag_type.id_pk
    LEFT JOIN acoustic.acoustic_tag_subtype AS tag_subtype
      ON tag_device.acoustic_tag_subtype_fk = tag_subtype.id_pk
    LEFT JOIN (
      SELECT
        'acoustic:' ||  acoustic_tag.id_pk AS tag_id,
        tag_device_fk,
        sensor_type,
        tag_full_id AS acoustic_tag_id,
        thelma_converted_code,
        frequency,
        NULL AS resolution, NULL AS unit, NULL AS accurency, NULL AS range_min, NULL AS range_max,
        slope, intercept, range, sensor_transmit_ratio, accelerometer_algoritm, accelerometer_samples_per_second,
        min_delay, max_delay, power, duration_step1, acceleration_on_sec_step1,
        min_delay_step2, max_delay_step2, power_step2, duration_step2, acceleration_on_sec_step2,
        min_delay_step3, max_delay_step3, power_step3, duration_step3, acceleration_on_sec_step3,
        min_delay_step4, max_delay_step4, power_step4, duration_step4, acceleration_on_sec_step4
        -- serial_number_tbd, type_tbd, model_tbd, owner_pi_tbd, activation_date_tbd,
        -- end_date_tbd, estimated_lifetime_tbd, acoustic_tag_type_tbd, manufacturer_fk_tbd,
        -- owner_group_fk_tbd, financing_project_fk_tbd, status_tbd
        -- id_code, tag_code_space AS protocol, id_pk, file, units, external_id
      FROM
        acoustic.tags AS acoustic_tag
      UNION
      SELECT
        'archive:' || archival_tag.id_pk AS tag_id,
        device_tag_fk AS tag_device_fk,
        sensor_type.description AS sensor_type,
        sensor_full_id AS acoustic_tag_id,
        NULL AS thelma_converted_code,
        frequency,
        resolution, unit, accurency, range_min, range_max,
        slope, intercept, range, sensor_transmit_ratio, accelerometer_algoritm, accelerometer_samples_per_second,
        min_delay, max_delay, power, duration_step1, acceleration_on_sec_step1,
        min_delay_step2, max_delay_step2, power_step2, duration_step2, acceleration_on_sec_step2,
        min_delay_step3, max_delay_step3, power_step3, duration_step3, acceleration_on_sec_step3,
        min_delay_step4, max_delay_step4, power_step4, duration_step4, acceleration_on_sec_step4
        -- id_pk, id_code protocol
      FROM
        archive.sensor AS archival_tag
        LEFT JOIN archive.sensor_type AS sensor_type
          ON archival_tag.sensor_type_fk = sensor_type.id_pk
    ) AS tag_union
      ON tag_device.id_pk = tag_union.tag_device_fk

query

SELECT
      animal.id_pk AS animal_id,
      animal_project.projectcode AS animal_project_code,
      tag.tag_serial_number AS tag_serial_number,
      tag.tag_type AS tag_type,
      tag.tag_subtype AS tag_subtype,
      tag.acoustic_tag_id AS acoustic_tag_id,
      tag.thelma_converted_code AS acoustic_tag_id_alternative,
      animal.scientific_name AS scientific_name,
      animal.common_name AS common_name,
      animal.aphia_id AS aphia_id,
      animal.animal_id AS animal_label,
      animal.animal_nickname AS animal_nickname,
      animal.tagger AS tagger,
      animal.catched_date_time AS capture_date_time,
      animal.capture_location AS capture_location,
      animal.capture_latitude AS capture_latitude,
      animal.capture_longitude AS capture_longitude,
      animal.capture_method AS capture_method,
      animal.capture_depth AS capture_depth,
      animal.temperature_change AS capture_temperature_change,
      animal.utc_release_date_time AS release_date_time,
      animal.release_location AS release_location,
      animal.release_latitude AS release_latitude,
      animal.release_longitude AS release_longitude,
      animal.recapture_date AS recapture_date_time,
      animal.length_type AS length1_type,
      animal.length AS length1,
      animal.length_units AS length1_unit,
      animal.length2_type AS length2_type,
      animal.length2 AS length2,
      animal.length2_units AS length2_unit,
      animal.length3_type AS length3_type,
      animal.length3 AS length3,
      animal.length3_units AS length3_unit,
      animal.length4_type AS length4_type,
      animal.length4 AS length4,
      animal.length4_units AS length4_unit,
      animal.weight AS weight,
      animal.weight_units AS weight_unit,
      animal.age AS age,
      animal.age_units AS age_unit,
      animal.sex AS sex,
      animal.life_stage AS life_stage,
      animal.wild_or_hatchery AS wild_or_hatchery,
      animal.stock AS stock,
      animal.date_of_surgery AS surgery_date_time,
      animal.surgery_location AS surgery_location,
      animal.surgery_latitude AS surgery_latitude,
      animal.surgery_longitude AS surgery_longitude,
      animal.treatment_type AS treatment_type,
      animal.implant_type AS tagging_type,
      animal.implant_method AS tagging_methodology,
      animal.dna_sample_taken AS dna_sample,
      animal.sedative AS sedative,
      animal.sedative_concentration AS sedative_concentration,
      animal.anaesthetic AS anaesthetic,
      animal.buffer AS buffer,
      animal.anaesthetic_concentration AS anaesthetic_concentration,
      animal.buffer_concentration_in_anaesthetic AS buffer_concentration_in_anaesthetic,
      animal.anesthetic_concentration_in_recirculation AS anaesthetic_concentration_in_recirculation,
      animal.buffer_concentration_in_recirculation AS buffer_concentration_in_recirculation,
      animal.dissolved_oxygen AS dissolved_oxygen,
      animal.preop_holding_period AS pre_surgery_holding_period,
      animal.post_op_holding_period AS post_surgery_holding_period,
      animal.holding_temperature AS holding_temperature,
      animal.comments AS comments
      -- animal.project: animal.project_fk instead
      -- animal.person_id
      -- animal.est_tag_life
      -- animal.date_modified
      -- animal.date_created
      -- animal.end_date_tag
      -- animal.post_op_holding_period_new
      -- animal.external_id
    FROM common.animal_release_limited AS animal
      LEFT JOIN common.animal_release_tag_device AS animal_with_tag
        ON animal.id_pk = animal_with_tag.animal_release_fk
        LEFT JOIN ({tag_sql}) AS tag
          ON animal_with_tag.tag_device_fk = tag.tag_device_fk
      LEFT JOIN common.projects AS animal_project
        ON animal.project_fk = animal_project.id
    WHERE
      animal.id_pk IN ({animal_id*})
      AND LOWER(animal_project.projectcode) IN ({animal_project_code*})
      AND tag.tag_serial_number IN ({tag_serial_number*})
      AND animal.scientific_name IN ({scientific_name*})

postprocessing

# Collapse tag information, to obtain one row = one animal
  tag_cols <-
    animals |>
    dplyr::select(dplyr::starts_with("tag"), dplyr::starts_with("acoustic_tag_id")) |>
    names()
  other_cols <-
    animals |>
    dplyr::select(-dplyr::starts_with("tag"), -dplyr::starts_with("acoustic_tag_id")) |>
    names()
  animals <-
    animals |>
    dplyr::group_by_at(other_cols) |>
    dplyr::summarize_at(tag_cols, paste, collapse = ",") |> # Collapse multiple tags by comma
    dplyr::ungroup() |>
    dplyr::mutate_at(tag_cols, gsub, pattern = "NA", replacement = "") |> # Use "" instead of "NA"
    dplyr::select(names(animals)) # Use the original column order

  # Sort data
  animals <-
    animals |>
    dplyr::arrange(
      .data$animal_project_code,
      .data$release_date_time,
      factor(.data$tag_serial_number, levels = list_tag_serial_numbers(credentials))
    )

get_cpod_projects

project.sql

https://github.com/inbo/etnservice/blob/31d1a8e24b8567c617172a5b2086231295141082/inst/sql/project.sql

/* Projects with controlled type */
SELECT
  project.id AS project_id,
  project.projectcode AS project_code,
  CASE
    WHEN project.type = 'animal' THEN 'animal'
    WHEN project.type = 'network' AND project.context_type = 'acoustic_telemetry' THEN 'acoustic'
    WHEN project.type = 'network' AND project.context_type = 'cpod' THEN 'cpod'
  END AS project_type,
  project.telemtry_type AS telemetry_type,
  project.name AS project_name,
  -- ADD coordinating_organization
  -- ADD principal_investigator
  -- ADD principal_investigator_email
  project.startdate AS start_date,
  project.enddate AS end_date,
  project.latitude AS latitude,
  project.longitude AS longitude,
  project.moratorium AS moratorium,
  project.imis_dataset_id AS imis_dataset_id
  -- project.mrgid
  -- project.mda_folder_id
FROM
  common.projects AS project
``
### query
```sql
 SELECT
      project.*
    FROM
      ({project_sql}) AS project
    WHERE
      project_type = 'cpod'
      AND LOWER(project.project_code) IN ({cpod_project_code*})

postprocessing

  # Set the column classes explicitly
    dplyr::mutate(moratorium = as.logical(as.integer(.data$moratorium)))

  # Optionally add citation information from IMIS/MarineInfo
  if(citation){
    imis_dataset_ids <- unique(dplyr::pull(out, "imis_dataset_id"))
    citation_df <- cite_imis_dataset(imis_dataset_ids)

    out <- dplyr::full_join(out,
                            citation_df,
                            by = dplyr::join_by("imis_dataset_id"))
  }

get_package

This function does not have it's own api endpoint, and does not need a corresponding parquet file.

It consists of a number of calls to list_ and other get_ functions.

etn/R/get_package.R

Line 57 in e57debd

list_animal_project_codes(),

etn/R/get_package.R

Line 69 in e57debd

animals <- get_animals(animal_project_code = animal_project_code)

etn/R/get_package.R

Line 85 in e57debd

tags <- get_tags(tag_serial_number = tag_serial_numbers)

etn/R/get_package.R

Line 90 in e57debd

detections <- get_acoustic_detections(

etn/R/get_package.R

Line 109 in e57debd

deployments <- get_acoustic_deployments(

etn/R/get_package.R

Line 134 in e57debd

receivers <- get_acoustic_receivers(

etn/R/get_package.R

Line 145 in e57debd

get_animal_projects(

etn/R/get_package.R

Line 151 in e57debd

get_acoustic_projects(

etn/R/get_package.R

Line 165 in e57debd

project_info <- get_animal_projects(

get_tags

tag.sql

https://github.com/inbo/etnservice/blob/31d1a8e24b8567c617172a5b2086231295141082/inst/sql/tag.sql

/* Unified tags with controlled tag_type, tag_subtype */
SELECT
  tag_device.serial_number AS tag_serial_number,
  CASE
    WHEN tag_type.name = 'id-tag' THEN 'acoustic'
    WHEN tag_type.name = 'sensor-tag' AND acoustic_tag_id IS NOT NULL THEN 'acoustic-archival'
    WHEN tag_type.name = 'sensor-tag' THEN 'archival'
  END AS tag_type,
  CASE
    WHEN tag_subtype.name = 'animal' THEN 'animal'
    WHEN tag_subtype.name = 'built-in tag' THEN 'built-in'
    WHEN tag_subtype.name = 'range tag' THEN 'range'
    WHEN tag_subtype.name = 'sentinel tag' THEN 'sentinel'
  END AS tag_subtype,
  tag_union.*
FROM
  common.tag_device_limited AS tag_device
    LEFT JOIN common.tag_device_type AS tag_type
      ON tag_device.tag_device_type_fk = tag_type.id_pk
    LEFT JOIN acoustic.acoustic_tag_subtype AS tag_subtype
      ON tag_device.acoustic_tag_subtype_fk = tag_subtype.id_pk
    LEFT JOIN (
      SELECT
        'acoustic:' ||  acoustic_tag.id_pk AS tag_id,
        tag_device_fk,
        sensor_type,
        tag_full_id AS acoustic_tag_id,
        thelma_converted_code,
        frequency,
        NULL AS resolution, NULL AS unit, NULL AS accurency, NULL AS range_min, NULL AS range_max,
        slope, intercept, range, sensor_transmit_ratio, accelerometer_algoritm, accelerometer_samples_per_second,
        min_delay, max_delay, power, duration_step1, acceleration_on_sec_step1,
        min_delay_step2, max_delay_step2, power_step2, duration_step2, acceleration_on_sec_step2,
        min_delay_step3, max_delay_step3, power_step3, duration_step3, acceleration_on_sec_step3,
        min_delay_step4, max_delay_step4, power_step4, duration_step4, acceleration_on_sec_step4
        -- serial_number_tbd, type_tbd, model_tbd, owner_pi_tbd, activation_date_tbd,
        -- end_date_tbd, estimated_lifetime_tbd, acoustic_tag_type_tbd, manufacturer_fk_tbd,
        -- owner_group_fk_tbd, financing_project_fk_tbd, status_tbd
        -- id_code, tag_code_space AS protocol, id_pk, file, units, external_id
      FROM
        acoustic.tags AS acoustic_tag
      UNION
      SELECT
        'archive:' || archival_tag.id_pk AS tag_id,
        device_tag_fk AS tag_device_fk,
        sensor_type.description AS sensor_type,
        sensor_full_id AS acoustic_tag_id,
        NULL AS thelma_converted_code,
        frequency,
        resolution, unit, accurency, range_min, range_max,
        slope, intercept, range, sensor_transmit_ratio, accelerometer_algoritm, accelerometer_samples_per_second,
        min_delay, max_delay, power, duration_step1, acceleration_on_sec_step1,
        min_delay_step2, max_delay_step2, power_step2, duration_step2, acceleration_on_sec_step2,
        min_delay_step3, max_delay_step3, power_step3, duration_step3, acceleration_on_sec_step3,
        min_delay_step4, max_delay_step4, power_step4, duration_step4, acceleration_on_sec_step4
        -- id_pk, id_code protocol
      FROM
        archive.sensor AS archival_tag
        LEFT JOIN archive.sensor_type AS sensor_type
          ON archival_tag.sensor_type_fk = sensor_type.id_pk
    ) AS tag_union
      ON tag_device.id_pk = tag_union.tag_device_fk

query

    SELECT
      tag.tag_serial_number AS tag_serial_number,
      tag.tag_type AS tag_type,
      tag.tag_subtype AS tag_subtype,
      tag.sensor_type AS sensor_type,
      tag.acoustic_tag_id AS acoustic_tag_id,
      tag.thelma_converted_code AS acoustic_tag_id_alternative,
      manufacturer.project AS manufacturer,
      tag_device.model AS model,
      tag.frequency AS frequency,
      tag_status.name AS status,
      tag_device.activation_date AS activation_date,
      tag_device.battery_estimated_lifetime AS battery_estimated_life,
      tag_device.battery_estimated_end_date AS battery_estimated_end_date,
      tag_device.archive_length AS length,
      tag_device.archive_diameter AS diameter,
      tag_device.archive_weight AS weight,
      tag_device.archive_floating AS floating,
      tag_device.device_internal_memory AS archive_memory,
      tag.slope AS sensor_slope,
      tag.intercept AS sensor_intercept,
      tag.range AS sensor_range,
      tag.range_min AS sensor_range_min,
      tag.range_max AS sensor_range_max,
      tag.resolution AS sensor_resolution,
      tag.unit AS sensor_unit,
      tag.accurency AS sensor_accuracy,
      tag.sensor_transmit_ratio AS sensor_transmit_ratio,
      tag.accelerometer_algoritm AS accelerometer_algorithm,
      tag.accelerometer_samples_per_second AS accelerometer_samples_per_second,
      CASE
        WHEN tag_device.owner_group_fk_limited IS NOT NULL THEN owner_organization.name
        ELSE NULL
      END AS owner_organization,
      CASE
        WHEN tag_device.owner_group_fk_limited IS NOT NULL THEN tag_device.owner_pi
        ELSE NULL
      END AS owner_pi,
      financing_project.projectcode AS financing_project,
      tag.min_delay AS step1_min_delay,
      tag.max_delay AS step1_max_delay,
      tag.power AS step1_power,
      tag.duration_step1 AS step1_duration,
      tag.acceleration_on_sec_step1 AS step1_acceleration_duration,
      tag.min_delay_step2 AS step2_min_delay,
      tag.max_delay_step2 AS step2_max_delay,
      tag.power_step2 AS step2_power,
      tag.duration_step2 AS step2_duration,
      tag.acceleration_on_sec_step2 AS step2_acceleration_duration,
      tag.min_delay_step3 AS step3_min_delay,
      tag.max_delay_step3 AS step3_max_delay,
      tag.power_step3 AS step3_power,
      tag.duration_step3 AS step3_duration,
      tag.acceleration_on_sec_step3 AS step3_acceleration_duration,
      tag.min_delay_step4 AS step4_min_delay,
      tag.max_delay_step4 AS step4_max_delay,
      tag.power_step4 AS step4_power,
      tag.duration_step4 AS step4_duration,
      tag.acceleration_on_sec_step4 AS step4_acceleration_duration,
      tag.tag_id AS tag_id,
      tag_device.id_pk AS tag_device_id
      -- tag_device.qc_migration
      -- tag_device.order_number
      -- tag_device.external_id
    FROM ({tag_sql}) AS tag
      LEFT JOIN common.tag_device_limited AS tag_device
        ON tag.tag_device_fk = tag_device.id_pk
        LEFT JOIN common.manufacturer AS manufacturer
          ON tag_device.manufacturer_fk = manufacturer.id_pk
        LEFT JOIN common.tag_device_status AS tag_status
          ON tag_device.tag_device_status_fk = tag_status.id_pk
        LEFT JOIN common.etn_group AS owner_organization
          ON tag_device.owner_group_fk_limited = owner_organization.id_pk
        LEFT JOIN common.projects AS financing_project
          ON tag_device.financing_project_fk = financing_project.id
    WHERE
      tag.tag_serial_number IN ({tag_serial_number*})
      ANDtag.tag_type IN ({tag_type*})
      AND tag.tag_subtype IN ({tag_subtype*})
      AND tag.acoustic_tag_id IN ({acoustic_tag_id*})

postprocessing

 tags <-
    tags |>
    dplyr::arrange(factor(.data$tag_serial_number, levels = list_tag_serial_numbers(credentials)))

    # Set the column classes explicitly
    dplyr::mutate(floating = as.logical(.data$floating))

[PietrH]

@Stijn-VLIZ

• I've listed the sql queries behind all get_ functions here. Would you like me to do the same for the list_ functions? These will be much simpler.

• Is this a convenient format for you, or would it be better if I transferred this information to a google doc?

• Do you need anything else? If helpful, I can link to the exact lines the queries take place in inbo/etnservice

[PietrH]

@Stijn-VLIZ Do you need any more information from me?

[Stijn-VLIZ]

I think we can start with the get queries first.
They look very similar to what we have used to create our first queries.
I'm gonna try to set them all up so moratorium and open data can be accessed as many of these use views that only support open data.