This document is designed to test and track marine energy data summarization efforts for the marineenrgy.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, which are then ingested by tabulate_dataset_shp_within_aoi in marineenergy/apps/functions.R.
Some notes related to this effort here.
Geometries defined with WGS84 reference system. Geographies of the WGS system are in meters.
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")
# 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)
}Usage: test_new_summary(select_sql, summarize_sql, buffer_nm, intersection, aoi_size)
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))
}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)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")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")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")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)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)”
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)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)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.
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)Native geometry is in 3D (MultiPolygon Z)
Interesting problem when forcing geometry to 2D:
ST_Force2D(geometry) and intersection = Y causes query function to fail.geometry in the select_sql string and replaces it with geometry, st_intersection(...)ST_Force2D(geom)Sparse metadata for this dataset. Not sure what columns mean.
Acres AOI overlaps with maintained dredging channels
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)Count of structures within AOI.
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)Min, max, average annual WEF by depth category
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)Area, district, office, regional zone, percent of source polygon within AOI
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)aor = agency of authorization
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)Should probably create a new table that is the unionization of all the individual tables.
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)