Updating AI or GOA Survey Grid GIS with Trawable/Untrawlable information

[Ned-Laman-NOAA]

Every year during the course of a Gulf of Alaska or Aleutian Islands survey, FPCs collect data at sea on the trawlability of individual stations and record this in an electronic station log. Delegated individuals within the team are tasked post-survey with translating the station logs into trawlability flags of Yes, No, or Unknown assigned to each station evaluated that year. The electronic station logs are currently collected in an MS Access database at sea, evaluated on a record by record basis for assignment of trawlability, and then translated into an Oracle table in preparation for updating the master reference table. This is a cumulative data collection where the master reference accumulates updates survey over survey. Once evaluation and assignment of trawlable status have been validated, depending on the survey, those data are used to update the master referencde tables AI.AIGRID_GIS or GOA.GOAGRID_GIS tables in a table-to-table update in Oracle. Permission to make this update is constrained by password access to the relevant Oracle schema.

This exercise has been completed for the 2023 survey and adding it here as an issue since it is a recurring, annual data request.

Example SQLPlus code for validating assignments and updating the GRID_GIS tables is included:

-- identifying stations successfully trawled in 2023 and ready to be updated in GOA.GOAGRID_GIS select stationid, stratum, trawlable from goa.goagrid_gis where (stationid, stratum) in ( select distinct stationid, stratum from racebase.haul where region = 'GOA' and cruise = 202301 and performance >= 0 and haul_type = 3 ) and trawlable is null / -- 155 on 11/06/2023

select * from aigoa_work_data.goagrid_gis where (stationid, stratum) in ( select distinct stationid, stratum from racebase.haul where region = 'GOA' and cruise = 202301 ); -- 551 records

select * from aigoa_work_data.goagrid_gis where (stationid, stratum) in ( select distinct stationid, stratum from racebase.haul where region = 'GOA' and cruise = 202301 and abundance_haul = 'Y' ); -- 534 records

select goagrid# from goa.goagrid_gis group by goagrid# having count() > 1; select goagrid_id from goa.goagrid_gis group by goagrid_id having count() > 1; -- both of these fields are unique from top to bottom select count(*) from goa.goagrid_gis; -- 23530

-- aigoa_work_data.goagrid_gis is table is a match to above on these criteria select goagrid# from aigoa_work_data.goagrid_gis group by goagrid# having count() > 1; select goagrid_id from aigoa_work_data.goagrid_gis group by goagrid_id having count() > 1; -- both of these fields are unique from top to bottom select count(*) from aigoa_work_data.goagrid_gis; -- 23530

-- given the above, this should return 23530 select count(*) from goa.goagrid_gis a, aigoa_work_data.goagrid_gis b where a.goagrid_id = b.goagrid_id; -- 23530! so it does what it should

-- how many records will be updated? select a.stationid, a.stratum, a.trawlable goa_trawlable, b.trawlable aigoa_work_trawlable from goa.goagrid_gis a, -- the official record aigoa_work_data.goagrid_gis b -- your new record where a.goagrid_id = b.goagrid_id -- this is a unique identifier in each table (like hauljoin) and nvl(a.trawlable,'X') != nvl(b.trawlable,'X'); -- this says compare trawlable field in table a to trawlable field in table b, but where there is a null value substitute and 'X' (this is required because Oracle cannot compare a null value to a not null value and find a difference) -- 245 in this iteration

-- how many stations will change from Y to N? select a.stationid, a.stratum, a.trawlable goa_trawlable, b.trawlable aigoa_work_trawlable from goa.goagrid_gis a, -- the official record aigoa_work_data.goagrid_gis b -- your new record where a.goagrid_id = b.goagrid_id -- this is a unique identifier in each table (like hauljoin) and a.trawlable = 'Y' and b.trawlable = 'N'; -- 4

-- how many stations will change from N to Y? select a.stationid, a.stratum, a.trawlable goa_trawlable, b.trawlable aigoa_work_trawlable from goa.goagrid_gis a, -- the official record aigoa_work_data.goagrid_gis b -- your new record where a.goagrid_id = b.goagrid_id -- this is a unique identifier in each table (like hauljoin) and b.trawlable = 'Y' and a.trawlable = 'N'; -- 0

-- how many stations will change from Not Null to Null? select a.stationid, a.stratum, a.trawlable goa_trawlable, b.trawlable aigoa_work_trawlable from goa.goagrid_gis a, -- the official record aigoa_work_data.goagrid_gis b -- your new record where a.goagrid_id = b.goagrid_id -- this is a unique identifier in each table (like hauljoin) and b.trawlable is null and a.trawlable is not null; -- 6 Yes's changed to Nulls

-- how many stations will change from Null to Not Null? select a.stationid, a.stratum, a.trawlable goa_trawlable, b.trawlable aigoa_work_trawlable from goa.goagrid_gis a, -- the official record aigoa_work_data.goagrid_gis b -- your new record where a.goagrid_id = b.goagrid_id -- this is a unique identifier in each table (like hauljoin) and a.trawlable is null -- unknown and b.trawlable is not null; -- known -- 235 unknowns becoming knowns

-- breakdown by Y and N

-- how many stations will change from Null to Y? select a.stationid, a.stratum, a.trawlable goa_trawlable, b.trawlable aigoa_work_trawlable from goa.goagrid_gis a, -- the official record aigoa_work_data.goagrid_gis b -- your new record where a.goagrid_id = b.goagrid_id -- this is a unique identifier in each table (like hauljoin) and a.trawlable is null -- unknown and b.trawlable ='Y'; -- known -- 152 newly trawlable

-- how many stations will change from Null to N? select a.stationid, a.stratum, a.trawlable goa_trawlable, b.trawlable aigoa_work_trawlable from goa.goagrid_gis a, -- the official record aigoa_work_data.goagrid_gis b -- your new record where a.goagrid_id = b.goagrid_id -- this is a unique identifier in each table (like hauljoin) and a.trawlable is null -- unknown and b.trawlable ='N'; -- known -- 83 newly untrawlable

-- before select nvl(trawlable,'unknown'), count(*) record_count from goa.goagrid_gis group by nvl(trawlable,'unknown') / -- N 1866 -- Y 6304 -- unknown 15360

-- TABLE TO TABLE UPDATE IS HERE update goa.goagrid_gis a set trawlable = (select trawlable from aigoa_work_data.goagrid_gis b where a.goagrid_id = b.goagrid_id and nvl(a.trawlable,'X') != nvl(b.trawlable,'X') )
where (a.goagrid_id) in (select a.goagrid_id from goa.goagrid_gis a, aigoa_work_data.goagrid_gis b where a.goagrid_id = b.goagrid_id and nvl(a.trawlable,'X') != nvl(b.trawlable,'X')); -- 245 records updated on 11/06/2023

-- after/incoming select nvl(trawlable,'unknown'), count(*) record_count from goa.goagrid_gis group by nvl(trawlable,'unknown') / -- N 1953 (+87) -- Y 6446 (+142) -- unknown 15131 (-229)

[MargaretSiple-NOAA]

Hi Ned, just to confirm-- the next time we'll need to do this is after the AI 2024 survey, correct? I am interested to see a) how the Access database gets turned into an Oracle table and b) How our first rounds of searches / logging will go in 2025 with the new design. I imagine there will be a lot of grid cells with funky patterns of untrawlability (due to the mix of new strata + historical untrawlability designations) that we will completely red out in the same year. But we will have to reconcile the old T/UT grid with the info coming in from the new design.

[Ned-Laman-NOAA]

Actually, we're (Paul and I) about to finalize Bethany's efforts to translate the 2022 AI station logs into Oracle in a couple of weeks; training her in how to do this exercise. This is the next step toward planning the 2024 AI survey so that we have up to date T/UT information before we run the station allocation to start building charts and such. Yes. This is going to be complicated in 2025 by the efforts to translate historic T/UT information to the optimized restratification. Still not completely convinced we have all the pieces to that plan in place yet, but we've started talking about it which is good.

[Ned-Laman-NOAA]

There isn't a request here. This was an attempt to simply put a bookmark in for an annual process. There is potential here for improvement and integration in the future since the MS Access db is standalone and unconnected to any of our other digital data collection at sea. An integrated database tool connected with the effort data collection suite on the bridge would be ideal. Answering Megsie's original question: Yes, it's now post-AI-2024 survey and time to sift the electronic station logs from last summer into assignments of T/UT to the AI grid for the 2026 survey.