How do we assign INPFC area to the AFSC database's RACEBASE.SPECIMEN?

[JaneSullivan-NOAA]

I tried joining BIOSTRATUM as STRATUM to STRATUM in the AKFIN database's AFSC.RACE_GOASTRATAAIGOA. Also looked at the AFSC database's RACEBASE.STRATA table, but there was no AI information in there for some reason

> DBI::dbGetQuery(afsc, 
+                 "select   distinct biostratum as stratum
+                  from     racebase.specimen
+                  where    region in ('AI')") %>%
+   dplyr::left_join(
+     DBI::dbGetQuery(akfin, 
+                     "select   distinct stratum, inpfc_area 
+                      from     afsc.race_goastrataaigoa 
+                      where    survey in ('AI')", 
+                     as.is = TRUE, believeNRows = FALSE)
+   ) %>% 
+   dplyr::arrange(STRATUM)

Joining with `by = join_by(STRATUM)`
    STRATUM          INPFC_AREA
1         0                <NA>
2         1                <NA>
3         2                <NA>
4         3                <NA>
5         4                <NA>
6         5                <NA>
7         6                <NA>
8         7                <NA>
9       100                <NA>
10      111                <NA>
11      112                <NA>
12      113                <NA>
13      120                <NA>
14      121                <NA>
15      122                <NA>
16      123                <NA>
17      131                <NA>
18      132                <NA>
19      133                <NA>
20      140                <NA>
21      141                <NA>
22      142                <NA>
23      143                <NA>
24      151                <NA>
25      152                <NA>
26      153                <NA>
27      180                <NA>
28      200                <NA>
29      211   Western Aleutians
30      212   Western Aleutians
31      213   Western Aleutians
32      214   Western Aleutians
33      221   Western Aleutians
34      222   Western Aleutians
35      223   Western Aleutians
36      224   Western Aleutians
37      231                <NA>
38      232                <NA>
39      233                <NA>
40      241                <NA>
41      242                <NA>
42      243                <NA>
43      251                <NA>
44      252                <NA>
45      253                <NA>
46      260                <NA>
47      280                <NA>
48      300                <NA>
49      311   Central Aleutians
50      312   Central Aleutians
51      313   Central Aleutians
52      314   Central Aleutians
53      320                <NA>
54      321   Central Aleutians
55      322   Central Aleutians
56      323   Central Aleutians
57      324   Central Aleutians
58      325                <NA>
59      331                <NA>
60      332                <NA>
61      333                <NA>
62      341                <NA>
63      342                <NA>
64      343                <NA>
65      351                <NA>
66      352                <NA>
67      400                <NA>
68      411   Central Aleutians
69      412   Central Aleutians
70      413   Central Aleutians
71      414   Central Aleutians
72      421   Central Aleutians
73      422   Central Aleutians
74      423   Central Aleutians
75      424   Central Aleutians
76      425                <NA>
77      431                <NA>
78      432                <NA>
79      433                <NA>
80      441                <NA>
81      442                <NA>
82      443                <NA>
83      451                <NA>
84      452                <NA>
85      453                <NA>
86      500                <NA>
87      502                <NA>
88      504                <NA>
89      511   Eastern Aleutians
90      512   Eastern Aleutians
91      513   Eastern Aleutians
92      521   Eastern Aleutians
93      522   Eastern Aleutians
94      523   Eastern Aleutians
95      531                <NA>
96      541                <NA>
97      551                <NA>
98      594   Eastern Aleutians
99      611   Eastern Aleutians
100     612   Eastern Aleutians
101     613   Eastern Aleutians
102     614   Eastern Aleutians
103     621   Eastern Aleutians
104     622   Eastern Aleutians
105     623   Eastern Aleutians
106     624   Eastern Aleutians
107     650                <NA>
108     700                <NA>
109     702                <NA>
110     710                <NA>
111     711 Southern Bering Sea
112     712 Southern Bering Sea
113     721 Southern Bering Sea
114     722 Southern Bering Sea
115     731                <NA>
116     732                <NA>
117     741                <NA>
118     742                <NA>
119     751                <NA>
120     752                <NA>
121     780                <NA>
122     790                <NA>
123     793 Southern Bering Sea
124     794 Southern Bering Sea
125     810                <NA>

[JaneSullivan-NOAA]

@zoyafuso-NOAA @MargaretSiple-NOAA this question came up yesterday... maybe you can help?

[zoyafuso-NOAA]

Hi Jane,

Currently we use the AFSC table GOA.GOA_STRATA to obtain stratum information for both the AI and GOA, so there may be more information in that table that may help, but let me know otherwise. Are you referring to the INFPC areas in the AI as the the four survey districts: Western, Central, Eastern, and Southern Bering Sea?

[JaneSullivan-NOAA]

Thanks @zoyafuso-NOAA. Yes, I am talking about the WAI, CAI, EAI, and SBS. It looks like I don't have select access to the GOA schema... does this table do the trick?

[zoyafuso-NOAA]

I'll send you the table.

[JaneSullivan-NOAA]

@zoyafuso-NOAA, got it, thanks! So, it looks like the GOA.GOA_STRATA table has the same information and stratum definition/categories as AKFIN's AFSC.RACE_GOASTRATAAIGOA. The issue is that there are a bunch more BIOSTRATUM categories in the RACEBASE.SPECIMEN table in the AI region.

I did a test for POP and got the following results (note that I used both the AKFIN table and the csv you shared, and they are the same). You can see that NA values dominate, which tells me that BIOSTRATUM may have a different definition than STRATUM.

DBI::dbGetQuery(afsc, 
                "select   cruise, biostratum as stratum, age, sex, length, weight
                 from     racebase.specimen
                 where    region in ('AI') and
                          species_code in ('30060')") %>%
  dplyr::left_join(
    readr::read_csv('GOA_STRATA.csv') %>% 
      dplyr::filter(SURVEY == 'AI') %>% 
      dplyr::select(STRATUM, INPFC_AREA)) %>% 
  dplyr::group_by(INPFC_AREA) %>% 
  dplyr::tally() 

  INPFC_AREA              n
  <chr>               <int>
1 Central Aleutians    1362
2 Eastern Aleutians     814
3 Southern Bering Sea   232
4 Western Aleutians    1292
5 NA                  18501

DBI::dbGetQuery(afsc, 
                "select   cruise, biostratum as stratum, age, sex, length, weight
                 from     racebase.specimen
                 where    region in ('AI') and
                          species_code in ('30060')") %>%
    dplyr::left_join(
      DBI::dbGetQuery(akfin, 
                    "select   distinct stratum, inpfc_area 
                     from     afsc.race_goastrataaigoa 
                     where    survey in ('AI')", 
                      as.is = TRUE, believeNRows = FALSE)
    ) %>% 
    dplyr::group_by(INPFC_AREA) %>% 
    dplyr::tally()

  INPFC_AREA              n
  <chr>               <int>
1 Central Aleutians    1362
2 Eastern Aleutians     814
3 Southern Bering Sea   232
4 Western Aleutians    1292
5 NA                  18501

[JaneSullivan-NOAA]

@pdspencer just FYI, Zack O is trying to help us out on this one.

[zoyafuso-NOAA]

BIOSTRATUM is a new field for me. Doing some digging, it seems that BIOSTRATUM was a column that was used sporadically pre-2000. From 2000-onwards the value for BIOSTRATUM is zero. So if you wanted to classify the AI specimen data by INPFC area, I would join the racebase.specimen to the racebase.haul table by HAULJOIN to get STRATUM information for each specimen record. Then, join that with the goa.goa_strata table (subsetted by SURVEY == "AI" to avoid duplicate stratum names between GOA and AI) by STRATUM to append the INPFC_AREA info to each specimen record.

[JaneSullivan-NOAA]

Thank you so much @zoyafuso-NOAA!

[pdspencer]

Thanks, Zach (and Jane for bringing up this discussion)! Zach, I will give your solution a try.

Jane, I realized why I did not initially see the AFSC.RACE_GOASTRATAAIGOA table yesterday -- I was looking on the afsc database, not akfin.