search

inbo / etn

R package to access data from the European Tracking Network
https://inbo.github.io/etn/
MIT License
6 stars 4 forks source link

Some tag_device have the same serial_number: is this by design? #173

Open peterdesmet opened 2 years ago

peterdesmet commented 2 years ago 
animals3 <- dbGetQuery(con, glue_sql("
SELECT
  tag.serial_number,
  animal_with_tag.animal_release_fk,
  COUNT(*)
FROM common.tag_device AS tag
  LEFT JOIN common.animal_release_tag_device AS animal_with_tag
    ON tag.id_pk = animal_with_tag.tag_device_fk
GROUP BY
  tag.serial_number,
  animal_with_tag.animal_release_fk
HAVING COUNT(*) > 1
"))
serial_number animal_release_fk count
1293290 5363 2
1293289 5364 2
1280669 5840 2
1280688 5841 2
1400261 7180 2
1400259 7181 2
1400260 7182 2
1400262 7183 2
1400264 7184 2
1400268 7575 2
etc.  ... ...

Some tags (of the same or different type) have the same serial_number. Is this by design? I though - especially for tags of the same type - that they would have a single animal_release_tag_device id.

peterdesmet commented 2 years ago

To visualize my question, the situation for some tags seems to be this:

            common.animal_release_tag_device – common.tag_device (serial A) - sensor.tag "temp"
           /
common.animal_release
           \
            common.animal_release_tag_device – common.tag_device (serial A) - sensor.tag "pressure"

Rather than:

                                                                               sensor.tag "temp"
                                                                               /
common.animal_release - common.animal_release_tag_device – common.tag_device (serial A)
                                                                               \
                                                                               sensor.tag "pressure"

Examples:

https://www.lifewatch.be/etn/animals/5340 (archival + archival) https://www.lifewatch.be/etn/animals/5841 (archival + acoustic)