Microbial Discovery Forge

Philosophy

Understand subsurface microbial ecology at contaminated sites. Study how microbial community composition (ASVs) varies in space and time and correlates with geochemical parameters (metals, nitrate, etc.) across the Oak Ridge Reservation watershed.

Data sources: ENIGMA SFA

Citation & Attribution

Provider: ENIGMA SFA, LBNL

Website: https://enigma.lbl.gov/

Scale

596

locations

4,346

samples

213,044

asvs

3,154

strains

6,705

genomes

47

tables

Schema Browser

Tables (4)

sdt_location 596 sdt_taxon 3,365 sdt_strain sdt_condition

`sdt_location` 596 rows

Column	Type	Description
`sdt_location_id`	string	Primary Key
`sdt_location_name`	string	Location name
`latitude_degree`	string	Latitude
`longitude_degree`	string	Longitude
`continent_sys_oterm_id`	string	Continent ontology term ID
`continent_sys_oterm_name`	string	Continent name
`country_sys_oterm_id`	string	Country ontology term ID
`country_sys_oterm_name`	string	Country name
`region`	string	Region name
`biome_sys_oterm_id`	string	Biome ontology term ID
`biome_sys_oterm_name`	string	Biome name
`feature_sys_oterm_id`	string	Feature ontology term ID
`feature_sys_oterm_name`	string	Feature name

Sample Queries

Get SSO community ASV profiles

SELECT ab.sdt_community_name, ab.sdt_asv_name,
       tax.taxonomic_level_sys_oterm_name, tax.sdt_taxon_name,
       ab.count_count_unit
FROM enigma_coral.ddt_brick0000459 ab
JOIN enigma_coral.ddt_brick0000454 tax
  ON ab.sdt_asv_name = tax.sdt_asv_name
WHERE ab.sdt_community_name LIKE 'SSO%'
  AND ab.count_count_unit > 0
  AND tax.taxonomic_level_sys_oterm_name = 'Genus'
ORDER BY ab.sdt_community_name, ab.count_count_unit DESC

Get SSO geochemistry

SELECT sdt_sample_name, molecule_from_list_sys_oterm_name,
       concentration_micromolar, physiochemical_state
FROM enigma_coral.ddt_brick0000010
WHERE sdt_sample_name LIKE 'SSO%'
ORDER BY sdt_sample_name, concentration_micromolar DESC

Get samples with location metadata

SELECT s.sdt_sample_name, s.date, s.depth_meter,
       l.latitude_degree, l.longitude_degree, l.region,
       l.feature_sys_oterm_name
FROM enigma_coral.sdt_sample s
JOIN enigma_coral.sdt_location l
  ON s.sdt_location_name = l.sdt_location_name
WHERE s.sdt_location_name LIKE 'SSO%'
ORDER BY s.date, s.sdt_location_name

Get high-quality genomes

SELECT q.sdt_strain_name,
       q.genome_completeness_method_checkm_percent,
       q.genome_contamination_method_checkm_percent,
       q.genome_n50_method_checkm_count_unit
FROM enigma_coral.ddt_brick0000521 q
WHERE q.genome_completeness_method_checkm_percent >= 95
  AND q.genome_contamination_method_checkm_percent <= 5
ORDER BY q.genome_completeness_method_checkm_percent DESC