17_important data tables

Overview

Teaching: min
Exercises: min

Questions

Objectives

title: “17. Important Data Tables” teaching: 25 exercises: 17 questions:

• “What information can we find in these tables?”
• “What are the corresponsing SQL code to access the data?”

1. Vendor Schema

The OTN Node database format includes a “vendor” schema which contains multiple tables. Each table is formatted to hold tag or receiver speciications for a certain manufactoruer. These tables are used by the Nodebooks during verification processes and it is often important for Node Managers to do further investigating by searching these tables.

These are the tables:

• vendor.c_vemco_tags
• vendor.c_vemco_receivers
• vendor.c_thelma_tags
• vendor.c_thelma_receivers
• vendor.contacts_auths

Here are some useful SQL queries:

• select * from vendor.c_vemco_tags where serial_no ='XXXXX'
• select * from vendor.c_vemco_tags where vue_id ='XXXXX'
• select * from vendor.c_vemco_receivers where tag_id ='XXXXX'
• select * from vendor.c_vemco_receivers where serial_no ='XXXXX'
• select * from vendor.c_thelma_receivers where serial_no ='XXXXX'

When we identify problems like different tag ID or tag life expectancy in tag-1 notebook, we should check which one is correct in vendor.c_vemco_tags.

Details about vendor.c_vemco_tags table:

• column tag_fam represents tag model type (V7, V9, V16…)
• column vue_id represents transmitter ID. Note some tags (the same serials) may have several different transmitter IDs because they have different ID_codes.
• column est_tag_life represents a tag’s life expectancy

When we identify problems like different transmitter_IDs in deployment notebook, we want to check which one is correct in vendor.c_vemco_receivers

When there’s a missing INNOVASEA spec (warning shows as below), we want to first check if we have a auth file

SELECT * FROM obis.contacts c
LEFT JOIN vendor.contacts_auths ca
ON c.contact_pk = ca.contact_pk
LEFT JOIN obis.contacts_projects cp
ON c.contact_pk = cp.contact_pk WHERE
  collectioncode = 'XXXXX'

Details about vendor.c_vemco_receivers table:

• model represents receiver models (VR2AR, VR2Tx, VR4…)
• tag_id represents transmitter ID.

2. OBIS Schema

The OTN Node database format includes a “OBIS” schema which contains multiple tables. Each table is formatted to hold speiecs, instrument, animal, and project information

There are a couple of important tables:

• obis.scientificnames: list of projects and their associated species. select * from obis.scientificnames where collectioncode = 'XXX'
• obis.lengthtype_codes: a collection of all existing length types and their entry in the standard vocabulary of NERC: select * from obis.lengthtype_codes
• obis.lifestage_codes: a collection of all existing lifestage codes and their entry in the standard vocabulary of NERC: select * from obis.lifestage_codes
• obis.instrument_models: a collection of all existing instrument model types: select * from obis.instrument_models
• obis.institution_codes: a collection of existing institutions with their information: select * from obis.institution_codes
• obis.contacts: a collection of existing contacts with a unique identifier: select * from obis.contacts
• obis.contacts_projects: a table of associations between contacts (with the unique identifier) and the projects they are a part of: select * from obis.contacts_projects where collectioncode = 'XXXXX'
• obis.otn_animals: an aggregation of all the animal information across all projects. This is a read-only table and should not be updated: select * from obis.otn_animals
• obis.moorings: an aggregation of all the receiver and tag deployments information across all projects. This is a read-only table and should not be updated: select * from obis.moorings
  • It contains information about all tags, transmitters, VMTs, receivers, events, downloads, etc. In this table, the columns basisofrecord and relationshiptype distinguish which type of data it is.
• obis.detection_extracts_list: a collection of all detection extracts for all pushes, with associated Gitlab issue: select * from obis.detection_extracts_list where push_date = 'YYYY-MM-DD'
• obis.otn_resources: a collection of projects and their information: select * from obis.otn_resources where collectioncode= 'XXXXX'
• IN OTNUNIT ONLY: obis.loan_tracking: a collection of project loan information: select * from obis.loan_tracking

3. Discovery Schema

The OTN Node database format includes a “discovery” schema which contains multiple tables. Each table is formatted to hold a summary of detecion information

This schema holds the summarized information of the data. The tables in this schema include:

• discovery.all_animals: The animal biology information, scientific names, and release locations and times: select * from discovery.all_animals
• discovery.detection_pre_summary: summarized information about detections and their matches grouped by week: select * from discovery.detection_pre_summary. Some noteworthy columns in this table:
  • collectioncode represents the receiver project code
  • trackercode represents the tag project code which is matched to the detection
  • relationshiptype represents the type of match (i.e. animal, unqualified, transmitter, and test)
  • weekcollected represents the week of the approximate detection time
  • fieldnumber represents the transmitter ID of the detection
  • rcvrcatnumber represents the unique identifier of the receiver
  • detection_count represents how many times this fish has been detected during that week (min_detect_date to max_detect_date)

These tables have many applications, including creating data reports.

Key Points