U.S. Environmental Protection Agency
@usgov.epa_gov_datasets_to_evaluate_the_effects_of_reducing_t_4437d64e
The National Rivers and Stream Assessment 2008-2009 and 2013-2014 diatom datasets and associated site information.
This dataset is associated with the following publication:
Riato, L., J. Stoddard, A. Herlihy, and K. Blocksom. Reduced count size can provide a robust and more efficient diatom assessment of environmental conditions. Journal of Applied Ecology. Blackwell Publishing, Malden, MA, USA, 61(9): 2308-2320, (2024).
Organization: U.S. Environmental Protection Agency
Last updated: 2025-02-12T02:44:34.820890
Tags: diatom-valve-counting, ecological-assessment, genus-taxonomy, methodology-optimization, national-rivers-and-streams-assessment, reduction-in-counted-valves, traits
CREATE TABLE nrsa_2008_2009_diatom_dataset (
"uid" BIGINT,
"achnanthes" BIGINT,
"achnanthes_acares" BIGINT,
"achnanthes_amoena" BIGINT,
"achnanthes_brevipes" BIGINT,
"achnanthes_brevipes_var_intermedia" BIGINT -- Achnanthes Brevipes Var. Intermedia,
"achnanthes_coarctata" BIGINT,
"achnanthes_curtissima" BIGINT,
"achnanthes_exigua_var_elliptica" BIGINT -- Achnanthes Exigua Var. Elliptica,
"achnanthes_gracillima" BIGINT,
"achnanthes_harveyi" BIGINT,
"achnanthes_hintzii" BIGINT,
"achnanthes_imperfecta" BIGINT,
"achnanthes_inflata" BIGINT,
"achnanthes_johncarteri" BIGINT,
"achnanthes_kriegeri" BIGINT,
"achnanthes_lemmermannii" BIGINT,
"achnanthes_minuscula" BIGINT,
"achnanthes_minutissima_var_jackii" BIGINT -- Achnanthes Minutissima Var. Jackii,
"achnanthes_nitidiformis" BIGINT,
"achnanthes_nodosa" BIGINT,
"achnanthes_rechtensis" BIGINT,
"achnanthes_reimeri" BIGINT,
"achnanthes_reversa" BIGINT,
"achnanthes_ricula" BIGINT,
"achnanthes_sp_1_nls_eam" BIGINT -- Achnanthes Sp. 1 NLS EAM,
"achnanthes_sp_1_nrsa_lr" BIGINT -- Achnanthes Sp. 1 NRSA LR,
"achnanthes_stolida" BIGINT,
"achnanthes_subhudsonis_var_kraeuselii" BIGINT -- Achnanthes Subhudsonis Var. Kraeuselii,
"achnanthes_submarina" BIGINT,
"achnanthes_subrostrata_var_appalachiana" BIGINT -- Achnanthes Subrostrata Var. Appalachiana,
"achnanthes_thermalis" BIGINT,
"achnanthidium" BIGINT,
"achnanthidium_affine" BIGINT,
"achnanthidium_alpestre" BIGINT,
"achnanthidium_altergracillima" BIGINT,
"achnanthidium_atomus" BIGINT,
"achnanthidium_caledonicum" BIGINT,
"achnanthidium_catenatum" BIGINT,
"achnanthidium_crassum" BIGINT,
"achnanthidium_deflexum" BIGINT,
"achnanthidium_eutrophilum" BIGINT,
"achnanthidium_exiguum" BIGINT,
"achnanthidium_exiguum_var_constrictum" BIGINT -- Achnanthidium Exiguum Var. Constrictum,
"achnanthidium_exilis" BIGINT,
"achnanthidium_kranzii" BIGINT,
"achnanthidium_latecephalum" BIGINT,
"achnanthidium_macrocephalum" BIGINT,
"achnanthidium_minutissimum" BIGINT,
"achnanthidium_minutissimum_var_gracillima" BIGINT -- Achnanthidium Minutissimum Var. Gracillima,
"achnanthidium_pyrenaicum" BIGINT,
"achnanthidium_rivulare" BIGINT,
"achnanthidium_rosenstockii" BIGINT,
"achnanthidium_saprophilum" BIGINT,
"achnanthidium_semiapertum" BIGINT,
"achnanthidium_sp_4_nawqa_eam" BIGINT -- Achnanthidium Sp. 4 NAWQA EAM,
"achnanthidium_sp_5_nawqa_eam" BIGINT -- Achnanthidium Sp. 5 NAWQA EAM,
"achnanthidium_thienemannii" BIGINT,
"actinella_punctata" BIGINT,
"actinocyclus_normanii" BIGINT,
"actinocyclus_normanii_fo_subsalsus" BIGINT -- Actinocyclus Normanii Fo. Subsalsus,
"adlafia" BIGINT,
"adlafia_bryophila" BIGINT,
"adlafia_minuscula" BIGINT,
"adlafia_minuscula_var_muralis" BIGINT -- Adlafia Minuscula Var. Muralis,
"adlafia_multnomahii" BIGINT,
"adlafia_sp_1_nawqa_eam" BIGINT -- Adlafia Sp. 1 NAWQA EAM,
"adlafia_suchlandtii" BIGINT,
"amphipleura_pellucida" BIGINT,
"amphora" BIGINT,
"amphora_aequalis" BIGINT,
"amphora_archibaldii" BIGINT,
"amphora_coffeaeformis" BIGINT,
"amphora_commutata" BIGINT,
"amphora_copulata" BIGINT,
"amphora_cymbamphora" BIGINT,
"amphora_delicatissima" BIGINT,
"amphora_fogediana" BIGINT,
"amphora_holsatica" BIGINT,
"amphora_inariensis" BIGINT,
"amphora_laevissima" BIGINT,
"amphora_montana" BIGINT,
"amphora_normanii" BIGINT,
"amphora_ocellata" BIGINT,
"amphora_ovalis" BIGINT,
"amphora_pediculus" BIGINT,
"amphora_sabiniana" BIGINT,
"amphora_sp_1_ans_wrc" BIGINT -- Amphora Sp. 1 ANS WRC,
"amphora_sublaevis" BIGINT,
"amphora_thumensis" BIGINT,
"amphora_turgida" BIGINT,
"amphora_veneta" BIGINT,
"aneumastus_apiculatus" BIGINT,
"aneumastus_minor" BIGINT,
"aneumastus_tuscula" BIGINT,
"anomoeoneis_follis" BIGINT,
"anomoeoneis_sphaerophora" BIGINT,
"anomoeoneis_sphaerophora_fo_costata" BIGINT -- Anomoeoneis Sphaerophora Fo. Costata,
"astartiella_bahusiensis" BIGINT,
"asterionella_formosa" BIGINT
);CREATE TABLE nrsa_2013_2014_diatom_dataset (
"uid" BIGINT,
"achnanthes_acares" BIGINT,
"achnanthes_brevipes" BIGINT,
"achnanthes_brevipes_var_intermedia" BIGINT -- Achnanthes.brevipes.var..intermedia,
"achnanthes_cf_trinodis" BIGINT -- Achnanthes.cf..trinodis,
"achnanthes_coarctata" BIGINT,
"achnanthes_curvirostrum" BIGINT,
"achnanthes_elata" BIGINT,
"achnanthes_exigua_var_elliptica" BIGINT -- Achnanthes.exigua.var..elliptica,
"achnanthes_inflata" BIGINT,
"achnanthes_lanceolata_var_lanceolatoides" BIGINT -- Achnanthes.lanceolata.var..lanceolatoides,
"achnanthes_minuscula" BIGINT,
"achnanthes_minutissima_var_robusta" BIGINT -- Achnanthes.minutissima.var..robusta,
"achnanthes_reversa" BIGINT,
"achnanthes_ricula" BIGINT,
"achnanthes_sp_1" BIGINT -- Achnanthes.sp..1..,
"achnanthes_spp" BIGINT -- Achnanthes.spp.,
"achnanthes_stolida" BIGINT,
"achnanthes_subhudsonis_var_kraeuselii" BIGINT -- Achnanthes.subhudsonis.var..kraeuselii,
"achnanthes_submarina" BIGINT,
"achnanthes_subrostrata_var_appalachiana" BIGINT -- Achnanthes.subrostrata.var..appalachiana,
"achnanthes_tenera" BIGINT,
"achnanthes_thermalis" BIGINT,
"achnanthes_trinodis" BIGINT,
"achnanthes_tumescens" BIGINT,
"achnanthidium_affine" BIGINT,
"achnanthidium_alpestre" BIGINT,
"achnanthidium_altergracillima" BIGINT,
"achnanthidium_atomus" BIGINT,
"achnanthidium_caledonicum" BIGINT,
"achnanthidium_catenatum" BIGINT,
"achnanthidium_cf_gracillimum" BIGINT -- Achnanthidium.cf..gracillimum,
"achnanthidium_cf_latecephalum" BIGINT -- Achnanthidium.cf..latecephalum,
"achnanthidium_cf_minutissimum" BIGINT -- Achnanthidium.cf..minutissimum,
"achnanthidium_crassum" BIGINT,
"achnanthidium_deflexum" BIGINT,
"achnanthidium_druartii" BIGINT,
"achnanthidium_duthiei" BIGINT,
"achnanthidium_eutrophilum" BIGINT,
"achnanthidium_exiguum" BIGINT,
"achnanthidium_exiguum_var_constrictum" BIGINT -- Achnanthidium.exiguum.var..constrictum,
"achnanthidium_exiguum_var_heterovalvum" BIGINT -- Achnanthidium.exiguum.var..heterovalvum,
"achnanthidium_exilis" BIGINT,
"achnanthidium_gracillimum" BIGINT,
"achnanthidium_jackii" BIGINT,
"achnanthidium_kranzii" BIGINT,
"achnanthidium_kriegeri" BIGINT,
"achnanthidium_latecephalum" BIGINT,
"achnanthidium_macrocephalum" BIGINT,
"achnanthidium_microcephalum" BIGINT,
"achnanthidium_minutissimum" BIGINT,
"achnanthidium_minutissimum_fo_inconspicuum" BIGINT -- Achnanthidium.minutissimum.fo..inconspicuum,
"achnanthidium_pyrenaicum" BIGINT,
"achnanthidium_reimeri" BIGINT,
"achnanthidium_rivulare" BIGINT,
"achnanthidium_saprophilum" BIGINT,
"achnanthidium_semiapertum" BIGINT,
"achnanthidium_sp" BIGINT -- Achnanthidium.sp.,
"achnanthidium_sp_1" BIGINT -- Achnanthidium.sp..1..,
"achnanthidium_sp_1_nrsa2_jah" BIGINT -- Achnanthidium.sp..1.NRSA2.JAH,
"achnanthidium_sp_5_nawqa_eam" BIGINT -- Achnanthidium.sp..5.NAWQA.EAM,
"achnanthidium_spp" BIGINT -- Achnanthidium.spp.,
"achnanthidium_strictum" BIGINT,
"achnanthidium_subsalsa" BIGINT,
"achnanthidium_thienemannii" BIGINT,
"actinella_punctata" BIGINT,
"actinella_sp_1" BIGINT -- Actinella.sp..1..,
"actinocyclus_normanii" BIGINT,
"actinocyclus_normanii_fo_subsalsus" BIGINT -- Actinocyclus.normanii.fo..subsalsus,
"actinocyclus_subtilis" BIGINT,
"actinoptychus_senarius" BIGINT,
"adlafia_aquaeductae" BIGINT,
"adlafia_bryophila" BIGINT,
"adlafia_minuscula" BIGINT,
"adlafia_minuscula_var_muralis" BIGINT -- Adlafia.minuscula.var..muralis,
"adlafia_multnomahii" BIGINT,
"adlafia_sp" BIGINT -- Adlafia.sp.,
"adlafia_suchlandtii" BIGINT,
"amphipleura_pellucida" BIGINT,
"amphora_aequalis" BIGINT,
"amphora_angusta" BIGINT,
"amphora_archibaldii" BIGINT,
"amphora_birugula" BIGINT,
"amphora_copulata" BIGINT,
"amphora_cymbamphora" BIGINT,
"amphora_delicatissima" BIGINT,
"amphora_exigua" BIGINT,
"amphora_fogediana" BIGINT,
"amphora_inariensis" BIGINT,
"amphora_laevissima" BIGINT,
"amphora_minutissima" BIGINT,
"amphora_ocellata" BIGINT,
"amphora_ovalis" BIGINT,
"amphora_pediculus" BIGINT,
"amphora_sp" BIGINT -- Amphora.sp.,
"amphora_sp_1" BIGINT -- Amphora.sp..1..,
"amphora_sp_1_ans_wrc" BIGINT -- Amphora.sp..1.ANS.WRC,
"amphora_sp_2" BIGINT -- Amphora.sp..2..,
"amphora_spp" BIGINT -- Amphora.spp.,
"amphora_sulcata" BIGINT
);CREATE TABLE site_info_2008_2014 (
"site_id" VARCHAR,
"visit_no" DOUBLE,
"year" DOUBLE,
"ecowsa9_2015" VARCHAR,
"comid" DOUBLE,
"uid" DOUBLE,
"state" VARCHAR,
"valxsite" VARCHAR,
"lat_dd83" DOUBLE,
"lon_dd83" DOUBLE
);Anyone who has the link will be able to view this.