spatial_summaries.Rmd

---
title: "Spatial Summaries"
output: 
  html_document:
    toc: true
    number_sections: false
editor_options: 
  chunk_output_type: console
---


```{r setup, include=F}
if (!require(librarian)){
  remotes::install_github("DesiQuintans/librarian")
  library(librarian)
}
shelf(
  # database
  DBI, RPostgres,
  # spatial
  ggmap, leaflet,
  r-spatial/mapview, # https://github.com/r-spatial/mapview/issues/324
  sf, sp,
  # scrape
  # rvest, 
  # tidyverse
  dplyr, purrr, readr, tibble, tidyr,
  # someday
  # googledrive, zeallot,
  # report
  DT, gt, htmltools, htmlwidgets, kableExtra, knitr, markdown, rmarkdown, shiny, webshot,
  # utility
  fs, glue, here, png, scales, stringr, urltools
)
here <- here::here

pass <- readLines("../password_mhk-env.us")
con = dbConnect(
  RPostgres::Postgres(),
  dbname   = "gis",
  host     = "marineenergy.app",
  port     = 5432,
  user     = "admin",
  password = pass
)

knitr::opts_chunk$set(echo = T, warning = F, message = F) #, connection = "db")
```

# About

This document is designed to test and track marine energy data summarization efforts for the [marineenrgy.app](https://marineenergy.app/).
Raw data sources have been loaded into a PostgreSQL database and our task is to interpret and summarize the *important* information.

Once tested, data queries and metadata get compiled into a [master table](https://docs.google.com/spreadsheets/d/1MMVqPr39R5gAyZdY2iJIkkIdYqgEBJYQeGqDk1z-RKQ/edit#gid=936111013), which are then ingested by `tabulate_dataset_shp_within_aoi` in [marineenergy/apps/functions.R](https://github.com/marineenergy/apps/blob/c98a3bacfa06370e823863f52b6633036cd228dc/functions.R).

Some notes related to this effort [here](https://docs.google.com/document/d/109KwZ2PsKkD_ohRZI5F3IkIEOmYKXLl-qVf_ZxHIoyo).

Geometries defined with WGS84 reference system. Geographies of the WGS system are in meters.

---


# Data Summary Testing Functions

## Existing Data Summaries

Usage: `test_tabulate_dataset_shp_within_aoi(<dataset code>, <aoi size>, <optional: coast>)`

Example: `test_tabulate_dataset_shp_within_aoi("ocs-lease-blk", "S", "W")`

or: `test_tabulate_dataset_shp_within_aoi("ocs-lease-blk", "L")`

```{r test_existing_sql, class.source='fold-show'}
# source("functions.R")

test_tabulate_dataset_shp_within_aoi <- function(ds_code, aoi_size, coast){
  # AOI covering most of North America
  if (aoi_size == "L") {
    aoi_wkt = "POLYGON ((
      -149.765625 9.795677582829743, 
      -37.96875 9.795677582829743, 
      -37.96875 56.9449741808516, 
      -149.765625 56.9449741808516, 
      -149.765625 9.795677582829743))"
  # AOI covering the United States
  } else if (aoi_size == "M") {
    aoi_wkt = "POLYGON ((
      -131.484375 26.43122806450644, 
      -66.796875 26.43122806450644, 
      -66.796875 49.83798245308484, 
      -131.484375 49.83798245308484, 
      -131.484375 26.43122806450644))"
  # AOI covering the U.S. east or west coast
  } else {
    if (missing(coast)) {
      aoi_wkt = "POLYGON ((
        -83.3203125 30.90222470517144, 
        -80.33203125 25.64152637306577, 
        -61.52343749999999 44.96479793033101, 
        -72.94921875 45.213003555993964, 
        -83.3203125 30.90222470517144))"
    } else {
      if (coast == "W") {
        aoi_wkt = "POLYGON ((
          -128.84765625 32.99023555965106, 
          -118.30078125 32.99023555965106, 
          -118.30078125 45.460130637921004, 
          -128.84765625 45.460130637921004, 
          -128.84765625 32.99023555965106))"
      } else {
        aoi_wkt = "POLYGON ((
          -83.3203125 30.90222470517144, 
          -80.33203125 25.64152637306577, 
          -61.52343749999999 44.96479793033101, 
          -72.94921875 45.213003555993964, 
          -83.3203125 30.90222470517144))"
      }
    }
  }
  
  dataset_code = ds_code; aoi_wkt = aoi_wkt; output = "kable"
  res <- tabulate_dataset_shp_within_aoi(ds_code, aoi_wkt)
  return(res)
}
```


## New Data Summaries

Usage: `test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size)`
```{r test_new_sql, class.source='fold-show'}
test_new_summary <- function(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast) {
  if (aoi_size == "L") {
    aoi_wkt = "POLYGON ((
      -149.765625 9.795677582829743, 
      -37.96875 9.795677582829743, 
      -37.96875 56.9449741808516, 
      -149.765625 56.9449741808516, 
      -149.765625 9.795677582829743))"
  # AOI covering the United States
  } else if (aoi_size == "M") {
    aoi_wkt = "POLYGON ((
      -131.484375 26.43122806450644, 
      -66.796875 26.43122806450644, 
      -66.796875 49.83798245308484, 
      -131.484375 49.83798245308484, 
      -131.484375 26.43122806450644))"
  # AOI covering the U.S. east or west coast
  } else {
    if (missing(coast)) {
      aoi_wkt = "POLYGON ((
        -83.3203125 30.90222470517144,
        -80.33203125 25.64152637306577,
        -61.52343749999999 44.96479793033101,
        -72.94921875 45.213003555993964,
        -83.3203125 30.90222470517144))"
    } else {
      if (coast == "W") {
        aoi_wkt = "POLYGON ((
          -128.84765625 32.99023555965106, 
          -118.30078125 32.99023555965106, 
          -118.30078125 45.460130637921004, 
          -128.84765625 45.460130637921004, 
          -128.84765625 32.99023555965106))"
      } else {
        aoi_wkt = "POLYGON ((
          -83.3203125 30.90222470517144, 
          -80.33203125 25.64152637306577, 
          -61.52343749999999 44.96479793033101, 
          -72.94921875 45.213003555993964, 
          -83.3203125 30.90222470517144))"
      }
    }
  }
  
  
  if (length(aoi_wkt) > 1){
    aoi_wkts <- glue("'SRID=4326;{aoi_wkt}'::geometry")
    aoi_sql  <- glue("ST_COLLECT(\n{paste(aoi_wkts, collapse=',\n')})") # Is this recreating the ST_COLLECT statement
  } else {
    aoi_sql <- glue("'SRID=4326;{aoi_wkt}'::geometry")
  }
  
  # Different set of queries required for data sets that do or
  #   do not need area weighted statistics. 
  # This is controlled by the "st_intersection" field in the "datasets" table
  if (intersection){
    # Area weighted statistics ARE required
    ixn_sql <- str_replace(select_sql, 'geometry', 'geometry, st_intersection(ds.geometry, buf_aoi.geom) as ixn ')
    
    # Check if there is a sql summary statement for this dataset
    if (!is.na(summarize_sql)){
      x_df <- dbGetQuery(
        con,
        glue("
          with
            buf_aoi as (
              select ST_BUFFER({aoi_sql}, {buffer_nm} * 1852) as geom),
            tmp_aoi as (
              {ixn_sql} as ds, buf_aoi
              where st_intersects(ds.geometry, buf_aoi.geom))
            {summarize_sql}
          "))
    } else {
      x_sf <- st_read(
        con, 
        glue("
          with
            buf_aoi as (
              select ST_BUFFER({aoi_sql}, {buffer_nm} * 1852) as geom)
            {ixn_sql} as ds, buf_aoi
            where st_intersects(ds.geometry, buf_aoi.geom)
          "))
      x_df <- st_drop_geometry(x_sf)
    }
    
  } else {
    # Area weighted statistics NOT required
    if (!is.na(summarize_sql)){
      x_df <- dbGetQuery(
        con, glue("
          with 
            buf_aoi as (
              select ST_BUFFER({aoi_sql}, {buffer_nm} * 1852) as geom ),
            tmp_aoi as (
              {select_sql} as ds
              inner join buf_aoi on st_intersects(ds.geometry, buf_aoi.geom) )
           {summarize_sql}
           "))
    } else {
      x_sf <- st_read(
        con, query = glue("
          with 
            buf_aoi as (
              select ST_BUFFER({aoi_sql}, {buffer_nm} * 1852) as geom)
            {select_sql} as ds
            inner join buf_aoi on st_intersects(ds.geometry, buf_aoi.geom )
            "))
      x_df <- st_drop_geometry(x_sf)
    }
  }
  
  return(datatable(x_df, rownames=F))
}
```

---

# Data Summaries

## Template
```{r template, eval=F, class.source='fold-show'}
select_sql <- glue('')

summarize_sql <- glue("")

intersection = F
buffer_nm = 0
aoi_size = "M"
coast = "E"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```

---

## Selected Pipelines
```{r pipelines, class.source='fold-show'}
select_sql <- glue('
  select
    fed_seg_len_ft,
    status_code,
    outer_diameter,
    product_code,
    aprv_code,
    operator,
    geometry
  from (
    select
      seg_length as fed_seg_len_ft,
      status_cod as status_code,
      ppl_size_c as outer_diameter,
      prod_code as product_code,
      aprv_code,
      sde_compan as operator,
      geometry
    from shp_ppl_arcs
    UNION
    select
      seg_length as fed_seg_len_ft,
      status_cod as status_code,
      ppl_size_c as outer_diameter,
      prod_code as product_code,
      aprv_code,
      sde_compan as operator,
      geometry
    from "shp_BOEM_Pacific_Pipelines_2011-08_nad83")')

summarize_sql <- glue("
  select
    status_code as \"Status Code\",
    product_code as \"Product Code\",
    operator as \"Operator\",
    to_char(round(sum(st_length(ixn::geography)) * 3.281), 'FM999,999,999') as \"Segment Length (ft)\"
  from 
    tmp_aoi
  group by
    status_code, product_code, operator
  order by
    status_code, product_code, operator")

intersection = T
buffer_nm = 0

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, "M")
```


## Outer Continental Shelf Proposed Final Program Areas 2017-2022
```{r ocs-programs, class.source='fold-show'}
select_sql <- glue('
  select
  	notes, geometry
  from (
  	select 
  	  notes, geometry
  	from 
  	  "shp_GOMR_Proposed_FInal_Program_Area"
  	UNION
  	select 
  	  notes, geometry 
  	from 
      "shp_Alaska_Region_2017-2022_Proposed_Final_Program_Area")')

summarize_sql <- glue("
  select
    replace(notes, 'FInal', 'Final') as \"Note\",
    round(100 * sum(st_area(ixn::geography) / st_area(geometry::geography))) || ' %' as \"AOI Overlap\"
  from
    tmp_aoi
  group by
    notes
  order by
    notes
")

intersection = T
buffer_nm = 0

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, "M")
```


## Federal Outer Continental Shelf (OCS) Sand and Gravel Borrow Areas (Lease Areas)
```{r fed-ocs-lease, class.source='fold-show'}
select_sql <- glue('
  select
    state,
    leaseno,
    projectid,
    projectcou,
    totalsanda,
    fiscalyear,
    effectived,
    expiration,
    originalvo,
    borrowarea,
    amendmentd,
    amendmentn,
    additional,
    acres,
    leasestatu,
    geometry
  from "shp_Leases_200203"
')

summarize_sql <- glue("
  select
    state as \"State\",
    leaseno as \"Lease\",
    projectid as \"Project\",
    leasestatu as \"Status\",
    expiration as \"Expiration\",
    totalsanda as \"Allocated Sand Volume (yd^3)\"
  from
    tmp_aoi
  order by
    state, leasestatu, expiration, leaseno, projectid
")

intersection = F
buffer_nm = 0

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, "M")
```


## Coastal Barrier Resources System
```{r cbrs, class.source='fold-show'}
select_sql <- glue('
  select
    unit,
    name,
    unit_type,
    fast_acres,
    wet_acres,
    shore_mile,
    geometry
  from "shp_CBRS_Polygons_03122019"
')

summarize_sql <- glue("
  select
    unit as \"CBRS Unit\",
    name as \"Unit Name\",
    unit_type as \"Unit Type\",
    round(fast_acres * sum(st_area(ixn::geography) / st_area(geometry::geography))) as \"Upland Area (acres)\",
    round(wet_acres * sum(st_area(ixn::geography) / st_area(geometry::geography))) as \"Wetland Area (acres)\"
  from
    tmp_aoi
  group by
    unit, name, unit_type, fast_acres, wet_acres
  order by
    unit, name, unit_type
")

intersection = T
buffer_nm = 0.00329158
aoi_size = "S"
coast = "E"
  
test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```


## National Seafloor Geology (GLORIA)

Overkill?

> to_char(round(sum(st_area(geometry::geography) * (st_area(ixn::geography) / st_area(geometry::geography))) / 1000), 'FM999,999,999,999') as "Area (km^2)"
    
```{r gloria, eval=T, class.source='fold-show'}
select_sql <- glue('
  select
    unit, 
    description, 
    geometry 
  from (
    select area, unit, null as description, geometry from shp_interp
    union
    select area, unit, descriptio as description, geometry from shp_west
    union
    select st_area(geometry) as area, tunits as unit, null as description, geometry from shp_geo_poly
    union
    select area, geoabrv as unit, descriptio as description, geometry from shp_geology
  )
')

summarize_sql <- glue("
  select
    unit as \"Unit\",
    case when description is null then 'No description' when description = 'No Data' then 'No description' else description end as \"Description\"
  from 
    tmp_aoi
  where
    unit is not null and unit != 'nodat'
  group by
    unit, description
  order by
    unit, description
")

intersection = F
buffer_nm = 0
aoi_size = "M"
coast = "E"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```


## EFH Areas Protected from Fishing
```{r nationwide-efha, eval=T, class.source='fold-show'}
select_sql <- glue('
  select 
  	fmc_report,
  	sitename_l,
  	instatewat,
  	lifestage,
  	lttdt_link,
  	acres,
  	geometry
  from shp_nationwide_efha
')

summarize_sql <- glue("
  select
    fmc_report as \"FMC Region\",
    sitename_l as \"Site\",
    case when instatewat = 'Y' then 'Yes' else 'No' end as \"State Waters\",
    lifestage as \"Lifestage\",
    to_char(round(sum(st_area(geometry::geography) * (st_area(ixn::geography) / st_area(geometry::geography))) / 4047), 'FM999,999,999,999') as \"Acres\"
  from
    tmp_aoi
  group by
    fmc_report, sitename_l, instatewat, lifestage, lttdt_link
  order by
    fmc_report, sitename_l, instatewat, lifestage, lttdt_link
")

intersection = T
buffer_nm = 0
aoi_size = "S"
coast = "W"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```


## Ocean Wave Resource Potential

wef = wave energy flux (kilowatts per meter of wave crest)

depth = meters

Could go about summarizing this in a few different ways. Lots of points.

```{r wave-power, eval=F, class.source='fold-show'}
select_sql <- glue('
  select
    depth_wef,
    ann_wef,
    geometry
  from
    shp_wave_power_density
')

summarize_sql <- glue("
  select
    depth_wef,
    avg(ann_wef) as avg_ann_wef
  from
    tmp_aoi
  group by
    depth_wef
  order by
    depth_wef
")

intersection = F
buffer_nm = 0
aoi_size = "S"
coast = "E"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```


## Coastal Maintained Channels

Native geometry is in 3D (MultiPolygon Z)

Interesting problem when forcing geometry to 2D:

- Using `ST_Force2D(geometry)` and `intersection = Y` causes query function to fail.
- Query function looks for `geometry` in the `select_sql` string and replaces it with `geometry, st_intersection(...)`
- Workaround was to use `ST_Force2D(geom)`

Sparse metadata for this dataset. Not sure what columns mean.

> Acres AOI overlaps with maintained dredging channels

```{r coastal-channels, eval=T, class.source='fold-show'}
select_sql <- glue('
  select
  	objnam,
  	themelayer,
  	inform,
  	drval1,
  	quasou,
  	quasou_txt,
  	sordat,
  	fairway,
  	dsnm,
  	dataaccess,
  	datatype,
  	st_force2d(geom) as geometry
  from shp_maintainedchannels
')

summarize_sql <- glue("
  select
    fairway as \"Fairway\",
    round(sum(st_area(geometry::geography) * (st_area(ixn::geography) / st_area(geometry::geography))) / 4047) as \"AOI Acres\"
  from 
    tmp_aoi
  group by
    fairway
  order by
    fairway
")

intersection = T
buffer_nm = 0
aoi_size = "S"
coast = "E"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```


## Drilling Platforms

> Count of structures within AOI.

```{r drill-platforms, eval=T, class.source='fold-show'}
select_sql <- glue('
  select
    structure, 
    geometry
  from (
    select platform_n as structure, geometry from shp_pc_plat
    union
    select complex_id || \' \' || str_name as structure, geometry from shp_platform
  )
')

summarize_sql <- glue('
  select
    count(*) as "# Structures"
  from 
    tmp_aoi
')

intersection = F
buffer_nm = 0
aoi_size = "S"
coast = "W"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```


## Ocean Wave Resource Potential

> Min, max, average annual WEF by depth category

```{r wave-potential, eval=T, class.source='fold-show'}
select_sql <- glue('
  select
    case
      when depth_wef::double precision >= 0 and depth_wef::double precision < 1000 then \'0000-0999\'::text
      when depth_wef::double precision >= 1000 and depth_wef::double precision < 2000 then \'1000-1999\'::text
      when depth_wef::double precision >= 2000 and depth_wef::double precision < 3000 then \'2000-2999\'::text
      when depth_wef::double precision >= 3000 and depth_wef::double precision < 4000 then \'3000-3999\'::text
      when depth_wef::double precision >= 4000 and depth_wef::double precision < 5000 then \'4000-4999\'::text
      when depth_wef::double precision >= 5000 then \'>5000\'::text end as depth_wef_cat,
    ann_wef,
    geometry
  from (
    select
      depth_wef,
      ann_wef,
      geometry
    from
      shp_wave_power_density
  )
')

summarize_sql <- glue("
  select
    depth_wef_cat as \"Depth Category\",
    min(ann_wef) as \"Min Annual WEF\",
    max(ann_wef) as \"Max Annual WEF\",
    avg(ann_wef) as \"Avg Annual WEF\"
  from
    tmp_aoi
  group by
    depth_wef_cat
  order by
    depth_wef_cat
")

intersection = F
buffer_nm = 0
aoi_size = "S"
coast = "W"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```


## US Coast Guard Districts

> Area, district, office, regional zone, percent of source polygon within AOI

```{r uscg-districts, eval=T, class.source='fold-show'}
select_sql <- glue('
  select
    districtna,
    office,
    areaname,
    zones,
    shape_area,
    geometry
  from
    "shp_U.S._Coast_Guard_Districts"
')

summarize_sql <- glue("
  select
    areaname as \"Area\",
    districtna as \"District\",
    office as \"Office\",
    replace(replace(zones, 'Cotp Zones Consist of ', ''), 'Cotp Zones consist of ', '') as \"Zones\",
    round(100 * sum(st_area(ixn::geography) / st_area(geometry::geography))) || ' %' as \"AOI Intersection\"
  from
    tmp_aoi
  group by
    areaname, districtna, office, zones, shape_area
  order by
    districtna
")

intersection = T
buffer_nm = 0
aoi_size = "S"
coast = "E"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```


## US/Canada Land Boundary

aor = agency of authorization

```{r land-boundary, eval=T, class.source='fold-show'}
select_sql <- glue('
  select
    region,
    feat_type,
    legal_auth,
    aor,
    supp_info,
    geometry
  from
    "shp_USMaritimeLimitsNBoundaries"
')

summarize_sql <- glue("
  select
    region as \"Region\",
    feat_type as \"Boundary Type\",
    legal_auth as \"Legal Authority\"
  from
    tmp_aoi
  group by
    region, feat_type, legal_auth
  order by
    region, feat_type, legal_auth
")

intersection = F
buffer_nm = 0
aoi_size = "L"
coast = "E"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size, coast)
```


## Individual Fish Species

> Should probably create a new table that is the unionization of all the individual tables.

```{r fish-species, eval=T, class.source='fold-show'}
select_sql <- glue('
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP101_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP102_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP103_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP104_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP105_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP106_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP107_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP108_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP109_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP116_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP12_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP121_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP13_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP131_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP135_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP136_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP139_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP141_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP142_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP143_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP145_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP146_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP149_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP15_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP151_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP155_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP156_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP163_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP164_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP171_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP172_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP176_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP177_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP18_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP181_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP19_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP192_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP193_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP196_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP197_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP21_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP22_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP23_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP24_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP25_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP26_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP27_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP28_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP29_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP301_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP306_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP31_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP312_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP318_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP32_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP33_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP34_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP35_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP36_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP360_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP37_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP375_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP380_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP384_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP4_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP401_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP43_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP44_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP502_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP503_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP640_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP651_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP69_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP72_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP73_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP74_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP75_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP76_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP77_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP78_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_FALL_SP84_2010TO2016"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP101_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP102_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP103_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP104_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP105_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP106_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP107_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP108_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP109_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP116_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP12_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP121_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP13_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP131_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP135_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP136_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP139_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP141_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP142_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP143_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP145_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP146_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP149_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP15_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP151_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP155_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP156_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP163_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP164_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP171_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP172_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP176_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP177_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP18_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP181_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP19_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP192_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP193_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP196_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP197_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP21_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP22_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP23_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP24_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP25_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP26_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP27_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP28_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP29_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP301_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP306_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP31_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP312_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP318_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP32_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP33_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP34_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP35_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP36_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP360_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP37_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP375_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP380_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP384_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP4_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP401_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP43_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP44_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP502_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP503_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP640_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP651_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP69_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP72_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP73_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP74_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP75_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP76_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP77_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP78_2010TO2017"
union
select idnum, cruise, stratum, station, year, season, avgdepth, spp_name, sci_name, biomass, geometry from "shp_SPRING_SP84_2010TO2017"
')

summarize_sql <- glue("
  select
    year as \"Year\", 
    season as \"Season\", 
    spp_name as \"Species Name\", 
    sci_name as \"Scientific Name\", 
    round(max(biomass)::numeric, 2) as \"Biomass Max\", 
    round(avg(biomass)::numeric, 2) as \"Avg Biomass\"
  from
    tmp_aoi
  group by
    year, season, spp_name, sci_name
  order by
    year, season, sci_name
")

intersection = F
buffer_nm = 0
aoi_size = "S"

test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size)
```