Acoustic Data
acoustic_data - This table stores the detection data. It is a large table and queries to it can be slow.
Do NOT use the date, time, fdate or ftime fields. The correct field for detection date/time is ping_detection. Joins to tag metadata can be made with both code and codespace. Joins to receiver data can be made with receiver and ping_detection or receiver and receiver_dnum.
acoustic_deployment - This table contains deployment info for acoustic receivers affiliated with Block Lab acoustic detection data. A deployment represents a specific period of time at a specific location and is denoted by the fields receiver and receiver_dnum. It's also possible to use receiver and deploystamp (the receiver deployment date field) but for any receiver in the table that was not deployed by the Block Lab the deploystamp field will likely be null. The geographic name for the deployment is broken down into three levels by area of influence - station, site and region. These levels are subjective.
acoustic_recovery - This table contains recovery info for acoustic receivers affiliated with Block Lab acoustic detection data. Generally the only important fields are receiver, receiver_dnum and recdatetime. It joins to acoustic_deployment with receiver and receiver_dnum.
Example query:
SELECT tm.eventid,ac.code,ac.ping_detection,ad.site
FROM acoustic_data as ac
INNER JOIN acoustic_deployment as ad ON ac.receiver = ad.receiver AND ac.receiver_dnum = ad.receiver_dnum
INNER JOIN tm_acoustic_with_eventid as tm ON ac.code = tm.idcode AND ac.codespace = tm.codespace1
WHERE tm.eventid::text like '51%' AND ac.ping_detection > tm.taggingdate::date AND ac.ping_detection <= current_date AND ac.false_hit <> 1
ORDER BY 1,3;