/* CALCULATIONS FOR TOTAL SOIL VALUES */ UPDATE soilbins2006 SET total_percentn = (heavy_percentn * percent_heavysmall) + (light_percentn * percent_light) UPDATE soilbins2006 SET total_percentc = (heavy_percentc * percent_heavysmall) + (light_percentc * percent_light) UPDATE soilbins2006 SET total_del15n = ((heavy_del15n * percent_heavysmall) + (light_del15n * percent_light)) * (1/(percent_heavysmall + percent_light) UPDATE soilbins2006 SET total_del13c = ((heavy_percentc * percent_heavysmall) + (light_percentc * percent_light)) * (1/(percent_heavysmall + percent_light) UPDATE soilbins2006 SET total_C_to_N = total_percentc / total_percentn /* CALCULATIONS FOR BURIED BAGS 2007 */ UPDATE buriedbag2007 SET moisture_initial = NULL ; UPDATE buriedbag2007 SET moisture_initial = 1 - ((tin_drysoil_i - tin_i) / (tin_soil_i - tin_i)) ; UPDATE buriedbag2007 SET moisture_final = NULL ; UPDATE buriedbag2007 SET moisture_final = 1 - ((tin_drysoil_f - tin_f) / (tin_soil_f - tin_f)) ; UPDATE buriedbag2007 SET soil_NH4_i = NULL ; UPDATE buriedbag2007 SET soil_NH4_i = NH4_initial / ((flask_soil_i - flask_i) * (1 - moisture_initial)) * 40 ; /* µg N g-1 soil*/ UPDATE buriedbag2007 SET soil_NH4_f = NULL ; UPDATE buriedbag2007 SET soil_NH4_f = NH4_final / ((flask_soil_f - flask_f) * (1 - moisture_final)) * 40 ; /* µg N g-1 soil*/ UPDATE buriedbag2007 SET soil_NO3_i = NULL ; UPDATE buriedbag2007 SET soil_NO3_i = NO3_initial / ((flask_soil_i - flask_i) * (1 - moisture_initial)) * 40 ; ; /* µg N g-1 soil*/ UPDATE buriedbag2007 SET soil_NO3_f = NULL ; UPDATE buriedbag2007 SET soil_NO3_f = NO3_final / ((flask_soil_f - flask_f) * (1 - moisture_final)) * 40 ; /* µg N g-1 soil*/ UPDATE buriedbag2007 SET buriedbag2007.IN = NULL ; UPDATE buriedbag2007 SET buriedbag2007.IN = soil_NH4_i + soil_NO3_i ; /* µg N g-1 soil*/ UPDATE buriedbag2007 SET net_min = NULL ; UPDATE buriedbag2007 SET net_min = ((soil_NH4_f + soil_NO3_f) - (soil_NH4_i + soil_NO3_i)) / 14 ; /* µg N g-1 soil day-1*/ UPDATE buriedbag2007 SET net_nit = NULL ; UPDATE buriedbag2007 SET net_nit = (soil_NO3_f - soil_NO3_i) / 14 ; ; /* µg N g-1 soil day-1*/ UPDATE buriedbag2007 SET soil_NH4_i = 0 WHERE soil_NH4_i < 0 ; /* CALCULATIONS FOR CORE LEVEL C AND N PERCENTAGES */ UPDATE soilbins2006 SET heavy_pc_core = heavy_percentc * percent_heavysmall ; UPDATE soilbins2006 SET light_pc_core = light_percentc * percent_light ; UPDATE soilbins2006 SET heavy_pn_core = heavy_percentn * percent_heavysmall ; UPDATE soilbins2006 SET light_pn_core = light_percentn * percent_light ; /* CALCULATIONS FOR RESINS */ UPDATE resins2007 SET NH4a = (NH4a_raw - NH4_blank) * 40 ; UPDATE resins2007 SET NO3a = (NO3a_raw - NO3_blank) * 40 ; UPDATE resins2007 SET NH4b = (NH4b_raw - NH4_blank) * 40 ; UPDATE resins2007 SET NO3b = (NO3b_raw - NO3_blank) * 40 ; UPDATE resins2007 SET NH4a = 0 WHERE NH4a < 0; UPDATE resins2007 SET NO3a = 0 WHERE NO3a < 0; UPDATE resins2007 SET NH4b = 0 WHERE NH4b < 0; UPDATE resins2007 SET NO3b = 0 WHERE NO3b < 0; UPDATE resins2007 SET NH4a_cor = (NH4a_raw - NH4_blank)/resin_mass_a - NH4_blank_cor ; UPDATE resins2007 SET NO3a_cor = (NO3a_raw - NO3_blank)/resin_mass_a - NO3_blank_cor ; UPDATE resins2007 SET NH4b_cor = (NH4b_raw - NH4_blank)/resin_mass_b - NH4_blank_cor ; UPDATE resins2007 SET NO3b_cor = (NO3b_raw - NO3_blank)/resin_mass_b - NO3_blank_cor ; UPDATE resins2007 SET mean_NH4 = (NH4a + NH4b) / 2 ; UPDATE resins2007 SET mean_NO3 = (NO3a + NO3b) / 2 ; UPDATE resins2007 SET mean_NH4 = NH4a WHERE mean_NH4 IS NULL; UPDATE resins2007 SET mean_NH4 = NH4b WHERE mean_NH4 IS NULL; UPDATE resins2007 SET mean_NO3 = NO3a WHERE mean_NO3 IS NULL; UPDATE resins2007 SET mean_NO3 = NO3b WHERE mean_NO3 IS NULL; UPDATE resins2007 SET mean_NH4_cor = (NH4a_cor + NH4b_cor) / 2 ; UPDATE resins2007 SET mean_NO3_cor = (NO3a_cor + NO3b_cor) / 2 ; UPDATE resins2007 SET resins2007.IN = NULL ; UPDATE resins2007 SET resins2007.IN = mean_NH4 + mean_NO3 ; /* µg N g-1 soil*/ /* CALCULATIONS FOR BURIED BAGS 2008 */ UPDATE buriedbag2008 SET moisture_i = NULL ; UPDATE buriedbag2008 SET moisture_i = 1 - ((tin_drysoil_i - tin_mass_i) / (tin_soil_i - tin_mass_i)) ; UPDATE buriedbag2008 SET moisture_f = NULL ; UPDATE buriedbag2008 SET moisture_f = 1 - ((tin_drysoil_f - tin_mass_f) / (tin_soil_f - tin_mass_f)) ; UPDATE buriedbag2008 SET NH4i = NULL ; UPDATE buriedbag2008 SET NH4i = NH4i_raw / ((flask_soil_i - flask_mass_i) * (1 - moisture_i)) * 40 ; /* µg N g-1 soil*/ UPDATE buriedbag2008 SET NH4f = NULL ; UPDATE buriedbag2008 SET NH4f = NH4f_raw / ((flask_soil_f - flask_mass_f) * (1 - moisture_f)) * 40 ; /* µg N g-1 soil*/ UPDATE buriedbag2008 SET NO3i = NULL ; UPDATE buriedbag2008 SET NO3i = NO3i_raw / ((flask_soil_i - flask_mass_i) * (1 - moisture_i)) * 40 ; ; /* µg N g-1 soil*/ UPDATE buriedbag2008 SET NO3f = NULL ; UPDATE buriedbag2008 SET NO3f = NO3f_raw / ((flask_soil_f - flask_f) * (1 - moisture_f)) * 40 ; /* µg N g-1 soil*/ UPDATE buriedbag2008 SET buriedbag2008.IN = NULL ; UPDATE buriedbag2008 SET buriedbag2008.IN = NH4i + NO3i ; /* µg N g-1 soil*/ UPDATE buriedbag2008 SET net_min = NULL ; UPDATE buriedbag2008 SET net_min = ((NH4f + NO3f) - (NH4i + NO3i)) / 14 ; /* µg N g-1 soil day-1*/ UPDATE buriedbag2008 SET net_nit = NULL ; UPDATE buriedbag2008 SET net_nit = (NO3f - NO3i) / 14 ; ; /* µg N g-1 soil day-1*/ /* CALCULATIONS FOR POOL DILUTIONS 2008 */ UPDATE pooldilutions2008 SET temp_i = (temp_i - 32) * (5/9) WHERE temp_i > 30 ; UPDATE pooldilutions2008 SET temp_f = (temp_f - 32) * (5/9) WHERE temp_f > 30 ; UPDATE pooldilutions2008 SET moisture = NULL ; UPDATE pooldilutions2008 SET moisture = 1 - ((tin_drysoil - tin_mass)/(tin_soil - tin_mass)) ; UPDATE pooldilutions2008 SET NO3i = NULL ; UPDATE pooldilutions2008 SET NO3i = NO3i_raw / ((cup_soil_initial - cup_initial) * (1 - moisture)) * KCl_ml ; /* µg N g-1 soil*/ UPDATE pooldilutions2008 SET NH4i = NULL ; UPDATE pooldilutions2008 SET NH4i = NH4i_raw / ((cup_soil_initial - cup_initial) * (1 - moisture)) * KCl_ml ; /* µg N g-1 soil*/ UPDATE pooldilutions2008 SET NO3f = NULL ; UPDATE pooldilutions2008 SET NO3f = NO3f_raw / ((cup_soil_NO3 - cup_NO3) * (1 - moisture)) * KCl_ml ; /* µg N g-1 soil*/ UPDATE pooldilutions2008 SET NH4f = NULL ; UPDATE pooldilutions2008 SET NH4f = NH4f_raw / ((cup_soil_NH4 - cup_NH4) * (1 - moisture)) * KCl_ml ; /* µg N g-1 soil*/ UPDATE pooldilutions2008 SET pooldilutions2008.IN = NULL ; UPDATE pooldilutions2008 SET pooldilutions2008.IN = NH4i + NO3i ; /* µg N g-1 soil*/ UPDATE pooldilutions2008 SET KCl_recov_NO3 = NULL ; UPDATE pooldilutions2008 SET KCl_recov_NO3 = ((sv_NO3_post - 6.5 + bottle_NO3 - 12.3)/1.087 + 4.44) / KCl_ml * 100 ; UPDATE pooldilutions2008 SET KCl_recov_NH4 = NULL ; UPDATE pooldilutions2008 SET KCl_recov_NH4 = ((sv_NH4_post - 6.5 + bottle_NH4 - 12.3)/1.087 + 0.89) / KCl_ml * 100 ; /* PH CATIONS */ UPDATE ph_cations2008 SET moisture = NULL ; UPDATE ph_cations2008 SET moisture = ((bag_wetsoil - 6.1) - (tin_drysoil - tin_mass)) / (bag_wetsoil - 6.1) ; UPDATE ph_cations2008 SET Mn = NULL ; UPDATE ph_cations2008 SET Mn = Mn_raw * 40 / BaCl_mass ; /* µg Mn g-1 soil*/ UPDATE ph_cations2008 SET Fe = NULL ; UPDATE ph_cations2008 SET Fe = Fe_raw * 40 / BaCl_mass ; /* µg Fe g-1 soil*/ UPDATE ph_cations2008 SET Mg = NULL ; UPDATE ph_cations2008 SET Mg = Mg_raw * 40 / BaCl_mass ; /* µg Mg g-1 soil*/ UPDATE ph_cations2008 SET Ca = NULL ; UPDATE ph_cations2008 SET Ca = Ca_raw * 40 / BaCl_mass ; /* µg Ca g-1 soil*/ UPDATE ph_cations2008 SET Al = NULL ; UPDATE ph_cations2008 SET Al = Al_raw * 40 / BaCl_mass ; /* µg Al g-1 soil*/ UPDATE ph_cations2008 SET Na = NULL ; UPDATE ph_cations2008 SET Na = (Na_raw - 4.845) * 40 / BaCl_mass ; UPDATE ph_cations2008 SET Na = 0 WHERE Na < 0 ; /* µg Na g-1 soil*/ UPDATE ph_cations2008 SET K = NULL ; UPDATE ph_cations2008 SET K = K_raw * 40 / BaCl_mass ; /* µg K g-1 soil*/ UPDATE ph_cations2008 SET NO3 = NULL ; UPDATE ph_cations2008 SET NO3 = NO3_raw * 40 / BaCl_mass ; /* µg N-NO3 g-1 soil*/ UPDATE ph_cations2008 SET NH4 = NULL ; UPDATE ph_cations2008 SET NH4 = NH4_raw * 40 / BaCl_mass ; /* µg N-NH4 g-1 soil*/ UPDATE ph_cations2008 SET ph_cations2008.IN = NULL ; UPDATE ph_cations2008 SET ph_cations2008.IN = NH4 + NO3 ; /* µg N g-1 soil*/ /* PH SPATIAL ACID ADDITIONS */ UPDATE pH_spatial SET acid = NULL ; UPDATE pH_spatial SET acid = HNO3_ml * 0.08 / soilmass ; /* POOL DILUTION VIEW */ CREATE VIEW pd_gis AS SELECT soilbins2006.bin, pooldilutions2008.date, pooldilutions2008.moisture, pooldilutions2008.NO3i, pooldilutions2008.NH4i, pooldilutions2008.IN, soilbins2006.northing_mean, soilbins2006.easting_mean FROM pooldilutions2008 INNER JOIN soilbins2006 ON pooldilutions2008.bin = soilbins2006.bin ; /* BURIED BAG 2008 VIEW */ DROP VIEW bb08 ; CREATE VIEW bb08 AS SELECT soilbins2006.bin, soilbins2006.easting_mean AS easting, soilbins2006.northing_mean AS northing, DATE(datetime_i) AS date_i, DATE(datetime_f) AS date_f, moisture_i, moisture_f, NH4i, NO3i, NH4f, NO3f, buriedbag2008.IN, net_min, net_nit, notes FROM buriedbag2008 INNER JOIN soilbins2006 ON buriedbag2008.bin = soilbins2006.bin ; /* POOL DILUTIONS VIEW */ DROP VIEW pooldilutions2008view ; CREATE VIEW pooldilutions2008view AS SELECT pooldilutions2008.bin, soilbins2006.easting_mean AS easting, soilbins2006.northing_mean AS northing, pooldilutions2008.datetime_i, pooldilutions2008.datetime_f, soilbins2006.cover AS cover, pooldilutions2008.plants, pooldilutions2008.temp_i, pooldilutions2008.temp_f, pooldilutions2008.moisture, pooldilutions2008.NO3i, pooldilutions2008.NH4i, pooldilutions2008.NO3f, pooldilutions2008.NH4f, pooldilutions2008.IN, pooldilutions2008.percentc, pooldilutions2008.percentn, pooldilutions2008.measurer, pooldilutions2008.lab_notes, pooldilutions2008.field_notes FROM pooldilutions2008 INNER JOIN soilbins2006 ON pooldilutions2008.bin = soilbins2006.bin ORDER BY datetime_i ; /* SOIL POINTS VIEW */ DROP VIEW soilpoints2006view ; CREATE VIEW soilpoints2006view AS SELECT soilpoints2006.samplepoint_id, soilpoints2006.bin, soilpoints2006.datetime, soilpoints2006.northing, soilpoints2006.easting, soilbins2006.cover, soilpoints2006.temp, soilbins2006.moisture, soilbins2006.pH, soilbins2006.light_percentc, soilbins2006.heavy_percentc, soilbins2006.total_percentc, soilbins2006.light_pc_core, soilbins2006.heavy_pc_core,Ê soilbins2006.light_percentn, soilbins2006.heavy_percentn, soilbins2006.total_percentn, soilbins2006.light_pn_core, soilbins2006.heavy_pn_core,Ê soilbins2006.light_del13c, soilbins2006.heavy_del13c, soilbins2006.total_del13c, soilbins2006.light_del15n, soilbins2006.heavy_del15n, soilbins2006.total_del15n, soilbins2006.light_C_to_N, soilbins2006.heavy_C_to_N, soilbins2006.total_C_to_N, soilbins2006.percent_light, soilbins2006.percent_heavysmall, soilbins2006.percent_heavylargeÊ FROM soilpoints2006 INNER JOIN soilbins2006 ON soilpoints2006.bin = soilbins2006.bin ; /* RESINS VIEW */ DROP VIEW resinsview ; CREATE VIEW resinsview AS SELECT sample, easting, northing, cover, bottle, NO3a, NO3b, NH4a, NH4b, mean_NO3, mean_NH4, mean_NO3 + 1 AS NO3_1, mean_NH4 + 1 AS NH4_1, resins2007.IN, field_notes, lab_notes_a, lab_notes_b FROM resins2007 ; /* CATIONS VIEW */ DROP VIEW cationsview ; CREATE VIEW cationsview AS SELECT ph_cations2008.bin, soilbins2006.easting_mean AS easting, soilbins2006.northing_mean AS northing, soilbins2006.cover, ph_cations2008.Na, ph_cations2008.K, ph_cations2008.Mn, ph_cations2008.Mg, ph_cations2008.Ca, ph_cations2008.Al, ph_cations2008.Fe, ph_cations2008.NO3, ph_cations2008.NH4, ph_cations2008.IN FROM ph_cations2008 INNER JOIN soilbins2006 ON ph_cations2008.bin = soilbins2006.bin ; DROP VIEW bb07 ; CREATE VIEW bb07 AS SELECT soilbins2006.bin, soilbins2006.easting_mean AS easting, soilbins2006.northing_mean AS northing, soilbins2006.cover, buriedbag2007.net_min, buriedbag2007.net_nit, buriedbag2007.temp_i, buriedbag2007.moisture_initial, buriedbag2007.moisture_final, buriedbag2007.soil_NH4_i as NH4i, buriedbag2007.soil_NO3_i as NO3i, buriedbag2007.soil_NH4_f as NH4f, buriedbag2007.soil_NO3_f as NO3f, buriedbag2007.IN, buriedbag2007.notes_initial, buriedbag2007.notes_final FROM buriedbag2007 INNER JOIN soilbins2006 ON buriedbag2007.bin = soilbins2006.bin ; /* ACID EXPERIMENT VIEW */ DROP VIEW aadif ; CREATE VIEW aadif AS SELECT pH_spatial_gis.bin, soilbins2006.easting_mean AS easting, soilbins2006.northing_mean AS northing, soilbins2006.cover, pH_spatial_gis.pH_5weekdif_1_0, pH_spatial_gis.pH_5weekdif_2_0, pH_spatial_gis.pH_5weekdif_3_0, pH_spatial_gis.pH_5weekdif_4_0, pH_spatial_gis.pH_5weekdif_5_0, pH_spatial_gis.pH_24h_st0, pH_spatial_gis.pH_5week_st0, pH_spatial_gis.pH_24h_st1, pH_spatial_gis.pH_5week_st1, pH_spatial_gis.pH_24h_st2, pH_spatial_gis.pH_5week_st2, pH_spatial_gis.pH_24h_st3, pH_spatial_gis.pH_5week_st3, pH_spatial_gis.pH_24h_st4, pH_spatial_gis.pH_5week_st4, pH_spatial_gis.pH_24h_st5, ph_cations2008.Na, ph_cations2008.K, ph_cations2008.Mn, ph_cations2008.Mg, ph_cations2008.Ca, ph_cations2008.Al, ph_cations2008.Fe, ph_cations2008.NO3, ph_cations2008.NH4, ph_cations2008.IN FROM pH_spatial_gis INNER JOIN soilbins2006 ON pH_spatial_gis.bin = soilbins2006.bin INNER JOIN ph_cations2008 ON pH_spatial_gis.bin = ph_cations2008.bin ; /* VEGETATION VIEW */ DROP VIEW vegetationview ; CREATE VIEW vegetationview AS SELECT vegetation.bin, soilbins2006.easting_mean AS easting, soilbins2006.northing_mean AS northing, soilbins2006.cover, vegetation.measurer, vegetation.Achillea_millefolium, vegetation.Aconitum_columbianum, vegetation.Agoseris_aurantiaca, vegetation.Allium_geyeri, vegetation.Angelica_grayi, vegetation.Antennaria_alpina, vegetation.Aquilegia_coerulea, vegetation.Arctostaphylos_uva_ursi, vegetation.Arenaria_fendleri, vegetation.Arnica_cordifolia, vegetation.Arnica_mollis, vegetation.Arnica_rydbergii, vegetation.Artemisia_pattersonii, vegetation.Artemisia_scopulorum, vegetation.Caltha_leptosepala, vegetation.Campanula_rotundifolia, vegetation.Campanula_uniflora, vegetation.Castilleja_occidentalis, vegetation.Cerastium_arvense, vegetation.Chamerion_angustifolium, vegetation.Cirsium_scopulorum, vegetation.Dodecatheon_pulchellum, vegetation.Draba_spp, vegetation.Dryas_octopetala_, vegetation.Erigeron_melanocephalus, vegetation.Erigeron_peregrinus, vegetation.Erigeron_pinnatisectus, vegetation.Erigeron_simplex, vegetation.Erigeron_unkA, vegetation.Eritrichium_nanum, vegetation.Erysimum_capitatum, vegetation.Frasera_speciosa, vegetation.Gentiana_amarella, vegetation.Gentiana_parryi, vegetation.Geum_rossii, vegetation.Heuchera_parvifolia, vegetation.Hieracium_gracile, vegetation.Lewisia_pygmaea, vegetation.Ligusticum_tenuifolium, vegetation.Mertensia_spp, vegetation.Minuartia_obtusiloba, vegetation.Noccaea_montana, vegetation.Oreoxis_alpina, vegetation.Oxypolis_fendleri, vegetation.Packera_cana, vegetation.Packera_streptanthifolia, vegetation.Paronychia_pulvinata, vegetation.Pedicularis_bracteosa, vegetation.Pedicularis_groenlandica, vegetation.Pedicularis_parryi, vegetation.Pedicularis_racemosa, vegetation.Penstemon_whippleanus, vegetation.Phlox_pulvinata, vegetation.Polemonium_pulcherrimum, vegetation.Polemonium_viscosum, vegetation.Polygonum_bistortoides, vegetation.Polygonum_viviparum, vegetation.Potentilla_diversifolia, vegetation.Potentilla_nivea, vegetation.Potentilla_ovina, vegetation.Potentilla_subjuga, vegetation.Pseudocymopterus_montanus, vegetation.Ranunculus_adoneus, vegetation.Saxifraga_rhomboidea, vegetation.Selaginella_densa, vegetation.Senecio_crassulus, vegetation.Senecio_triangularis, vegetation.Sibbaldia_procumbens, vegetation.Silene_acaulis, vegetation.Solidago_multiradiata, vegetation.Stellaria_longipes, vegetation.Taraxacum_officinale, vegetation.Tetraneuris_acaulis, vegetation.Tetraneuris_grandiflora, vegetation.Tonestus_pygmaeus, vegetation.Trollius_laxus, vegetation.Veronica_americana, vegetation.Veronica_wormskjoldii, vegetation.Viola_adunca, vegetation.Calamagrostis_canadensis, vegetation.Calamagrostis_purpurascens, vegetation.Danthonia_intermedia, vegetation.Deschampsia_caespitosa, vegetation.Elymus_scribneri, vegetation.Elymus_trachycaulus, vegetation.Festuca_brachyphylla, vegetation.Helictotrichon_mortonianum, vegetation.Phleum_alpinum, vegetation.Poa_alpina, vegetation.Poa_unk, vegetation.Trisetum_spicatum, vegetation.Juncus_spp, vegetation.Luzula_parviflora, vegetation.Luzula_spicata, vegetation.Lloydia_serotina, vegetation.Zigadenus_elegans, vegetation.Trifolium_dasyphyllum, vegetation.Trifolium_parryi, vegetation.bryophyte, vegetation.lichen, vegetation.Carex_A, vegetation.Carex_aquatilis, vegetation.Carex_B, vegetation.Carex_C, vegetation.Carex_E, vegetation.Carex_nova, vegetation.Carex_rossii, vegetation.Carex_rupestris, vegetation.Carex_siccata, vegetation.Kobresia_myosuroides, vegetation.Rhodiola_integrifolia, vegetation.Rhodiola_rhodantha, vegetation.Sedum_lanceolatum, vegetation.Unvegetated, vegetation.Abies_lasiocarpa, vegetation.Juniperus_communis, vegetation.Pentaphylloides_floribunda, vegetation.Picea_engelmannii, vegetation.Pinus_flexilis, vegetation.Ribes_montigenum, vegetation.Salix_spp, vegetation.Vaccinium_spp FROM vegetation INNER JOIN soilbins2006 ON vegetation.bin = soilbins2006.bin ; /* ALL PHOTOS VIEW */ CREATE VIEW photos AS SELECT northing, easting, photo FROM resins2007 WHERE photo IS NOT NULL UNION SELECT northing, easting, photo FROM soilpoints2006 WHERE photo IS NOT NULL AND northing IS NOT NULL UNION SELECT soilbins2006.northing_mean, soilbins2006.easting_mean, buriedbag2007.photo_initial AS photo FROM buriedbag2007 INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2007.bin WHERE photo_initial IS NOT NULL UNION SELECT soilbins2006.northing_mean, soilbins2006.easting_mean, buriedbag2007.photo_final AS photo FROM buriedbag2007 INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2007.bin WHERE photo_final IS NOT NULL ORDER BY northing; /* ALL SOIL TEMPERATURES VIEW */ DROP VIEW temperature ; CREATE VIEW temperature AS SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, datetime_initial AS datetime, buriedbag2007.temp_i AS temp FROM buriedbag2007 INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2007.bin WHERE buriedbag2007.temp_i IS NOT NULL UNION SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, buriedbag2008.datetime_i AS datetime, buriedbag2008.temp_i AS temp FROM buriedbag2008 INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2008.bin WHERE buriedbag2008.temp_i IS NOT NULL UNION SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, buriedbag2008.datetime_f AS datetime, buriedbag2008.temp_f AS temp FROM buriedbag2008 INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2008.bin WHERE DATE(buriedbag2008.datetime_f) = "2008-09-23" UNION SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, pooldilutions2008.datetime_i AS datetime, pooldilutions2008.temp_i AS temp FROM pooldilutions2008 INNER JOIN soilbins2006 ON soilbins2006.bin = pooldilutions2008.bin WHERE pooldilutions2008.temp_i IS NOT NULL UNION SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, pooldilutions2008.datetime_f AS datetime, pooldilutions2008.temp_f AS temp FROM pooldilutions2008 INNER JOIN soilbins2006 ON soilbins2006.bin = pooldilutions2008.bin WHERE pooldilutions2008.temp_f IS NOT NULL UNION SELECT snowmax2008.northing, snowmax2008.easting, snowmax2008.cover, snowmelt2008.datetime, snowmelt2008.soil_temp AS temp FROM snowmelt2008 INNER JOIN snowmax2008 ON snowmax2008.number = snowmelt2008.number WHERE snowmelt2008.soil_temp IS NOT NULL UNION SELECT soilpoints2006.northing, soilpoints2006.easting, soilbins2006.cover, soilpoints2006.datetime, soilpoints2006.temp FROM soilpoints2006 INNER JOIN soilbins2006 ON soilpoints2006.bin = soilbins2006.bin WHERE soilpoints2006.temp IS NOT NULL UNION SELECT resins2007.northing, resins2007.easting, resins2007.cover, soiltemp2009.datetime, soiltemp2009.temp FROM soiltemp2009 INNER JOIN resins2007 ON soiltemp2009.resin = resins2007.sample WHERE soiltemp2009.resin IS NOT NULL UNION SELECT soilbins2006.northing_mean, soilbins2006.easting_mean, soilbins2006.cover, soiltemp2009.datetime, soiltemp2009.temp FROM soiltemp2009 INNER JOIN soilbins2006 ON soiltemp2009.bin = soilbins2006.bin WHERE soiltemp2009.bin IS NOT NULL ; /* ALL SOIL TEMPERATURES VIEW WITH SUMMARIZED 2006 DATA */ DROP VIEW temperature ; CREATE VIEW temperature AS SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, datetime_initial AS datetime, buriedbag2007.temp_i AS temp FROM buriedbag2007 INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2007.bin WHERE buriedbag2007.temp_i IS NOT NULL UNION SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, buriedbag2008.datetime_i AS datetime, buriedbag2008.temp_i AS temp FROM buriedbag2008 INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2008.bin WHERE buriedbag2008.temp_i IS NOT NULL UNION SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, buriedbag2008.datetime_f AS datetime, buriedbag2008.temp_f AS temp FROM buriedbag2008 INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2008.bin WHERE DATE(buriedbag2008.datetime_f) = "2008-09-23" UNION SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, pooldilutions2008.datetime_i AS datetime, pooldilutions2008.temp_i AS temp FROM pooldilutions2008 INNER JOIN soilbins2006 ON soilbins2006.bin = pooldilutions2008.bin WHERE pooldilutions2008.temp_i IS NOT NULL UNION SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, pooldilutions2008.datetime_f AS datetime, pooldilutions2008.temp_f AS temp FROM pooldilutions2008 INNER JOIN soilbins2006 ON soilbins2006.bin = pooldilutions2008.bin WHERE pooldilutions2008.temp_f IS NOT NULL UNION SELECT snowmax2008.northing, snowmax2008.easting, snowmax2008.cover, snowmelt2008.datetime, snowmelt2008.soil_temp AS temp FROM snowmelt2008 INNER JOIN snowmax2008 ON snowmax2008.number = snowmelt2008.number WHERE snowmelt2008.soil_temp IS NOT NULL UNION SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(datetime))) AS datetime, avg(soilpoints2006.temp) FROM soilpoints2006 INNER JOIN soilbins2006 ON soilpoints2006.bin = soilbins2006.bin WHERE soilpoints2006.temp IS NOT NULL GROUP BY soilbins2006.bin UNION SELECT resins2007.northing, resins2007.easting, resins2007.cover, soiltemp2009.datetime, soiltemp2009.temp FROM soiltemp2009 INNER JOIN resins2007 ON soiltemp2009.resin = resins2007.sample WHERE soiltemp2009.resin IS NOT NULL UNION SELECT soilbins2006.northing_mean, soilbins2006.easting_mean, soilbins2006.cover, soiltemp2009.datetime, soiltemp2009.temp FROM soiltemp2009 INNER JOIN soilbins2006 ON soiltemp2009.bin = soilbins2006.bin WHERE soiltemp2009.bin IS NOT NULL ; /* ALL GRAVIMETRIC SOIL MOISTURES VIEW (NOT INCLUDING TDR) */ DROP VIEW moisture ; CREATE VIEW moisture AS SELECT soilbins2006.bin, soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, DATE(datetime_initial) AS date, soilbins2006.cover, buriedbag2007.moisture_initial AS moisture, soilbins_sup.elevation, soilbins_sup.slope, soilbins_sup.aspect FROM buriedbag2007 INNER JOIN soilbins_sup ON soilbins_sup.bin = buriedbag2007.bin INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2007.bin WHERE buriedbag2007.moisture_initial IS NOT NULL UNION SELECT soilbins2006.bin, soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, DATE(buriedbag2008.datetime_i) AS date, soilbins2006.cover, buriedbag2008.moisture_i AS moisture, soilbins_sup.elevation, soilbins_sup.slope, soilbins_sup.aspect FROM buriedbag2008 INNER JOIN soilbins_sup ON soilbins_sup.bin = buriedbag2008.bin INNER JOIN soilbins2006 ON soilbins2006.bin = buriedbag2008.bin WHERE buriedbag2008.moisture_i IS NOT NULL UNION SELECT soilbins2006.bin, soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, DATE(pooldilutions2008.datetime_i) AS date, soilbins2006.cover, pooldilutions2008.moisture AS moisture, soilbins_sup.elevation, soilbins_sup.slope, soilbins_sup.aspect FROM pooldilutions2008 INNER JOIN soilbins_sup ON soilbins_sup.bin = pooldilutions2008.bin INNER JOIN soilbins2006 ON soilbins2006.bin = pooldilutions2008.bin WHERE pooldilutions2008.moisture IS NOT NULL UNION SELECT soilbins2006.bin, soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, DATE("2008-08-23") AS date, soilbins2006.cover, ph_cations2008.moisture AS moisture, soilbins_sup.elevation, soilbins_sup.slope, soilbins_sup.aspect FROM ph_cations2008 INNER JOIN soilbins_sup ON soilbins_sup.bin = ph_cations2008.bin INNER JOIN soilbins2006 ON soilbins2006.bin = ph_cations2008.bin WHERE ph_cations2008.moisture IS NOT NULL UNION SELECT soilbins2006.bin, soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, DATE(soilpoints2006.datetime) as datetime, soilbins2006.cover, moisture, soilbins_sup.elevation, soilbins_sup.slope, soilbins_sup.aspect FROM soilbins2006 INNER JOIN soilbins_sup ON soilbins_sup.bin = soilbins2006.bin INNER JOIN soilpoints2006 ON soilbins2006.bin = soilpoints2006.bin WHERE moisture IS NOT NULL GROUP BY soilpoints2006.bin ORDER BY DATE ; /* ALL NO3 AND NH4 FROM EXTRACTIONS */ DROP VIEW NO3NH4 ; CREATE VIEW NO3NH4 AS SELECT soilbins2006.northing_mean AS northing, soilbins2006.easting_mean AS easting, soilbins2006.cover, buriedbag2007.soil_NO3_i AS NO3_2007, buriedbag2007.soil_NH4_i as NH4_2007, ph_cations2008.NO3 as NO3_8_23_08, ph_cations2008.NH4 as NH4_8_23_08, pooldilutions2008.NO3i as NO3_7_30_08, pooldilutions2008.NH4i as NH4_7_30_08 FROM soilbins2006 INNER JOIN ph_cations2008 ON soilbins2006.bin = ph_cations2008.bin INNER JOIN buriedbag2007 ON soilbins2006.bin = buriedbag2007.bin INNER JOIN pooldilutions2008 ON soilbins2006.bin = pooldilutions2008.bin WHERE DATE(pooldilutions2008.datetime_i) = "2008-07-30" ; /* STILL NEEDS SECOND DATE FROM pooldilutions */ /* VEGETATION DATA SETS SOILBINS AND RESINS */ DROP VIEW veg_resins_view ; CREATE VIEW veg_resins_view AS SELECT veg_resins.resin, resins2007.easting AS easting, resins2007.northing AS northing, resins2007.cover, veg_resins.Abies_lasiocarpa, veg_resins.Achillea_millefolium, veg_resins.Aconitum_columbianum, veg_resins.Agoseris_aurantiaca, veg_resins.Agoseris_glauca, veg_resins.Allium_geyeri, veg_resins.Androsace_septentrionalis, veg_resins.Angelica_grayi, veg_resins.Antennaria_alpina, veg_resins.Arabis_sp, veg_resins.Arctostaphylos_uva_ursi, veg_resins.Arenaria_fendleri, veg_resins.Arnica_cordifolia, veg_resins.Arnica_mollis, veg_resins.Arnica_rydbergii, veg_resins.Artemisia_pattersonii, veg_resins.Artemisia_scopulorum, veg_resins.Calamagrostis_canadensis, veg_resins.Calamagrostis_purpurascens, veg_resins.Caltha_leptosepala, veg_resins.Campanula_sp, veg_resins.Carex_aquatilis, veg_resins.Carex_arapahoensis, veg_resins.Carex_ebenea, veg_resins.Carex_microptera, veg_resins.Carex_nova, veg_resins.Carex_pyrenaica, veg_resins.Carex_R16B, veg_resins.Carex_rossii, veg_resins.Carex_rupestris, veg_resins.Carex_scopulorum, veg_resins.Carex_siccata, veg_resins.Castilleja_sp, veg_resins.Cerastium_arvense, veg_resins.Chamerion_angustifolium, veg_resins.Cryptogramma_acrostichoides, veg_resins.Danthonia_intermedia, veg_resins.Dasiphora_fruticosa, veg_resins.Deschampsia_caespitosa, veg_resins.Dodecatheon_pulchellum, veg_resins.Draba_aurea, veg_resins.Draba_crassifolia, veg_resins.Draba_sp, veg_resins.Draba_streptocarpa, veg_resins.Dryas_octopetala, veg_resins.Erigeron_melanocephalus, veg_resins.Erigeron_peregrinus, veg_resins.Erigeron_pinnatisectus, veg_resins.Erigeron_simplex, veg_resins.Eritrichium_nanum, veg_resins.Erysimum_capitatum, veg_resins.Festuca_brachyphylla, veg_resins.Frasera_speciosa, veg_resins.Gentiana_algida, veg_resins.Gentiana_parryi, veg_resins.Gentianella_amarella, veg_resins.Geum_rossii, veg_resins.Helictotrichon_mortonianum, veg_resins.Heuchera_parvifolia, veg_resins.Hieracium_gracile, veg_resins.Juncus_biglumis, veg_resins.Juncus_drummondii, veg_resins.Juniperus_communis, veg_resins.Kobresia_myosuroides, veg_resins.Lewisia_pygmaea, veg_resins.Ligusticum_tenuifolium, veg_resins.Lloydia_serotina, veg_resins.Lonicera_involucrata, veg_resins.Luzula_parviflora, veg_resins.Luzula_spicata, veg_resins.Mertensia_ciliata, veg_resins.Mertensia_lanceolata, veg_resins.Minuartia_obtusiloba, veg_resins.moss, veg_resins.Noccaea_montana, veg_resins.Oreoxis_alpina, veg_resins.Packera_cana, veg_resins.Paronychia_pulvinata, veg_resins.Pedicularis_bracteosa, veg_resins.Pedicularis_groenlandica, veg_resins.Pedicularis_parryi, veg_resins.Pedicularis_racemosa, veg_resins.Penstemon_whippleanus, veg_resins.Phleum_alpinum, veg_resins.Phlox_pulvinata, veg_resins.Picea_engelmannii, veg_resins.Pinus_flexilis, veg_resins.Poa_alpina, veg_resins.Poa_sp_A, veg_resins.Poa_sp_B, veg_resins.Poa_sp_C, veg_resins.Polemonium_pulcherrimum, veg_resins.Polemonium_viscosum, veg_resins.Polygonum_sp, veg_resins.Populus_tremuloides, veg_resins.Potentilla_diversifolia, veg_resins.Potentilla_nivea, veg_resins.Potentilla_pennsylvanica, veg_resins.Potentilla_sp, veg_resins.Pseudocymopterus_montanus, veg_resins.Ranunculus_adoneus, veg_resins.Rhodiola_integrifolia, veg_resins.Ribes_montigenum, veg_resins.Salix_sp, veg_resins.Saxifraga_rhomboidea, veg_resins.Sedum_lanceolatum, veg_resins.Selaginella_densa, veg_resins.Senecio_crassulus, veg_resins.Senecio_crocata, veg_resins.Senecio_triangularis, veg_resins.Sibbaldia_procumbens, veg_resins.Silene_acaulis, veg_resins.Solidago_multiradiata, veg_resins.Stellaria_calycantha, veg_resins.Stellaria_longipes, veg_resins.Stellaria_umbellata, veg_resins.Swertia_perennis, veg_resins.Taraxacum_officinale, veg_resins.Tetraneuris_acaulis, veg_resins.Tetraneuris_grandiflora, veg_resins.Tonestus_pygmaeus, veg_resins.Trifolium_dasyphyllum, veg_resins.Trifolium_parryi, veg_resins.Trisetum_spicatum, veg_resins.Trollius_laxus, veg_resins.unidentified, veg_resins.unidentified_no_sample, veg_resins.unidentified_sample, veg_resins.unvegetated, veg_resins.Vaccinium_sp, veg_resins.Veronica_americana, veg_resins.Veronica_wormskjoldii, veg_resins.Viola_adunca, veg_resins.Zigadenus_elegans FROM veg_resins INNER JOIN resins2007 ON veg_resins.resin = resins2007.sample ; DROP VIEW veg_soilbins_view ; CREATE VIEW veg_soilbins_view AS SELECT veg_soilbins.bin, soilbins2006.easting_mean AS easting, soilbins2006.northing_mean AS northing, soilbins2006.cover, veg_soilbins.Abies_lasiocarpa, veg_soilbins.Achillea_millefolium, veg_soilbins.Aconitum_columbianum, veg_soilbins.Agoseris_aurantiaca, veg_soilbins.Agoseris_glauca, veg_soilbins.Allium_geyeri, veg_soilbins.Angelica_grayi, veg_soilbins.Antennaria_alpina, veg_soilbins.Aquilegia_coerulea, veg_soilbins.Arabis_sp, veg_soilbins.Arctostaphylos_uva_ursi, veg_soilbins.Arenaria_fendleri, veg_soilbins.Arnica_cordifolia, veg_soilbins.Arnica_mollis, veg_soilbins.Artemisia_pattersonii, veg_soilbins.Artemisia_scopulorum, veg_soilbins.Calamagrostis_canadensis, veg_soilbins.Calamagrostis_purpurascens, veg_soilbins.Caltha_leptosepala, veg_soilbins.Campanula_sp, veg_soilbins.Carex_aquatilis, veg_soilbins.Carex_arapahoensis, veg_soilbins.Carex_microptera, veg_soilbins.Carex_nova, veg_soilbins.Carex_pyrenaica, veg_soilbins.Carex_R16B, veg_soilbins.Carex_rossii, veg_soilbins.Carex_rupestris, veg_soilbins.Carex_scopulorum, veg_soilbins.Carex_siccata, veg_soilbins.Castilleja_sp, veg_soilbins.Cerastium_arvense, veg_soilbins.Chamerion_angustifolium, veg_soilbins.Cirsium_scopulorum, veg_soilbins.Cystopteris_fragilis, veg_soilbins.Danthonia_intermedia, veg_soilbins.Dasiphora_fruticosa, veg_soilbins.Deschampsia_caespitosa, veg_soilbins.Dodecatheon_pulchellum, veg_soilbins.Draba_crassifolia, veg_soilbins.Draba_sp, veg_soilbins.Dryas_octopetala, veg_soilbins.Elymus_scribneri, veg_soilbins.Elymus_trachycaulus, veg_soilbins.Erigeron_melanocephalus, veg_soilbins.Erigeron_peregrinus, veg_soilbins.Erigeron_pinnatisectus, veg_soilbins.Erigeron_simplex, veg_soilbins.Eritrichium_nanum, veg_soilbins.Erysimum_capitatum, veg_soilbins.Festuca_brachyphylla, veg_soilbins.Frasera_speciosa, veg_soilbins.Gentiana_algida, veg_soilbins.Gentiana_parryi, veg_soilbins.Gentianella_amarella, veg_soilbins.Geum_rossii, veg_soilbins.Helictotrichon_mortonianum, veg_soilbins.Heuchera_parvifolia, veg_soilbins.Hieracium_gracile, veg_soilbins.Juncus_biglumis, veg_soilbins.Juncus_drummondii, veg_soilbins.Juniperus_communis, veg_soilbins.Kobresia_myosuroides, veg_soilbins.Lewisia_pygmaea, veg_soilbins.lichen, veg_soilbins.Lloydia_serotina, veg_soilbins.Luzula_parviflora, veg_soilbins.Luzula_spicata, veg_soilbins.Mertensia_ciliata, veg_soilbins.Mertensia_lanceolata, veg_soilbins.Minuartia_obtusiloba, veg_soilbins.moss, veg_soilbins.Noccaea_montana, veg_soilbins.Oreoxis_alpina, veg_soilbins.Packera_cana, veg_soilbins.Paronychia_pulvinata, veg_soilbins.Pedicularis_bracteosa, veg_soilbins.Pedicularis_groenlandica, veg_soilbins.Pedicularis_parryi, veg_soilbins.Pedicularis_racemosa, veg_soilbins.Penstemon_whippleanus, veg_soilbins.Phleum_alpinum, veg_soilbins.Phlox_pulvinata, veg_soilbins.Picea_engelmannii, veg_soilbins.Pinus_flexilis, veg_soilbins.Poa_alpina, veg_soilbins.Poa_sp_A, veg_soilbins.Poa_sp_B, veg_soilbins.Poa_sp_C, veg_soilbins.Polemonium_pulcherrimum, veg_soilbins.Polemonium_viscosum, veg_soilbins.Polygonum_sp, veg_soilbins.Potentilla_diversifolia, veg_soilbins.Potentilla_nivea, veg_soilbins.Potentilla_sp, veg_soilbins.Pseudocymopterus_montanus, veg_soilbins.Ranunculus_adoneus, veg_soilbins.Rhodiola_integrifolia, veg_soilbins.Rhodiola_rhodantha, veg_soilbins.Ribes_montigenum, veg_soilbins.Salix_sp, veg_soilbins.Saxifraga_rhomboidea, veg_soilbins.Sedum_lanceolatum, veg_soilbins.Selaginella_densa, veg_soilbins.Senecio_crassulus, veg_soilbins.Senecio_crocata, veg_soilbins.Senecio_triangularis, veg_soilbins.Sibbaldia_procumbens, veg_soilbins.Silene_acaulis, veg_soilbins.Solidago_multiradiata, veg_soilbins.Stellaria_longipes, veg_soilbins.Taraxacum_officinale, veg_soilbins.Tetraneuris_acaulis, veg_soilbins.Tetraneuris_grandiflora, veg_soilbins.Tonestus_pygmaeus, veg_soilbins.Trifolium_dasyphyllum, veg_soilbins.Trifolium_parryi, veg_soilbins.Trisetum_spicatum, veg_soilbins.Trollius_laxus, veg_soilbins.unidentified, veg_soilbins.unidentified_no_sample, veg_soilbins.unidentified_sample, veg_soilbins.unvegetated, veg_soilbins.Vaccinium_sp, veg_soilbins.Veronica_americana, veg_soilbins.Veronica_wormskjoldii, veg_soilbins.Viola_adunca, veg_soilbins.Zigadenus_elegans FROM veg_soilbins INNER JOIN soilbins2006 ON veg_soilbins.bin = soilbins2006.bin ; /* ASSEMBLING DIVERSE DATASETS FOR ANALYSIS */ DROP VIEW sb_sub_pd1 ; CREATE VIEW sb_sub_pd1 AS SELECT bin, percentn, percentc, NH4i, NO3i, moisture, plants, plant_class, species1, species2, species3 FROM pooldilutions2008 WHERE DATE(pooldilutions2008.datetime_i) = '2008-07-30' ; DROP VIEW sb_sub_pd2 ; CREATE VIEW sb_sub_pd2 AS SELECT bin, NH4i, NO3i, moisture, plants, plant_class, species1, species2, species3 FROM pooldilutions2008 WHERE DATE(pooldilutions2008.datetime_i) = '2008-07-09' ; DROP VIEW sb ; CREATE VIEW sb AS SELECT soilbins2006.bin, soilbins2006.northing_mean as northing, soilbins2006.easting_mean as easting, soilbins2006.light_C_to_N, soilbins2006.heavy_C_to_N, soilbins2006.total_C_to_N, soilbins2006.light_pc_core, soilbins2006.heavy_pc_core, soilbins2006.total_percentc, soilbins2006.light_pn_core, soilbins2006.heavy_pn_core, soilbins2006.total_percentn, soilbins2006.light_del15n, soilbins2006.heavy_del15n, soilbins2006.total_del15n, sb_sub_pd1.percentn, sb_sub_pd1.percentc, sb_sub_pd1.percentc/sb_sub_pd1.percentn AS singlecoreCN, buriedbag2007.net_min, buriedbag2007.net_nit, buriedbag2007.soil_NH4_i AS NH4_jul07, buriedbag2007.soil_NO3_i AS NO3_jul07, buriedbag2007.soil_NH4_i + buriedbag2007.soil_NO3_i AS IN_jul07, sb_sub_pd1.NH4i AS NH4_730, sb_sub_pd1.NO3i AS NO3_730, sb_sub_pd1.NH4i + sb_sub_pd1.NO3i AS IN_730, sb_sub_pd2.NH4i AS NH4_709, sb_sub_pd2.NO3i AS NO3_709, sb_sub_pd2.NH4i + sb_sub_pd2.NO3i AS IN_709, soilbins_sup.elevation, soilbins_sup.slope, soilbins_sup.aspect, temp_resid.resid AS temp_resid_field, soilbins_sup.temp_resid AS temp_resid_krige, sb_sub_pd1.moisture AS moist_field_730, sb_sub_pd2.moisture AS moist_field_709, soilbins_sup.moist_jul7_30 as moist_krige, soilbins_sup.snow_depth, soilbins2006.pH, ph_cations2008.Ca as Ca_field, soilbins_sup.Ca as Ca_krige, ph_cations2008.Al as Al_field, soilbins_sup.Al as Al_krige, soilbins_sup.unveg_krige, soilbins2006.cover, sb_sub_pd1.plants AS plants_730, sb_sub_pd2.plants AS plants_709, sb_sub_pd1.plant_class AS plant_class_730, sb_sub_pd2.plant_class AS plant_class_709, sb_sub_pd1.species1 AS species1_730, sb_sub_pd2.species1 AS species1_709, sb_sub_pd1.species2 AS species2_730, sb_sub_pd2.species2 AS species2_709, sb_sub_pd1.species3 AS species3_730, sb_sub_pd2.species3 AS species3_709, veg_soilbins.Picea_engelmannii, veg_soilbins.Vaccinium_sp, veg_soilbins.Carex_rupestris, veg_soilbins.Abies_lasiocarpa, veg_soilbins.Caltha_leptosepala, veg_soilbins.Geum_rossii, veg_soilbins.Deschampsia_caespitosa, veg_soilbins.Juncus_drummondii, veg_soilbins.Minuartia_obtusiloba, veg_soilbins.Oreoxis_alpina, veg_soilbins.Selaginella_densa, veg_soilbins.Trifolium_dasyphyllum, veg_soilbins.Danthonia_intermedia, veg_soilbins.Erigeron_peregrinus, veg_soilbins.moss, veg_soilbins.Trollius_laxus, veg_soilbins.Achillea_millefolium, veg_soilbins.Phlox_pulvinata, veg_soilbins.Salix_sp, veg_soilbins.Kobresia_myosuroides, veg_soilbins.Carex_scopulorum, veg_soilbins.Antennaria_alpina, veg_soilbins.Sibbaldia_procumbens, veg_soilbins.Pinus_flexilis, veg_soilbins.Arenaria_fendleri, veg_soilbins.Silene_acaulis, veg_soilbins.Arnica_cordifolia, veg_soilbins.Trifolium_parryi, veg_soilbins.Potentilla_diversifolia, veg_soilbins.Carex_R16B AS Carex_RsixteenB, veg_soilbins.Tetraneuris_acaulis, veg_soilbins.Artemisia_pattersonii, veg_soilbins.Polygonum_sp, veg_soilbins.Dasiphora_fruticosa, veg_soilbins.Solidago_multiradiata, veg_soilbins.Campanula_sp, veg_soilbins.Calamagrostis_purpurascens, veg_soilbins.Poa_sp_C, veg_soilbins.Poa_sp_B, veg_soilbins.Ribes_montigenum, veg_soilbins.Artemisia_scopulorum, veg_soilbins.Arctostaphylos_uva_ursi, veg_soilbins.Erigeron_simplex, veg_soilbins.Viola_adunca, veg_soilbins.Carex_rossii, veg_soilbins.Carex_aquatilis, veg_soilbins.Festuca_brachyphylla, veg_soilbins.Veronica_wormskjoldii, veg_soilbins.Carex_siccata, veg_soilbins.Lewisia_pygmaea, soilbins2006.cv_div FROM soilbins2006 LEFT JOIN ph_cations2008 ON soilbins2006.bin = ph_cations2008.bin LEFT JOIN buriedbag2007 ON soilbins2006.bin = buriedbag2007.bin LEFT JOIN veg_soilbins ON soilbins2006.bin = veg_soilbins.bin LEFT JOIN soilbins_sup ON soilbins2006.bin = soilbins_sup.bin LEFT JOIN temp_resid ON (soilbins2006.northing_mean = temp_resid.northing AND soilbins2006.easting_mean = temp_resid.easting) LEFT JOIN sb_sub_pd1 ON sb_sub_pd1.bin = soilbins2006.bin LEFT JOIN sb_sub_pd2 ON sb_sub_pd2.bin = soilbins2006.bin WHERE soilbins2006.cover IN ('open','trees', 'willows') ; DROP VIEW res ; CREATE VIEW res AS SELECT resins2007.sample as resin, resins2007.northing, resins2007.easting, resins2007.mean_NH4, resins2007.mean_NO3, resins2007.mean_NO3 + resins2007.mean_NH4 as mean_IN, resin_sup.elevation, resin_sup.slope, resin_sup.aspect, resin_sup.temp_resid AS temp_resid_krige, resin_sup.moist_jul7_30 as moist_krige, resin_sup.snow_depth, resin_sup.pH, resin_sup.Ca as Ca_krige, resin_sup.Al Al_krige, resin_sup.C_to_N, resin_sup.percentc, resin_sup.percentn, resin_sup.del15n, resin_sup.unveg_krige, resins2007.species1, resins2007.species2, resins2007.species3, resins2007.cover, veg_resins.Picea_engelmannii, veg_resins.Vaccinium_sp, veg_resins.Carex_rupestris, veg_resins.Abies_lasiocarpa, veg_resins.Caltha_leptosepala, veg_resins.Geum_rossii, veg_resins.Deschampsia_caespitosa, veg_resins.Juncus_drummondii, veg_resins.Minuartia_obtusiloba, veg_resins.Oreoxis_alpina, veg_resins.Selaginella_densa, veg_resins.Trifolium_dasyphyllum, veg_resins.Danthonia_intermedia, veg_resins.Erigeron_peregrinus, veg_resins.moss, veg_resins.Trollius_laxus, veg_resins.Achillea_millefolium, veg_resins.Phlox_pulvinata, veg_resins.Salix_sp, veg_resins.Kobresia_myosuroides, veg_resins.Carex_scopulorum, veg_resins.Antennaria_alpina, veg_resins.Sibbaldia_procumbens, veg_resins.Pinus_flexilis, veg_resins.Arenaria_fendleri, veg_resins.Silene_acaulis, veg_resins.Arnica_cordifolia, veg_resins.Trifolium_parryi, veg_resins.Potentilla_diversifolia, veg_resins.Carex_R16B AS Carex_RsixteenB, veg_resins.Tetraneuris_acaulis, veg_resins.Artemisia_pattersonii, veg_resins.Polygonum_sp, veg_resins.Dasiphora_fruticosa, veg_resins.Solidago_multiradiata, veg_resins.Campanula_sp, veg_resins.Calamagrostis_purpurascens, veg_resins.Poa_sp_C, veg_resins.Poa_sp_B, veg_resins.Ribes_montigenum, veg_resins.Artemisia_scopulorum, veg_resins.Arctostaphylos_uva_ursi, veg_resins.Erigeron_simplex, veg_resins.Viola_adunca, veg_resins.Carex_rossii, veg_resins.Carex_aquatilis, veg_resins.Festuca_brachyphylla, veg_resins.Veronica_wormskjoldii, veg_resins.Carex_siccata, veg_resins.Lewisia_pygmaea FROM resins2007 LEFT JOIN resin_sup ON resins2007.sample = resin_sup.resin LEFT JOIN veg_resins ON resins2007.sample = veg_resins.resin ; DROP VIEW acid ; CREATE VIEW acid AS SELECT ph_cations2008.bin, soilbins2006.northing_mean as northing, soilbins2006.easting_mean as easting, ph_cations2008.Mn, ph_cations2008.Fe, ph_cations2008.Mg, ph_cations2008.Ca, ph_cations2008.Al, ph_cations2008.Na, ph_cations2008.K, ph_cations2008.moisture, ph_cations2008.NO3, ph_cations2008.NH4, ph_cations2008.pH_BaCl, ph_cations2008.IN, pH_spatial_gis.pH_24h_st0, pH_spatial_gis.pH_5week_st0, pH_spatial_gis.pH_24h_st1, pH_spatial_gis.pH_5week_st1, pH_spatial_gis.pH_24h_st2, pH_spatial_gis.pH_5week_st2, pH_spatial_gis.pH_24h_st3, pH_spatial_gis.pH_5week_st3, pH_spatial_gis.pH_24h_st4, pH_spatial_gis.pH_5week_st4, pH_spatial_gis.pH_24h_st5, pH_spatial_gis.pH_5week_st5, pH_spatial_gis.pH_5weekdif_1_0, pH_spatial_gis.pH_5weekdif_2_0, pH_spatial_gis.pH_5weekdif_3_0, pH_spatial_gis.pH_5weekdif_4_0, pH_spatial_gis.pH_5weekdif_5_0, soilbins2006.total_C_to_N, soilbins2006.total_percentc, soilbins2006.total_percentn, soilbins2006.total_del15n, sb_sub_pd1.percentn, soilbins_sup.elevation, soilbins_sup.slope, soilbins_sup.aspect, temp_resid.resid AS temp_resid_field, soilbins_sup.temp_resid AS temp_resid_krige, sb_sub_pd1.moisture AS moist_field_730, sb_sub_pd2.moisture AS moist_field_709, soilbins_sup.moist_jul7_30 as moist_krige, soilbins_sup.snow_depth, soilbins2006.pH, ph_cations2008.Ca as Ca_field, soilbins_sup.Ca as Ca_krige, ph_cations2008.Al as Al_field, soilbins_sup.Al Al_krige, soilbins2006.cover, soilbins2006.cv_div FROM ph_cations2008 LEFT JOIN pH_spatial_gis ON ph_cations2008.bin = pH_spatial_gis.bin LEFT JOIN soilbins2006 ON ph_cations2008.bin = soilbins2006.bin LEFT JOIN buriedbag2007 ON ph_cations2008.bin = buriedbag2007.bin LEFT JOIN veg_soilbins ON ph_cations2008.bin = veg_soilbins.bin LEFT JOIN soilbins_sup ON ph_cations2008.bin = soilbins_sup.bin LEFT JOIN temp_resid ON (soilbins2006.northing_mean = temp_resid.northing AND soilbins2006.easting_mean = temp_resid.easting) LEFT JOIN sb_sub_pd1 ON sb_sub_pd1.bin = ph_cations2008.bin LEFT JOIN sb_sub_pd2 ON sb_sub_pd2.bin = ph_cations2008.bin ;