Updating station information in Oracle

[zoyafuso-NOAA]

@Lewis-Barnett-NOAA @MargaretSiple-NOAA @Ned-Laman-NOAA
Prompt: within the AIGOAStationAllocation package, new stations are created based on the new stratification. How do we integrate that into Oracle in a way that still preserves the historical stations while distinguishing them from the new stations.

Terminology (feel free to add to/edit):

1. GOA.GRID - There’s an ArcGIS shapefile maintained by OFIS that is considered the gold standard of our survey area and includes stratum boundaries. When updates are made to this shapefile, they originate with the Gulf-Aleutian group. Copies of this shapefile are downloaded to Oracle (GOA schema…table name? & RACE_DATA used in spatial lookups in GIDES…table name?). When we update the bathymetry of the GOA we will need to maintain versions of the stratum bounds, stratum areas, etc. so that we can reconstruct historic analyses. Lookup tables that are integrated into historic station allocation processes and data summaries:
   GOA.GOA_STRATA where SURVEY = ‘GOA’; GOA.GOAGRID, GOA.GOAGRID_GIS which holds the TRAWLABLE field modified each year after the survey
2. STATIONID: 5 x 5 km cell, in the format XX-YY where XX is the position along the west-east axis and YY is the position along the south-north axis (is this correct?). Colloquially, we call this a grid cell.
3. GOAGRID_ID: There could be > 1 strata within a grid, so a station is the combination of grid and stratum, uniquely ID'd by this column.

A preview of goa.grid_gis as of 10/5/2022 is:

<style> </style>

GOAGRID#	GOAGRID_ID	TRAWLABLE	AREA_KM2	PERIMETER_KM2	STRATUM	STATIONID	CENTER_LAT	CENTER_LONG	NORTH_LAT	SOUTH_LAT	EAST_LONG	WEST_LONG
365	364	N	7.287251	16.43379	33	260-175	60.00576	-148.517	60.03476	59.99705	-148.48	-148.555
412	411	N	17.75851	26.1535	33	260-174	59.9819	-148.521	59.99705	59.94815	-148.48	-148.562
1283	1282	N	25.28308	20.1737	41	284-169	59.7289	-146.537	59.7532	59.7046	-146.495	-146.579
1064	1063	N	21.45248	27.64727	33	248-170	59.75947	-149.512	59.8018	59.7532	-149.471	-149.553
1765	1764	N	11.89898	15.4712	241	332-167	59.6319	-142.586	59.6561	59.6077	-142.561	-142.611
1911	1910	N	18.53746	25.40139	33	241-166	59.56828	-150.091	59.6077	59.55935	-150.049	-150.132

[zoyafuso-NOAA]

An idea we talked about on 10/4 is including an extra field to this table called something like "STRS_DESIGN" filled with maybe the year the stations were created. So for the historical stations, that field would be filled with 1982 (whenever the stations were created). The records for the new stations are then appended (or row binded) to this table and values for that field would be 2023 (and so on when further modifying strata boundaries). When querying stations, you can then use that field to subset for the old design or the 2023 design. You could then have a separate table with columns YEAR AND STRS_DESIGN to denote which years use which STRS design when estimating biomass.

Adding an additional field like this maintains the "long" format of the table which is good practice. The stratum names for the new-design stations could also be preceded with "2023_stratumID" to further denote the change in design.

[MargaretSiple-NOAA]

(from discussion on 10/7/22): Grid cell ID will remain the same; in most cases we will be combining stations. Maybe make second station table with Zack stratification (but don't call it that) and pull out of old table. Centers will be the same, the coordinates will be the same bc the grid cells aren't changing. But need to make a key in new stratification table that gets you back to this table if you need it. Use one column as key for new stratification table. Then you ca convert back and forth.

[Ned-Laman-NOAA]

minor clarification. Stations centers may change or stay the same depending on the relationship between new stratum boundaries and the survey grid. The survey grid lines east/west longitudes and north/south latitudes will remain the same since the grid is not changing.
There may or may not be a need to be able to reversibly translate the old station stratification table into the new, but it could relatively easily be anticipated with a foreign key creating a relationship between the new and the old stratification tables. There also needs to be some thought given to providing distinct and informative names these two stratification tables which will necessarily live together in the GOA schema.

[Ned-Laman-NOAA]

We need to discuss and decide which is the better approach: 1) extend the existing GOAGRID* table with some versioning information as Zack suggested or 2) create an entirely new table for the new stratification.
We also need to determine which set of management one boundaries we are carrying forward and install those in any GOAGRID* lookup for the new stratification. This is because the INPFC areas (old) and NMFS areas (new) did not share the same boundaries and thus circumscribe different areas of the Gulf.
Then I'm pretty sure we need to rebuild the GOA_STRATA table for the new stratification, but there may be a way to extend the existing table. Not sure yet but need to discuss and decide.

[zoyafuso-NOAA]

We seem to be leaning more towards extending the existing GOAGRID table and using some naming convention to rename the strata that makes sense but can also be distinguished from the historical strata names. We can also extend the GOA_STRATA table in a similar way and NA out fields that are not relevant anymore (INPFC area, stratum type, maybe some of those summary ids?). That seems to be my impression of the situation.

[Ned-Laman-NOAA]

I agree that this is the direction we're all leaning. A "design year" field or some commonly shared identifier among lookup and product tables indicating which era of survey grid stratification is in use

[Ned-Laman-NOAA]

...and another thing...
We will need to get with Heather before year's end to make sure that all of our spatial infrastructure gets updated in RACE_DATA for GIDES where spatial lookups are part of the data QA/QC process.

[Ned-Laman-NOAA]

I just sent an email to Heather in this regard and cc'd both Zack and Megsie

[Ned-Laman-NOAA]

one other thing that just struck me - untrawlable stations.
The untrawlable designations cannot all follow the changes in bathymetry and stratum boundaries because, if a physical location was designated untrawlable, it will need to remain untrawlable whether or not the stratum bounds around it change. Right now those untrawlable designations are sited based on the combination of stationid and bathymetry. There is access to the GridIDs coming out of OFIS shapefiles too so we have unique IDs that could help in this process.

[zoyafuso-NOAA]

My proposal from yesterday's meeting:

• Create new stations based on the 5x5 km grid and bathymetry rasters
• overlay polygon of untrawlable areas and calculate trawlable area in the new stations (total area of the station - any untrawlable area that overlaps with the new station). I am currently using goagrid2019_landuntrawlsndmn.shp and there is probably a more precise version of this that includes 2021 data.
• When randomly drawing stations within a stratum, instead of using the total area of the station as the sampling weight, use instead the trawlable area in the station as the sampling weight. Excluding the untrawlable areas introduces bias but this is a way that we don't completely exclude a station because it contains a portion of historically untrawlable area.
• Continue to exclude stations with < 5 km^2 of trawlable area in the sampling process as done historically.

[Ned-Laman-NOAA]

point by point reply below

1 - ...new stations...what do we do about stations in the survey grid framework that aren't supported by the bathymetry raster (e.g., shelf edge in the Shumagins)?
2 - overlay...there are new untrawlable data in the GOA.GOAGRID_GIS table updated since the 2021 survey. these should be converted into a new shapefile that replaces the 2019 one you've been working with
3 - When randomly...I still need to develop more clarity around how we handle trawlable designations in grid cells that were based on depth strata that get changed when we redraw bathymetry...let's plan to sit down with Zimm on this once we have an R version of the grid with new bathymetry (translated to a shapefile so Mark can put it into ArcGIS) and have to start making decisions about trawlable designations
4 - Continue to...agree!