Receiver deployment metadata present in aatams and absent from AODN portal extract

[fjaine]

@lbesnard @lwgordonimos not sure if this issue belongs here - feel free to move somewhere more appropriate.

I just noticed that the 'IMOS - AATAMS Facility - Acoustic Receiver Locations' dataset accessible on the AODN Portal is missing some data compared to the contents of the database. For example, I was trying to access receiver deployment locations for 2016-2017 for the 'IMOS ATF Heron Island' project. These metadata show correctly in the aatams database however they do not seem to appear in the dataset downloaded from the portal. Any idea why? And how can I access them in the meantime (can't download via the web app)?

aatams database: https://animaltracking.aodn.org.au/receiverRecovery/list?max=20&sort=deploymentDateTime&order=desc&_filter.recovery.isNull=&filter.station.installation.in=name&filter.station.installation.in=&filter.station.installation.project.eq=name&filter.station.installation.project.eq=IMOS-ATF+Heron+Island&totalMatches=9131&offset=60

AODn portal: https://portal.aodn.org.au/search?uuid=0ede6b3d-8635-472f-b91c-56a758b4e091

[lbesnard]

These metadata show correctly in the aatams database however they do not seem to appear in the dataset downloaded from the portal. Any idea why?

It's really hard to know as I wasn't involved in the project before. But from my understanding, the QC layer on the portal only ran once before submitting the paper to Nature. Maybe the Heron Island project metadata was entered in the database past this first run?

And how can I access them in the meantime (can't download via the web app)?

What do you mean? You can't download the QC data for this project on the web app (because there shouldn't be any via the web app if I understand properly) ? or you can't download any data for this project?

I'll dig through some of @xhoenner 's scripts, but apparently, it is possible to run the QC for a specific project locally. Will see what I can do

[fjaine]

@lbesnard it's not the QC dataset it is the Receiver Deployment dataset which is separate on the portal - sorry if my link didn't point to the right dataset...

[lbesnard]

So just to agree, I got this from this layer for the heron project https://portal.aodn.org.au/search?uuid=4a97bd11-e821-4682-8b20-cb69201f3223

IMOS_-_AATAMSFacility-_Acoustic_Receiver_Locations.csv.zip

and indeed it looks like there is no data for 2016 2017

[lbesnard]

I have to understand how this layer is generated first

[fjaine]

Yes @lbesnard that's the one! Thanks for looking into it!

[lbesnard]

(can't download via the web app)?

Do you have an idea why?

[lbesnard]

Is it because its lacking the feature ?

[lbesnard]

Is that sufficient ? Let me know, I'm kind of trying to figure this out test.csv.zip

[fjaine]

@lbesnard nice one! It looks like this could address my issue, however this extract doesn't contain the station name or receiver model (VR2W, VR2-AR, etc.), so hard to tell for sure that it provides the data I am after. The GPS coordinates are missing too.

[lbesnard]

yeap Im still onto it. I don't know yet if there is an issue with the query itself generating this or an issue with missing data in the table. I have an hour more to work on this, and then will have to leave unfortunately. Will have a deeper look on monday

[lbesnard]

The query done on the portal to find all the Heron Island information is

SELECT DISTINCT ON (p.name,i.name, ist.name)
    p.name AS project_name,
    i.name AS installation_name,
    type AS installation_type,
    ist.name AS station_name, 
    COALESCE(dm.model_name || '-' || d.serial_number) AS receiver_name,
    rd.deploymentdatetime_timestamp AT TIME ZONE 'UTC' AS deployment_date, 
    rr.recoverydatetime_timestamp AT TIME ZONE 'UTC' AS recovery_date, 
    rr.status AS receiver_status,
    CASE WHEN rr.status = 'RECOVERED' OR rr.recoverydatetime_timestamp < now() OR rd.deploymentdatetime_timestamp IS NULL THEN 'Inactive' 
        WHEN rd.deploymentdatetime_timestamp < now() AND (rr.recoverydatetime_timestamp IS NULL OR rr.recoverydatetime_timestamp > now()) THEN 'Active' END AS active,
    ist.location AS geom,
    CASE WHEN substring(p.name, 'IMOS-ATF') = 'IMOS-ATF' THEN TRUE ELSE FALSE END AS imos_b,
    ST_X(ist.location) AS longitude,
    ST_Y(ist.location) AS latitude
  FROM installation_station ist
  LEFT JOIN receiver_deployment rd ON ist.id = rd.station_id
  LEFT JOIN receiver_recovery rr ON rr.deployment_id = rd.id
  LEFT JOIN installation i ON i.id = ist.installation_id
  LEFT JOIN project p ON i.project_id = p.id
  LEFT JOIN installation_configuration ic ON ic.id = i.configuration_id
  LEFT JOIN device d ON d.id = rd.receiver_id
  LEFT JOIN device_model dm ON dm.id = d.model_id
  WHERE i.name = 'IMOS-ATF Heron Island'
    ORDER BY project_name,installation_name, station_name, deploymentdatetime_timestamp DESC;

This outputs 44 lines of information IMOS_-_AATAMSFacility-_Acoustic_Receiver_Locations.csv.zip

However, in that query, there is a DISTINCT ON (p.name,i.name, ist.name) . So Maybe there is some misunderstanding about what should this collection display on the portal. But If I run the query without this distinct

set search_path to aatams_acoustic_reporting, public;
SELECT 
    p.name AS project_name,
    ist.id, 
    i.name AS installation_name,
    ist.name AS station_name, 
    type AS installation_type,

    COALESCE(dm.model_name || '-' || d.serial_number) AS receiver_name,
        rd.deploymentdatetime_timestamp AT TIME ZONE 'UTC' AS deployment_date,
        rr.recoverydatetime_timestamp AT TIME ZONE 'UTC' AS recovery_date, 
    rr.status AS receiver_status,
    ist.location AS geom,
    CASE WHEN rr.status = 'RECOVERED' OR rr.recoverydatetime_timestamp < now() OR rd.deploymentdatetime_timestamp IS NULL THEN 'Inactive' 
        WHEN rd.deploymentdatetime_timestamp < now() AND (rr.recoverydatetime_timestamp IS NULL OR rr.recoverydatetime_timestamp > now()) THEN 'Active' END AS active,
    ist.location AS geom,
    CASE WHEN substring(p.name, 'IMOS-ATF') = 'IMOS-ATF' THEN TRUE ELSE FALSE END AS imos_b,
    ST_X(ist.location) AS longitude,
    ST_Y(ist.location) AS latitude

  FROM installation_station ist
  LEFT JOIN receiver_deployment rd ON ist.id = rd.station_id
  LEFT JOIN receiver_recovery rr ON rr.deployment_id = rd.id
  LEFT JOIN installation i ON i.id = ist.installation_id
  LEFT JOIN project p ON i.project_id = p.id
  LEFT JOIN device d ON d.id = rd.receiver_id
  LEFT JOIN device_model dm ON dm.id = d.model_id
  LEFT JOIN installation_configuration ic ON ic.id = i.configuration_id

  WHERE p.name = 'IMOS-ATF Heron Island'
    ORDER BY project_name,installation_name, station_name, deploymentdatetime_timestamp DESC;

We get 396 lines. test_without_DISTINCT.csv.zip

@fjaine can you have a look if this was what you were after? However I'm not sure if there is any issue what the portal currently outputs. To be defined/decided next week

[fjaine]

@lbesnard I'm running into a similar urgent request from a user about another installation for which the metadata have disappeared during the extract process... is there a way for you to produce the same extract as above fr all installations (without specifying "heron Island")? It's for a publication that needs to be finalised/submitted soon... Thanks for your help.

This issue is quite concerning. I'm surprised we didn't notice this in the past as we went through metadata records quite extensively. Or could it be something new?

[lbesnard]

@fjaine , is this what you want ? aatams_user_query.csv.zip

This issue is quite concerning. I'm surprised we didn't notice this in the past as we went through metadata records quite extensively. Or could it be something new?

as mentioned in my previous comment : "So Maybe there is some misunderstanding about what should this collection display on the portal", I don't know if this is an issue, or how Xavier wanted it to be like this

[fjaine]

@lbesnard yes this worked for what I needed to do - thank you!

However please note that the extract you produced is missing some important metadata fields - let's look at it together next time I am in Hobart and make a list of what is missing / should be there.

All historical receiver deployment records should definitely be displayed on the AODN portal as part of this file - not sure why it is not happening or what's causing the issue

[lbesnard]

• let's look at it together next time I am in Hobart and make a list of what is missing / should be there.

:+1:

not sure why it is not happening or what's causing the issue

it's an easy fix, just got to change the sql query

[fjaine]

@lbesnard

@evacougnon and I recently checked and can confirm that there is still an issue with this - the dataset is still missing some historical receiver deployments that appear in the aatams web app but not in the file produced when downloading from the AODN portal. So I’m stuck as I can’t extract these metadata anywhere else from the database or web-app.

For example, I recently tried to make an extract for the OTN Perth line installation and realised that deployments for years prior to 2014 and between 2015-2018 are missing. Similarly, anything prior to 2015 and for year 2017 for OTN Maria Island seems to be missing. I had also previously noticed some historical annual deployments missing for the IMOS-ATF Heron Island installation.