search

afsc-assessments / sop

https://afsc-assessments.github.io/sop/
1 stars 1 forks source link

add species code and area lookup table #6

Open mkapur-noaa opened 2 years ago

mkapur-noaa commented 2 years ago

If this already exists, provide a link, otherwise a table which indicates the codes for given species within each dataset. Might make sense to have this in tandem with the area/stratum codes as a reference for people building SQL queries.

BenWilliams-NOAA commented 2 years ago

https://repository.library.noaa.gov/view/noaa/31571

mkapur-noaa commented 2 years ago

Awesome, thanks -- I think just embedding this perhaps at the top of Chapter 3.4 would make sense.

JaneSullivan-NOAA commented 2 years ago

There are separate species codes for RACE, NORPAC, AKR, and IPHC...and then species group codes... species are defined and aggregated differently within each of these code lists. There is no master list to my knowledge. The AKR has a species translation table but it only includes AKR and NORPAC to my knowledge.

zandjyo commented 2 years ago

This table has the RACE/Observer codes. NORPAC.ATL_LOV_SPECIES_CODE

zandjyo commented 2 years ago

And then for the region codes they can be found here: norpac.species_group_codes

JaneSullivan-NOAA commented 2 years ago

For the species codes there are two tables on NORPAC that can do the translations NORPAC.ATL_LOV_SPECIES_CODE and norpac.species_group_codes

zandjyo commented 2 years ago

The group codes for the foreign catch accounting data are different from those two tables, and as far as I know there isn't a key. I will look into it though.

zandjyo commented 2 years ago

Here is a SQL script that will connect the 2 for a master table, could probably get a view put up on AKFIN pretty easily.

SELECT norpac.atl_lov_species_group.species_code, norpac.atl_lov_species_group.prohib_species_group_code, norpac.atl_lov_species_group.common_name, norpac.atl_lov_species_group.scientific_name, norpac.atl_lov_species_group.group_code, norpac.atl_lov_species_group.unid_code, norpac.species_group_codes.bsa_group, norpac.species_group_codes.goa_group, norpac.species_group_codes.woc_group FROM norpac.atl_lov_species_group INNER JOIN norpac.species_group_codes ON norpac.atl_lov_species_group.species_code = norpac.species_group_codes.species WHERE norpac.species_group_codes.year = 0 ORDER BY norpac.atl_lov_species_group.species_code

careymcg commented 2 years ago

Maybe a simple way to move forward (which could be made fancier in the future) would be to provide either a link or a description of how to access each list of species codes. It would be really nice to be able to look in one place to figure out where to go for these pieces of info.