{"action":"created","issue":{"url":"https://api.github.com/repos/aodn/aatams/issues/208","labels_url":"https://api.github.com/repos/aodn/aatams/issues/208/labels{/name}","comments_url":"https://api.github.com/repos/aodn/aatams/issues/208/comments","events_url":"https://api.github.com/repos/aodn/aatams/issues/208/events","html_url":"https://github.com/aodn/aatams/issues/208","id":58293355,"number":208,"title":"Some transmitters re-deployed several times have the same tag_id","user":{"login":"xhoenner","id":3443821,"avatar_url":"https://avatars.githubusercontent.com/u/3443821?v=3","gravatar_id":"","url":"https://api.github.com/users/xhoenner","html_url":"https://github.com/xhoenner","followers_url":"https://api.github.com/users/xhoenner/followers","following_url":"https://api.github.com/users/xhoenner/following{/other_user}","gists_url":"https://api.github.com/users/xhoenner/gists{/gist_id}","starred_url":"https://api.github.com/users/xhoenner/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xhoenner/subscriptions","organizations_url":"https://api.github.com/users/xhoenner/orgs","repos_url":"https://api.github.com/users/xhoenner/repos","events_url":"https://api.github.com/users/xhoenner/events{/privacy}","received_events_url":"https://api.github.com/users/xhoenner/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/aodn/aatams/labels/bug","name":"bug","color":"fc2929"},{"url":"https://api.github.com/repos/aodn/aatams/labels/high%20priority","name":"high priority","color":"eb6420"},{"url":"https://api.github.com/repos/aodn/aatams/labels/in%20progress","name":"in progress","color":"ededed"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":5,"created_at":"2015-02-20T00:27:47Z","updated_at":"2015-10-26T00:39:04Z","closed_at":null,"body":"<h3> Steps to reproduce </h3>\r\n``` sql\r\nSET SEARCH_PATH = aatams,public;\r\nSELECT DISTINCT s.transmitter_id, s.tag_id, su.release_id, ar.animal_id, ar.releasedatetime_timestamp,sp.scientific_name\r\nFROM sensor s\r\nFULL JOIN surgery su ON s.tag_id = su.tag_id\r\nFULL JOIN animal_release ar ON ar.id = su.release_id\r\nFULL JOIN animal a ON a.id = ar.animal_id\r\nFULL JOIN species sp ON sp.id = a.species_id \r\nWHERE transmitter_id = 'A69-1303-10691'\r\nORDER BY s.transmitter_id, s.tag_id\r\n```\r\n\r\nAdditionally, go to https://aatams.emii.org.au/aatams/detection/list, and download detections for Tag ID A69-1303-10691. Sort the resulting CSV by ascending timestamp.\r\n\r\n<h3> What happens? </h3>\r\nThe results of the SQL query produces two rows, which is fine, aside from the fact that the tag_id is the same for both, which gives you for the same tag_id two different species (see screenshot below)\r\n\r\n![screen shot 2015-02-20 at 11 13 41 am](https://cloud.githubusercontent.com/assets/3443821/6279131/903d3738-b8f1-11e4-94c7-210f4c3de840.jpg)\r\n\r\nAs a result of that, when you download a CSV using the web app, each detection is duplicated since the transmitter/tag_id combination is associated to two species. This makes all the detections for that transmitter useless because users can't know which species was detected. \r\n\r\n![screen shot 2015-02-20 at 11 16 29 am](https://cloud.githubusercontent.com/assets/3443821/6279164/f7bc1988-b8f1-11e4-900e-9833c8f1b4ef.jpg)\r\n\r\n<h3> What should happen? </h3>\r\nIf a transmitter was re-deployed onto another animal, then it should have a different/unique tag_id, thus making it possible to retrace a single animal_id and species. \r\n\r\nThe SQL query below will give you a list of duplicate transmitter_id/tag_id combinations:\r\n\r\n``` sql\r\nWITH a AS (SELECT DISTINCT s.transmitter_id, s.tag_id, su.release_id, ar.animal_id, ar.releasedatetime_timestamp,sp.scientific_name\r\nFROM sensor s\r\nFULL JOIN surgery su ON s.tag_id = su.tag_id\r\nFULL JOIN animal_release ar ON ar.id = su.release_id\r\nFULL JOIN animal a ON a.id = ar.animal_id\r\nFULL JOIN species sp ON sp.id = a.species_id \r\nWHERE transmitter_id != ''\r\nORDER BY s.transmitter_id, s.tag_id),\r\nb AS (SELECT transmitter_id, tag_id, COUNT(*) AS duplicate_number\r\nFROM a\r\nGROUP BY transmitter_id, tag_id)\r\nSELECT transmitter_id, tag_id, duplicate_number\r\nFROM b\r\nWHERE duplicate_number > 1\r\nORDER BY transmitter_id, tag_id\r\n```"},"comment":{"url":"https://api.github.com/repos/aodn/aatams/issues/comments/150992281","html_url":"https://github.com/aodn/aatams/issues/208#issuecomment-150992281","issue_url":"https://api.github.com/repos/aodn/aatams/issues/208","id":150992281,"user":{"login":"xhoenner","id":3443821,"avatar_url":"https://avatars.githubusercontent.com/u/3443821?v=3","gravatar_id":"","url":"https://api.github.com/users/xhoenner","html_url":"https://github.com/xhoenner","followers_url":"https://api.github.com/users/xhoenner/followers","following_url":"https://api.github.com/users/xhoenner/following{/other_user}","gists_url":"https://api.github.com/users/xhoenner/gists{/gist_id}","starred_url":"https://api.github.com/users/xhoenner/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xhoenner/subscriptions","organizations_url":"https://api.github.com/users/xhoenner/orgs","repos_url":"https://api.github.com/users/xhoenner/repos","events_url":"https://api.github.com/users/xhoenner/events{/privacy}","received_events_url":"https://api.github.com/users/xhoenner/received_events","type":"User","site_admin":false},"created_at":"2015-10-26T00:39:04Z","updated_at":"2015-10-26T00:39:04Z","body":"Good point @jkburges, @dnahodil can you extract that information for @astecken?"}}