Missing Temperature Profile Data

[KSiwicke]

Data product requested: Missing temperature data from previous request. I'm processing the whole water column so raw data is fine.

GOA: Cruise 199309 on vessel 21, and cruise 200901 on vessel 159. If those exist.

AI: Cruises 199701 on vessels 23 and 94. If those exist.

EBS SHELF: I have everything for the shelf starting in 2005 to 2021, but I do not have anything before that, though I think it exists as far back as 1982? If I could get at least 1993 to 2004, that'd be great, and I'd take as far back as it goes if not too much trouble. Not sure why I only got data starting in 2005?

EBS SLOPE: I randomly have 2008 and 2010, but I do not have 2002, 2004, 2012 or 2016.

NBS: This is my lowest priority, but again it is kind of random that I got data for 2010, but I do not have 2017, 2019, or 2021.

Output type:

csv is fine

Species: NA

Region :

All

Research team making the request:

Kevin Siwicke kevin.siwicke@noaa.gov at ABL-MESA.

[Ned-Laman-NOAA]

Here are a few bits of information that I can add. The cruises on the NOAA Ship Freeman (21) and Dyson (159) in 1993 and 2009 do not exist in the data repository. BT data for the following are available in RACE_EDIT.RB2_BTD: AI 1997 vessels 23 & 94, EBS Shelf and Slope between 1999 and 2004. EBS Slope BT data from 2012 and 2016 should be available in RACE_DATA tables. NBS data from 2017, 2019, and 2021 should be available in RACE_DATA tables; accessing NBS data takes a little convoluted querying but Duane should have ready access to the logic/syntax. Kevin has specific requirements for the data to include in the outputs with examples here

[NancyRoberson]

Hi Kevin and Ned,

Thanks Ned for supplying the data whereabouts - super helpful especially for the AI 1997, Shelf, and Slope 1999 and 2004.
I am sending the data in separate posts because I thought they went through in a single post last week but I don't that post or the data now.

GOA: Cruise 199309 on vessel 21, and cruise 200901 on vessel 159.

We have data for 199309 and 200901 but they aren't stream data - there is only one temperature value per haul for surface temperature and gear temperature. Please look them over and let me know if you have questions or need changes made to the query.

GOA 199309 VESSEL 21:
GOA_199309_VESSEL21_HaulData.csv GOA_199309_VESSEL21_NOTES.xlsx Notes were available for this cruise so I included them in case you are interested.

GOA 200901 Vessel 159: GOA_200901_VESSEL159_HaulData.csv

Metadata: https://www.fisheries.noaa.gov/foss/f?p=215:29:10937673004490:::::

[NancyRoberson]

Hi Kevin, Please look over these and let me know if you have questions or need changes made to the query.

AI: Cruises 199701 on vessels 23 and 94 AI_199701_VESSEL23_HaulData.csv AI_199701_VESSEL94_BTdata.csv AI_199701_VESSEL94_HaulData.csv AI_199701_VESSEL23_BTdata.csv

SELECT cruise, vessel, haul, serial_number, to_char(date_time, 'MON/DD/YYYYHH24:MI:SS') date_time, depth depth_M, temperature temperatures_C FROM race_edit.rb2_btd WHERE cruise = 199701 AND vessel = 23 ----or 94 ORDER BY haul, date_time

Metadata https://www.fisheries.noaa.gov/foss/f?p=215:29:10937673004490:::::

[NancyRoberson]

EBS SLOPE: 2002, 2004 EBSSlope_200001_VESSEL57_BTdata.csv EBSSlope_200001_VESSEL57_HAULdata.csv

EBSSlope_200201_VESSEL57_BTdata.csv EBSSlope_200201_VESSEL57_HAULdata.csv

BSSlope_200401_VESSEL134_BTdata.csv BSSlope_200401_VESSEL134_HAULdata.csv

SQL SELECT cruise, vessel, haul, serial_number, to_char(date_time, 'MON/DD/YYYYHH24:MI:SS') date_time, depth depth_M, temperature temperatures_C FROM race_edit.rb2_btd WHERE cruise = 200001 ---200201 --- 200401 AND vessel = 57 --57 ----- ----134 ORDER BY haul, date_time

Metadata https://www.fisheries.noaa.gov/foss/f?p=215:29:10937673004490:::::

[NancyRoberson]

EBS Slope: 2016 BSSlope_201601_VESSEL178_BTdata.csv BSSlope_201601_VESSEL178_HAULdata.csv

EBS Slope: 2012

BSSlope_201201_VESSEL94_BTdata.csv BSSlope_201201_VESSEL94_HAULdata.csv

Notes: the haul dates for Haul data and BT data match but the time stamps differ. Jerry.Hoff@noaa.gov is the EBS Slope survey coordinator and can address questions about slope data.

SQL QUERIES

---

BT data

---

SELECT a.haul, to_char(c.date_time,'MON/DD/YYYY HH24:MI:SS') date_time, c.depth depth_m, c.temperature temperature_c FROM race_data.hauls a JOIN race_data.bathythermic_headers b ON a.haul_id = b.haul_id JOIN race_data.bathythermics c ON b.bathythermic_header_id = c.bathythermic_header_id WHERE a.cruise_id = 708 ---2016 EBS Slope Vessel178; 2012 EBS Slope vessel 94 cruise_id = 680 ORDER BY a.haul ASC, c.date_time

---

Haul data

---

SELECT * FROM racebase.haul WHERE cruise = 201601 AND vessel = 178 ORDER BY haul ASC

---

Cruise ID

---

SELECT cruise_id FROM race_data.cruises WHERE cruise = 201201 ---210601 AND vessel_id = 94 ---178

Metadata https://www.fisheries.noaa.gov/foss/f?p=215:29:10937673004490:::::

[NancyRoberson]

EBS SHELF 1999-2004
(There was no stream data found for 2003 and earlier)

EBSShelf_199901_vessel88_BTdata.csv EBSShelf_199901_vessel89_BTdata.csv

EBSShelf_200001_vessel88_BTdata.csv EBSShelf_200001_vessel89_BTdata.csv

EBSShelf_200101_vessel88_BTdata.csv EBSShelf_200101_vessel89_BTdata.csv

EBSShelf_200201_vessel88_BTdata.csv EBSShelf_200201_vessel89_BTdata.csv

EBSShelf_200301_vessel88_BTdata.csv EBSShelf_200301_vessel89_BTdata.csv

EBSShelf_200401_vessel88_BTdata.csv EBSShelf_200401_vessel89_BTdata.csv

SELECT cruise, vessel, haul, serial_number, to_char(date_time, 'MON/DD/YYYY HH24:MI:SS') date_time, depth depth_M, temperature temperature_C FROM race_edit.rb2_btd WHERE cruise = 200201 AND vessel = 88 ORDER BY haul, date_time

Metadata https://www.fisheries.noaa.gov/foss/f?p=215:29:10937673004490:::::

[Ned-Laman-NOAA]

It is worth noting that in the RACE_EDIT.RB2_BTD table is a column called DATUM_CODE and bad data are tagged with a "0" so query should be modified to include constraint DATUM_CODE != 0 or bad data should be culled later.

@NancyRoberson - it's tough for me to tell when these come through my email if they are just for my awareness or if there's something you need me to look at so please tag me in if there's something you want me to do.

[KSiwicke]

Thanks Ned and Nancy. I'll wade through this and try to extract as much as I can, though the multitude of files and different formats and column names is a bit daunting. I'll make this my final request on the historical data front!

[KSiwicke]

I was able to sort most of it out, so you've helped fill in the non-NBS missing data! However, there was still one EBS slope missing: cruise 200202 on vessel 57...you sent 200201 on that vessel which was the AI and I already had.

For your comment: EBS SHELF 1999-2004 (There was no stream data found for 2003 and earlier)...there was temperature depth profiles for all the data provided back to 1999, so not clear what stream means as there is a stream of temperature data at each haul.

Not really important, but I received data for cruise 200001 on vessel 57, which I'm not sure where it is from??? The accompanying haul info turned out to be 200101 and not 200001, and I couldn't locate any info on SQL server.

[Ned-Laman-NOAA]

There are 122735 records in RACE_EDIT.RB2_BTD for the 2022 EBS Slope cruise on Morning Star (Vessel = 57): select count(*) from race_edit.rb2_btd where vessel = 57 and cruise = 200202; The Morning Star 200001 cruise was on the EBS Shelf and the 200101 was in the GOA.

[NancyRoberson]

Hi Kevin,

Thanks for looking over the files and giving feedback and corrections.

In the folder below, you will find the missing 2002 and 2000 EBS Slope BT data. The bad data records were culled (DATUM_CODE != 0) - Thanks for the tip @Ned-Laman-NOAA)

https://drive.google.com/drive/folders/1hfNhfOmIDC7dSigbsz4P3amZWj4jMv_u

I am going to re-run the EBS shelf and other slope BT data so that the bad data are culled. I will put the files in folders: https://drive.google.com/drive/folders/1q1aROWGKprBQ2Kep_TltmQR8Zm5f4N3y

For the NBS BT data, I am looping in @Duane-Stevenson-NOAA

Will that work for you?

Thanks for your suggestions and patience.

[KSiwicke]

Thanks for the updates, and that all sounds good to me.

Very much appreciated as there is clearly a lot of data here!

[Duane-Stevenson-NOAA]

Hi Kevin,

Please see the NBS temperature stream data, and the script used to create the files, in this folder:

https://drive.google.com/drive/folders/1yhJOBYfvoRPpWdEhCdbJPoxaGm_7mUQj?usp=drive_link

Let me know if this fulfills your request.

Duane

On Wed, Mar 13, 2024 at 9:03 AM Nancy Roberson @.***> wrote:

Hi Kevin,

Thanks for looking over the files and giving feedback and corrections.

In the folder below, you will find the missing 2002 and 2000 EBS Slope BT data. The bad data records were culled (DATUM_CODE != 0) - Thanks for the tip @Ned-Laman-NOAA https://github.com/Ned-Laman-NOAA)

https://drive.google.com/drive/folders/1hfNhfOmIDC7dSigbsz4P3amZWj4jMv_u

I am going to re-run the EBS shelf and other slope BT data so that the bad data are culled. I will put the files in folders: https://drive.google.com/drive/folders/1q1aROWGKprBQ2Kep_TltmQR8Zm5f4N3y

For the NBS BT data, I am looping in @Duane-Stevenson-NOAA https://github.com/Duane-Stevenson-NOAA

Will that work for you?

Thanks for your suggestions and patience.

— Reply to this email directly, view it on GitHub https://github.com/afsc-gap-products/data-requests/issues/86#issuecomment-1994795381, or unsubscribe https://github.com/notifications/unsubscribe-auth/ANKDWATNGNWTTTHIV6S62P3YYB2FJAVCNFSM6AAAAABD3DJFZKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOJUG44TKMZYGE . You are receiving this because you were mentioned.Message ID: @.***>

-- Duane Stevenson, Ph.D. Supervisory Fish Biologist Groundfish Assessment Program NMFS, Alaska Fisheries Science Center

[KSiwicke]

Thanks Duane, that fulfills my request for the NBS. I think Nancy is still going to rerun some queries that filter out bad data, so after that I should be done.

[NancyRoberson]

Hi Kevin,

Please see the EBS Shelf, EBS Slope, and AI stream data and their respective queries.

https://drive.google.com/drive/folders/1q1aROWGKprBQ2Kep_TltmQR8Zm5f4N3y

Let me know if this fulfills your request.

Nancy