Open ktoddbrown opened 3 years ago
ktoddbrown
commented
3 years ago Date-time standards are fairly well addressed here: https://en.wikipedia.org/wiki/ISO_8601 Should be noted that this format does not deal with BC dates which could be an issue with age models but won't be an issue for observation times. (HT Tanja W for the pointer)
srearl
commented
3 years ago SoDaH uses a nearly identical approach to ISCN3 for location details except that SoDaH uses an ISO date, and latitude and longitude (i.e., does not request a datum). Location data can be at either the location or profile level depending on the resolution. For example, if location data are at the level of a site, a single location is provided in the location section of the metadata template, whereas if location data are available for every data point then they are included in the profile section of the metadata template.
location variables (coordinate) from the location section the metadata template (this is the default)
| Var_long | var | Level |
|---|---|---|
| Location name | location_name | location |
| Latitude (degrees) | lat | location |
| Longitude (degrees) | long | location |
location variables (depth) from the profile section the metadata template (this is the default)
| unit_levels | Var_long | var | Level | Notes | givenUnit | class | type | hardUnit |
|---|---|---|---|---|---|---|---|---|
| cm | Layer Top | layer_top | layer | cm | numeric | cm | ||
| cm | Layer Bottom | layer_bot | layer | cm | numeric | cm | ||
| Layer middle | layer_mid | layer | cm | numeric | cm | |||
| Horizon | hzn | layer | character |
example location variables
| location_name | lat | long |
|---|---|---|
| H.J. Andrews | 44.231 | -122.22 |
| H.J. Andrews | 44.231 | -122.22 |
| H.J. Andrews | 44.231 | -122.22 |
| H.J. Andrews | 44.231 | -122.22 |
| H.J. Andrews | 44.231 | -122.22 |
| H.J. Andrews | 44.231 | -122.22 |
example location (depth) variables - only the depths of the top and bottom of the layer are included in this dataset
| layer_top | layer_bot |
|---|---|
| 0 | 10 |
| 0 | 10 |
| 0 | 10 |
| 0 | 10 |
| 0 | 10 |
brownag
commented
3 years ago Here are three USDA-NRCS databases. The NASIS "Pedon" tables used for storing point data (not necessarily laboratory, but all lab pedons have pedon data entries) by USDA-NRCS soil scientists, and SSURGO, which is our geographic database available via the Soil Data Access API.
Not quite the same as the more research oriented data models, since our databases have a lot of hierarchy/parent-child relationships... but I think I was able to follow your format more or less! I linked to the source of the table and column information for these two.
https://www.nrcs.usda.gov/wps/portal/nrcs/detail/soils/survey/tools/?cid=nrcs142p2_053547
| var_long | var | description |
|---|---|---|
| site.latstddecimaldegrees | Standard Latitude | Standardized latitude value in decimal degrees, in geographic coordinate system, WGS84 datum. Values are either auto-populated from GPS, or computed from original latitude coordinates using standard conversion algorithms. |
| site.longstddecimaldegrees | Standard Longitude | Standardized longitude value in decimal degrees, in geographic coordinate system, WGS84 datum. Values are either auto-populated from GPS, or computed from original longitude coordinates using standard conversion algorithms. |
| - | Latitude/Longitude Datum | WGS84 (implied in metadata of above two columns) |
| phorizon.hzdept | (Pedon Horizon) Top Depth | The distance from the top of the soil to the upper boundary of the soil horizon. cm |
| phorizon.hzdepb | (Pedon Horizon) Bottom Depth | The distance from the top of the soil to the base of the soil horizon. (cm) |
| siteobs.obsdate | Observation Date | T-SQL DateTime (MM/DD/YYYY in metadata) |
| - | Zero reference for the layer bounds | topO (by convention/definition of organic soil material as soil material) |
https://sdmdataaccess.sc.egov.usda.gov/QueryHelp.aspx
SSURGO is the soil survey geographic database comprised of hierarchical tables representing map units, comprised of soil components, which are related to vector (polygon) data for location. These are the digital products of soil surveys spanning 100+ years. Not a standard "research" dataset, but I figured it could be put in these terms.
| var_long | var | description |
|---|---|---|
| mupolygon.mupolygongeo | Mapunit Polygon (Geographic) | A set of geographic (WGS84) coordinates that defines an instance of a map unit polygon. |
| - | Latitude/Longitude Datum | WGS84 (implied in geographic column) |
| chorizon.hzdept_r | Component Horizon Top Depth | Representative Horizon Top Depth (typical value); see hzdept_l and hzdept_h for "low" and "high" |
| chorizon.hzdepb_r | Component Horizon Bottom Depth | Representative Horizon Top Depth (typical value); see hzdepb_l and hzdepb_h for "low" and "high" |
| legend.cordate | Legend Correlation Date | The date the final correlation document for a soil survey is signed, expressed as month, year (e.g. 07/1999) (MM/YYYY) |
| - | Zero reference for the layer bounds | topO (by convention/definition of organic soil material as soil material) |
https://jneme910.github.io/Lab_Data_Mart_Documentation/Documents/SDA_KSSL_Data_model.html
**Note that the LIMS snapshot via SDA is a draft API at this point, but the lab_ tables are in there. And the main point is to illustrate that the lab maintains a linkage of their laboratory data and database tables to the NASIS pedon structure.
| var_long | var | description |
|---|---|---|
| lab_pedon.pedlabsampnum | Unique Pedon Identifier | Assigned by laboratory to samples or subsamples, linked to a NASIS pedon |
| lab_pedon.user_pedon_id | User Pedon ID (NASIS text-based ID) | |
| lab_layer.labsampnum | Unique Layer Identifier | Assigned by laboratory to samples or subsamples, linked to a NASIS pedon horizon |
| lab_combine_nasis_ncss.latitude_decimal_degrees | Standard Latitude | These are derived from the site.* records corresponding to pedlabsampnum |
| lab_combine_nasis_ncss.longitude_decimal_degrees | Standard Longitude | These are derived from the site.* records corresponding to pedlabsampnum |
| - | Latitude/Longitude Datum | WGS84 (implied in metadata of above two columns) |
| lab_layer.hzn_top | (Lab Layer) Top Depth | The distance from the top of the soil to the upper boundary of the soil horizon. cm |
| lab_layer.hzn_bot | (Lab Layer) Bottom Depth | The distance from the top of the soil to the base of the soil horizon. (cm) |
| lab_combine_nasis_ncss.site_obsdate | Observation Date | Reference to NASIS Site Observation record ID |
| - | Zero reference for the layer bounds | topO (by convention/definition of organic soil material as soil material) |
How are time/space described for layer level data in your database?
Ex: ISCN3 has latitude-longitude-datum with depth from surface or mineral-organic interface with down in the positive direction. Upper and lower bounds for the layers are specified. Observation time is entered as a MM-DD-YYYY object and converted to an Excel days since integer.
Relevant columns from the layer table example is below
Relevant columns from the profile table example is below