Extra shortspine thornyhead lengths in the southern Bering Sea in AI survey?

[JaneSullivan-NOAA]

Issue

The problem can be reproduced using this script, and the figure copied above can be reproduced with from this line.

Notably, I do not see this same issue with other species in the other rockfish complex. For example, here is dusky rockfish:

[zoyafuso-NOAA]

Hi @JaneSullivan-NOAA, (adding @Ned-Laman-NOAA here)

Thanks for posting this, I was actually trying to find the html you shared with us with this code today. I think I found the issue. The duplication occurs in GAP_PRODUCTS.SIZECOMP, see this example SQL query and the result:

select * 
from gap_products.sizecomp 
where survey_definition_id = 52 
and species_code in (30152, 30020) 
and year = 2014 
and area_id in (793, 794) 
and length_mm = 300 
order by sex, area_id

SURVEY_DEFINITION_ID	YEAR	AREA_ID	SPECIES_CODE	LENGTH_MM	SEX	POPULATION_COUNT
52	2014	793	30020	300	1	41039
52	2014	793	30020	300	1	41039
52	2014	794	30020	300	1	387920
52	2014	794	30020	300	1	387920
52	2014	793	30020	300	2	21048
52	2014	793	30020	300	2	21048
52	2014	794	30020	300	2	359327
52	2014	794	30020	300	2	359327

@Ned-Laman-NOAA , this issue (1) highlights the need for me to set keys for these tables because I would have gotten an error or something about this duplication and (2) favors the idea of creating new codes for the Aleutian Island SBS INPFC x DEPTH subareas 793 and 794. Although the composite of (SURVEY_DEFINITION_ID, AREA_ID, DESIGN_YEAR, AREA_TYPE) is the key for the GAP_PRODUCTS.AREA table, when we switch over to the size tables, since we don't have an AREA_TYPE field in those tables, the composite key of (SURVEY_DEFINITION_ID,YEAR,AREA_ID,SPECIES_CODE,LENGTH_MM,SEX) is not unique. The reason this happens is the stratum and subarea* estimates are done separately and then row-binded together, e.g., the stratum 793 and INPFC x DEPTH 793 are row-binded together.

@JaneSullivan-NOAA: I think maybe because duskies are found less (or not at all) in those deeper 793/794 areas, you don't see the same disparity in the length distributions. Also as a heads up, we just corrected the AREA_TYPE for the INPFC and INPFCxDepth areas from "SUBAREA" and "SUBAREA BY DEPTH" to "INPFC" and "INPFC BY DEPTH" in GAP_PRODUCTS.AREA. So the next time we transfer the tables to AKFIN again, you will need to change those strings in your code. And we'll let you know when that happens.

[zoyafuso-NOAA]

(adding AKFIN folks @MattCallahan-NOAA , @jeanlee-akfin)

We've talked about this and the solution is to change the AREA_ID of the SBS INPFC BY DEPTH areas. This will remove the duplicates found in the current gap_products.biomass and gap_products.sizecomp tables for the 793/794 area. After today's office hours, I will rerun the production code that creates the GAPPRODUCTS and AKFIN tables, then I will ask Ned to initiate the transfer of the AKFIN_ tables over to AKFIN. Jane, let's revisit this issue after that transfer has been completed (and hopefully it doesn't break your code again!).

The changes are:

AREA_TYPE	OLD AREA_ID	NEW AREA_ID
INPFC BY DEPTH	791	7891
INPFC BY DEPTH	792	7892
INPFC BY DEPTH	793	7893
INPFC BY DEPTH	794	7894

The naming convention follows other INPFC by DEPTH areas:

select * 
from gap_products.area
where survey_definition_id = 52
and area_type = 'INPFC BY DEPTH'

SURVEY_DEFINITION_ID	DESIGN_YEAR	AREA_ID	AREA_TYPE	AREA_NAME	DESCRIPTION
52	1980	291	INPFC BY DEPTH	Western Aleutians	Western Aleutians 1 m - 100 m
52	1980	292	INPFC BY DEPTH	Western Aleutians	Western Aleutians 101 m - 200 m
52	1980	293	INPFC BY DEPTH	Western Aleutians	Western Aleutians 201 m - 300 m
52	1980	294	INPFC BY DEPTH	Western Aleutians	Western Aleutians 301 m - 500 m
52	1980	7891	INPFC BY DEPTH	Southern Bering Sea	Southern Bering Sea 1 m - 100 m
52	1980	7892	INPFC BY DEPTH	Southern Bering Sea	Southern Bering Sea 101 m - 200 m
52	1980	7893	INPFC BY DEPTH	Southern Bering Sea	Southern Bering Sea 201 m - 300 m
52	1980	7894	INPFC BY DEPTH	Southern Bering Sea	Southern Bering Sea 301 m - 500 m
52	1980	3491	INPFC BY DEPTH	Central Aleutians	Central Aleutians 1 m - 100 m
52	1980	3492	INPFC BY DEPTH	Central Aleutians	Central Aleutians 101 m - 200 m
52	1980	3493	INPFC BY DEPTH	Central Aleutians	Central Aleutians 201 m - 300 m
52	1980	3494	INPFC BY DEPTH	Central Aleutians	Central Aleutians 301 m - 500 m
52	1980	5691	INPFC BY DEPTH	Eastern Aleutians	Eastern Aleutians 1 m - 100 m
52	1980	5692	INPFC BY DEPTH	Eastern Aleutians	Eastern Aleutians 101 m - 200 m
52	1980	5693	INPFC BY DEPTH	Eastern Aleutians	Eastern Aleutians 201 m - 300 m
52	1980	5694	INPFC BY DEPTH	Eastern Aleutians	Eastern Aleutians 301 m - 500 m

[Ned-Laman-NOAA]

@zoyafuso-NOAA Are you ready for me to initiate the next AKFIN transfer? Just say when!