search

microbiomedata / external-metadata-awareness

Tools for fetching and processing external schemas, ontologies and metadata records. Includes value set maintenance and some GitHub org exploration.
0 stars 0 forks source link

Use goldterms ontology as an input for prioritizing environmental context value sets #3

Closed turbomam closed 2 months ago

turbomam commented 2 months ago

In a merger of envo.db and goldterms.db... see

This finds the EnvO terms that appears to be used as env_local_scale in GOLD records, when the GOLD ecosystem path is environmental.terrestrial.soil:

This was a very good suggestion @cmungall , but the env_local_scale values associated with the most paths are biomes. I'm not inclined to include biomes in env_local_scale value sets.

I think many of the other suggestions are already present in the mostly-automated env_local_scale value set form the other sources, but I will continue to keep an eye on this.

cc @sierra-moxon @mslarae13 @Natalie-Winans 

select
     els_label_statements.subject,
    els_label_statements.value,
    count(1) as paths_count
from
    entailed_edge ee
join statements els_statements on
    ee.subject = els_statements.subject
join statements els_label_statements on
    els_label_statements.subject = els_statements.object
join statements goldterm_labels on
    goldterm_labels.subject = ee.subject
join statements goldterm_parent_label on
    goldterm_parent_label.subject = ee.object
where
    ee.predicate = 'rdfs:subClassOf'
    and ee.object = 'GOLDTERMS:4212'
    and els_statements.predicate = 'mixs:env_local'
    and els_label_statements.predicate = 'rdfs:label'
    and goldterm_labels.predicate = 'rdfs:label'
    and goldterm_parent_label.predicate = 'rdfs:label'
group by
    els_label_statements.subject,
    els_label_statements.value
order by
    count(1) desc
;
subject value paths_count
ENVO:00000446 terrestrial biome 52
ENVO:01000177 grassland biome 4
ENVO:00000108 meadow ecosystem 3
ENVO:00002061 oil spill 3
ENVO:00000266 pasture 2
ENVO:01001177 arable land 2
ENVO:01001207 ranch 2
ENVO:01001357 desert 2
ENVO:03600087 greenhouse 2
ENVO:00000011 garden 1
ENVO:00000022 river 1
ENVO:00000023 stream 1
ENVO:00000076 mine 1
ENVO:00000255 flood plain 1
ENVO:00000292 watershed 1
ENVO:00000300 scrubland area 1
ENVO:00000489 palsa 1
ENVO:00001996 mine drainage 1
ENVO:01000250 subpolar coniferous forest biome 1
ENVO:01000910 soil biocrust 1
ENVO:01001243 forest ecosystem 1
ENVO:01001835 alpine biome 1
ENVO:03000085 thermokarst 1
turbomam commented 2 months ago 
select
    ee.*,
    goldterm_labels.value,
    goldterm_parent_label.value,
    els_label_statements.value
from
    entailed_edge ee
join statements els_statements on
    ee.subject = els_statements.subject
join statements els_label_statements on
    els_label_statements.subject = els_statements.object
join statements goldterm_labels on
    goldterm_labels.subject = ee.subject
join statements goldterm_parent_label on
    goldterm_parent_label.subject = ee.object
where
    ee.predicate = 'rdfs:subClassOf'
    and ee.object = 'GOLDTERMS:4212' -- environmental.terrestrial.soil
    and els_statements.predicate = 'mixs:env_local'
    and els_label_statements.predicate = 'rdfs:label'
    and goldterm_labels.predicate = 'rdfs:label'
    and goldterm_parent_label.predicate = 'rdfs:label'
;
subject predicate object value value value
GOLDTERMS:4202 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Sand > Desert Environmental > Terrestrial > Soil desert
GOLDTERMS:4203 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Sand Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4204 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Sand > Oil-contaminated Environmental > Terrestrial > Soil oil spill
GOLDTERMS:4205 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Forest Soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4206 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Permafrost Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4209 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Grasslands Environmental > Terrestrial > Soil grassland biome
GOLDTERMS:4210 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Desert Environmental > Terrestrial > Soil desert
GOLDTERMS:4211 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Uranium contaminated Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4212 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4213 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Agricultural land Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4214 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Shrubland Environmental > Terrestrial > Soil scrubland area
GOLDTERMS:4215 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Contaminated Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4216 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Tropical rainforest Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4217 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Boreal forest Environmental > Terrestrial > Soil subpolar coniferous forest biome
GOLDTERMS:4218 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Mine Environmental > Terrestrial > Soil mine
GOLDTERMS:4220 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Oil-contaminated Environmental > Terrestrial > Soil oil spill
GOLDTERMS:4221 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Mine drainage Environmental > Terrestrial > Soil mine drainage
GOLDTERMS:4223 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Sand > Grasslands Environmental > Terrestrial > Soil grassland biome
GOLDTERMS:4224 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Sand > Forest soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4227 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Silt Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4228 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Clay > Grasslands Environmental > Terrestrial > Soil grassland biome
GOLDTERMS:4229 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Clay Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4230 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Clay > Agricultural land Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4231 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Clay > Contaminated Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4232 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Clay > Oil-contaminated Environmental > Terrestrial > Soil oil spill
GOLDTERMS:4234 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Wetlands > Permafrost Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4235 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Wetlands Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4237 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Loam > Agricultural Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4238 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Loam > Grasslands Environmental > Terrestrial > Soil grassland biome
GOLDTERMS:4239 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Loam > Forest soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4241 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Loam Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4242 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Loam > Agricultural land Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4243 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Loam > Contaminated Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4244 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Loam > Agricultural soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4418 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Fossil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4578 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Soil crust Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:4711 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Biocrust Environmental > Terrestrial > Soil soil biocrust
GOLDTERMS:4745 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Glacier Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5244 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Riparian soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5414 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Orchard soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5420 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Meadow Environmental > Terrestrial > Soil meadow ecosystem
GOLDTERMS:5421 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > River Environmental > Terrestrial > Soil river
GOLDTERMS:5424 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Bulk soil > Agricultural land Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5434 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Biofilm Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5438 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Farm Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5439 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Alpine Environmental > Terrestrial > Soil alpine biome
GOLDTERMS:5585 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Bulk soil > Meadow Environmental > Terrestrial > Soil meadow ecosystem
GOLDTERMS:5587 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Organic layer > Forest soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5589 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Mineral horizon > Forest soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5610 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Floodplain Environmental > Terrestrial > Soil flood plain
GOLDTERMS:5612 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Paddy field/soil > Contaminated Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5613 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Paddy field/soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5615 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Watershed Environmental > Terrestrial > Soil watershed
GOLDTERMS:5617 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Nature reserve > Meadow Environmental > Terrestrial > Soil meadow ecosystem
GOLDTERMS:5621 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Peat > Bog Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5725 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Bulk soil > Contaminated Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5727 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Ranch > Agricultural land Environmental > Terrestrial > Soil ranch
GOLDTERMS:5729 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Pasture > Agricultural land Environmental > Terrestrial > Soil pasture
GOLDTERMS:5754 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Manure-fertilized Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5768 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Arable > Agricultural land Environmental > Terrestrial > Soil arable land
GOLDTERMS:5770 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Greenhouse > Agricultural land Environmental > Terrestrial > Soil greenhouse
GOLDTERMS:5774 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Garden Environmental > Terrestrial > Soil garden
GOLDTERMS:5776 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Biochar > Agricultural land Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5777 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Greenhouse Environmental > Terrestrial > Soil greenhouse
GOLDTERMS:5779 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Potting soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5780 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Bulk soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5792 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified > Creek Environmental > Terrestrial > Soil stream
GOLDTERMS:5800 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Mangrove soil Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5803 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Peat Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5804 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Peat > Fen Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5808 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Peat > Permafrost Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5809 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Peat > Mire Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5816 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Peat > Palsa Environmental > Terrestrial > Soil palsa
GOLDTERMS:5817 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Peat > Forest Environmental > Terrestrial > Soil forest ecosystem
GOLDTERMS:5818 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Peat > Thermokarst pond Environmental > Terrestrial > Soil thermokarst
GOLDTERMS:5820 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Vadose zone Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5821 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Vadose zone > Saprolite Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5822 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Organic layer Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:5823 rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Bulk soil > Plant pot Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:Environmental-Terrestrial-Soil-Arable rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Arable Environmental > Terrestrial > Soil arable land
GOLDTERMS:Environmental-Terrestrial-Soil-Biochar rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Biochar Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:Environmental-Terrestrial-Soil-Mineral-horizon rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Mineral horizon Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:Environmental-Terrestrial-Soil-Nature-reserve rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Nature reserve Environmental > Terrestrial > Soil terrestrial biome
GOLDTERMS:Environmental-Terrestrial-Soil-Pasture rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Pasture Environmental > Terrestrial > Soil pasture
GOLDTERMS:Environmental-Terrestrial-Soil-Ranch rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Ranch Environmental > Terrestrial > Soil ranch
GOLDTERMS:Environmental-Terrestrial-Soil-Unclassified rdfs:subClassOf GOLDTERMS:4212 Environmental > Terrestrial > Soil > Unclassified Environmental > Terrestrial > Soil terrestrial biome
turbomam commented 2 months ago

closed by