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.