%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
Site ||--|| Location : "has one"
Site ||--|| Landform : "is in one"
Site ||--|| Landscape : "is in one"
Landscape ||--|{ Landform : "comprises at least one"
Site ||--o{ "Recent events" : "may have some"
Site ||--|| "Site surface" : "has one"
"Site surface" ||--|{ "Surface cover" : "has"
"Site surface" ||--o{ Erosion : "may have some"
"Site surface" ||--o{ Deposition : "may have some"
Site ||--|{ Horizon : "has at least one"
Horizon ||--o{ "Peds" : "may contain"
Horizon ||--o{ "Rock Fragments" : "may contain"
Horizon ||--o{ "Other Fragments" : "may contain"
Horizon ||--o{ "Artefacts" : "may contain"
Horizon ||--o{ "Roots" : "may contain"
Horizon ||--o{ "Voids" : "may contain"
Horizon ||--o{ "Pores" : "may contain"
Horizon ||--|{ "Matrix colour" : "has one or more"
Horizon ||--o{ "Colour patterns" : "may have"
Horizon ||--|| "Texture" : "has a"
Horizon ||--|| "Particle size distribution (estimate)" : "has a"
Horizon ||--o{ "Concentrations" : "may have some"
Horizon ||--o{ "Coatings" : "may have some"
Horizon ||--o{ "Pan" : "may be a"
Horizon ||--o{ "Stress Features" : "may have some"
Horizon ||--o{ "Biological features" : "may have some"
Horizon ||--o{ "Consistence tests" : "may have some"
Site ||--o{ "Field tests" : "may have some"
Site ||--|{ "NZ Soil Classification" : "has an"
Horizon ||--|| "Horizon Names" : "have"
Horizon ||--|| "Functional Horizon Names" : "have"
Appendix D — Data model
The following diagrams demonstrate one way in which the contents of this manual can be structured in a database. The diagrams only deal with the data themselves - security, user management, change tracking and other helper functionalities are left out for clarity. Data tables for samples and laboratory results are also not included. The model is optimised to highlight the relationships between the various data points and contains some structures that support the controlled vocabulary lists, but is not optimised for performance.
D.1 Conceptual model
This overview shows the relationships between each part of the standard.
D.2 Soil Setting
The setting tables store site location (Chapter 3), landscape and landform (Chapter 4), land cover (Section 8.1), and land use (Section 8.2).
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
site ||--|| location : "has one"
location }|--|{ elevation_measurement_methods : "code comes from"
location }|--|{ gnss_types : "code comes from"
site ||--|| landscape : "has one associated"
landscape }|--|{ provinces : "code comes from"
landscape }|--|{ landscapes : "code comes from"
landscape ||--|{ landform : "contains at least one"
landscape ||--o{ lscape_landuse : "may have some"
lscape_landuse }|--|{ landuse_primary : "code comes from"
lscape_landuse }|--|{ landuse_secondary : "code comes from"
landform }|--|{ landform_types : "code comes from"
landform }|--|{ measurement_types : "code comes from"
landform }|--|{ landform_elements : "code comes from"
landform }|--|{ landform_element_vmods : "code comes from"
landform }|--|{ landform_element_hmods : "code comes from"
landform ||--o{ lform_landuse : "may have some"
landform }|--|{ land_cover_types : "code comes from"
lform_landuse }|--|{ landuse_primary : "code comes from"
lform_landuse }|--|{ landuse_secondary : "code comes from"
lform_landuse }|--|{ landuse_tertiary : "code comes from"
lform_landuse }|--|{ landuse_impact : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
site ||--|| location : "has one"
location }|--|{ elevation_measurement_methods : "code comes from"
location }|--|{ gnss_types : "code comes from"
site ||--|| landscape : "has one associated"
landscape }|--|{ provinces : "code comes from"
landscape }|--|{ landscapes : "code comes from"
landscape ||--|{ landform : "contains at least one"
landscape ||--o{ lscape_landuse : "may have some"
lscape_landuse }|--|{ landuse_primary : "code comes from"
lscape_landuse }|--|{ landuse_secondary : "code comes from"
landform }|--|{ landform_types : "code comes from"
landform }|--|{ measurement_types : "code comes from"
landform }|--|{ landform_elements : "code comes from"
landform }|--|{ landform_element_vmods : "code comes from"
landform }|--|{ landform_element_hmods : "code comes from"
landform ||--o{ lform_landuse : "may have some"
landform }|--|{ land_cover_types : "code comes from"
lform_landuse }|--|{ landuse_primary : "code comes from"
lform_landuse }|--|{ landuse_secondary : "code comes from"
lform_landuse }|--|{ landuse_tertiary : "code comes from"
lform_landuse }|--|{ landuse_impact : "code comes from"
%% data %%
location {
id id_site PK
geo geo_point_site_xy "Section 3.1.1"
num[-5-3724] amt_elevation_abs_m "Section 3.1.1"
num[0-Inf] amt_error_location_m "Section 3.1.2"
num[0-Inf] amt_error_elevation_m "Section 3.1.2"
cat catu_elevation_method FK "Section 3.1.3"
cat catu_gnss FK "Section 3.1.4"
num[-5-3724] amt_elevation_rel_m "Section 3.2.1"
txt txt_notes_triangulation "Section 3.2.2"
txt txt_location_desc "Section 3.2.3"
%% not defining code tables for these just now %%
cat catu_admin_rc "Section 3.2.4"
cat catu_admin_dc "Section 3.2.4"
}
elevation_measurement_methods {
id id_elevation_method PK
cat catu_elevation_method FK "Table 3.1"
txt txt_elevation_method_name "Table 3.1"
}
gnss_types {
id id_gnss PK
cat catu_gnss FK "Table 3.2"
txt txt_gnss_name "Table 3.2"
txt txt_gnss_desc "Table 3.2"
}
%% data %%
landscape {
id id_site PK
cat catu_province FK "Section 4.1"
cat catu_landscape FK "Section 4.2.1.4"
%% landscape relief %%
num[0-Inf] amt_lscape_relief_med_m "Section 4.2.1.1"
%% landscape slope %%
num[0-90] amt_lscape_slope_med_d "Section 4.2.1.2"
num[0-90] amt_lscape_slope_min_d "Section 4.2.1.2"
num[0-90] amt_lscape_slope_max_d "Section 4.2.1.2"
%% not implementing stream channel stuff here %%
}
provinces {
id id_province PK
cat catu_province FK "Table 4.1"
txt txt_province_name "Table 4.1"
txt txt_province_desc "Note 4.1"
}
landscapes {
id id_landscape PK
cat catu_landscape FK "Table 4.2"
txt txt_landscape_name "Table 4.2"
txt txt_landscape_desc "Table 4.2"
}
%% data %%
lscape_landuse {
id id_site PK
id id_landscape PK
id id_lscape_landuse PK
cat catu_lscape_landuse_primary FK "Section 8.2.1"
cat catu_lscape_landuse_secondary FK "Section 8.2.1"
num[0-100] amt_landuse_area_p "Section 8.2.1"
}
landuse_primary {
id id_landuse_primary PK
cat catu_landuse_primary FK "Fig. 8.1"
txt txt_landuse_primary_name "Fig. 8.1"
}
landuse_secondary {
id id_landuse_primary PK
id id_landuse_secondary PK
cat catu_landuse_secondary FK "Fig. 8.1"
txt txt_landuse_secondary_name "Fig. 8.1"
}
%% data %%
landform {
id id_site PK
id id_landscape PK
id id_landform PK
cat catu_landform FK "Section 4.3.1"
num[0-100] amt_landform_p "Section 4.1.2.5"
%% can only be true for one landform per site/landscape: %%
bool ind_landform_contains_site
%% (other way is separate tables for site-landform and %%
%% landscape-landforms) %%
cat catu_land_cover FK "Section 8.1"
%% detailed optional stuff: %%
num[0-Inf] amt_landform_relief_m "Section 4.3.2.1"
num[0-90] amt_landform_slope_med_d "Section 4.3.2.2"
num[0-90] amt_landform_slope_min_d "Section 4.3.2.2"
num[0-90] amt_landform_slope_max_d "Section 4.3.2.2"
num[0-359] val_landform_aspect_med_d "Section 4.3.2.3"
num[0-359] val_landform_aspect_min_d "Section 4.3.2.3"
num[0-359] val_landform_aspect_max_d "Section 4.3.2.3"
cat catu_landform_aspect_rec FK "Section D.13.1"
cat catu_landform_element FK "Section 4.3.2.4"
cat catu_landform_element_vmod FK "Section 4.3.2.4"
cat catu_landform_element_hmod FK "Section 4.3.2.4"
%% extremely optional %%
num[0-Inf] amt_slope_length_m "Section 4.4.1"
num[-90-90] amt_mesoscale_topo_index "Section 4.4.2"
num[-90-90] amt_terrain_shape_index "Section 4.4.3"
}
landform_types {
id id_landform PK
cat catu_landform FK "Table 4.3"
txt txt_landform_name "Table 4.3"
txt txt_landform_desc "Table 4.3"
}
land_cover_types {
id id_land_cover PK
cat catu_land_cover FK "Table 8.1"
txt txt_land_cover_name "Table 8.1"
txt txt_land_cover_desc "Table 8.1"
}
measurement_types {
id id_mtype PK
cat catu_mtype FK "Table D.1.1"
txt txt_mtype_name "Table D.1.1"
}
landform_elements {
id id_landform_element PK
cat catu_landform_element FK "Table 4.4"
txt txt_landform_element_name "Table 4.4"
}
landform_element_vmods {
id id_landform_element_vmod PK
cat catu_landform_element_vmod FK "Table 4.5"
txt txt_landform_element_vmod_name "Table 4.5"
txt txt_landform_element_vmod_desc "Table 4.5"
}
landform_element_hmods {
id id_landform_element_hmod PK
cat catu_landform_element_hmod FK "Table 4.6"
txt txt_landform_element_hmod_name "Table 4.6"
txt txt_landform_element_hmod_desc "Table 4.6"
}
%% data %%
lform_landuse {
id id_site PK
id id_landform PK
id id_lform_landuse PK
cat catu_lform_landuse_tertiary FK "Section 8.2.2"
amt[0-100] amt_landuse_area_p "Section 8.2.2"
cat cato_landuse_impact "Section 8.2.2"
}
landuse_tertiary {
id id_landuse_secondary PK
id id_landuse_tertiary PK
cat catu_landuse_tertiary FK "Fig. 8.1"
txt txt_landuse_tertiary_name "Fig. 8.1"
}
landuse_impacts {
id id_landuse_impact PK
cat catu_landuse_impact FK "Table 8.2"
txt txt_landuse_impact_name "Table 8.2"
txt txt_landuse_impact_desc "Table 8.2"
}
D.3 Site overview
The site table stores reference data (Section 10.2), except for location.
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
site ||--|{ disturbances : "has one or more"
disturbances }|--|{ disturbance_types : "code comes from"
disturbances }|--|{ disturbance_ages : "code comes from"
site }|--|{ site_purposes : "code comes from"
site }|--|{ site_selection_methods : "code comes from"
site }|--|{ site_exposure_types : "code comes from"
site }|--|{ site_early_stop_types : "code comes from"
site }|--|{ current_weather : "code comes from"
site }|--|{ recent_rainfall : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
site ||--|{ disturbances : "has one or more"
disturbances }|--|{ disturbance_types : "code comes from"
disturbances }|--|{ disturbance_ages : "code comes from"
site }|--|{ site_purposes : "code comes from"
site }|--|{ site_selection_methods : "code comes from"
site }|--|{ site_exposure_types : "code comes from"
site }|--|{ site_early_stop_types : "code comes from"
site }|--|{ current_weather : "code comes from"
site }|--|{ recent_rainfall : "code comes from"
%% data %%
site {
id id_site PK "Section 10.2.1"
usr usr_author "Section 10.2.2"
dt dt_site "Section 10.2.4"
cat catu_site_purpose FK "Section 10.2.5"
cat catu_site_selection FK "Section 10.2.6"
cat catu_site_exposure FK "Section 10.2.7"
num[0-1000] amt_site_depth_max_cm "Section 10.2.8"
cat catu_site_early_stop FK "Section 10.2.9"
%% weather at visit %%
cat catu_weather FK "Section 6.3.1"
num[-50-50] val_temperature_c "Section 6.3.1"
cat cato_recent_rain FK "Section 6.3.2"
}
site_purposes {
id id_site_purpose PK
cat catu_site_purpose FK "Table 10.1"
txt txt_site_purpose_name "Table 10.1"
}
site_selection_methods {
id id_site_selection PK
cat catu_site_selection FK "Table 10.2"
txt txt_site_selection_name "Table 10.2"
}
site_exposure_types {
id id_site_exposure PK
cat catu_site_exposure FK "Table 10.3"
txt txt_site_exposure_name "Table 10.3"
}
site_early_stop_types {
id id_site_early_stop PK
cat catu_site_early_stop FK "Table 10.4"
txt txt_site_early_stop_name "Table 10.4"
txt txt_site_early_stop_desc "Table 10.4"
}
current_weather {
id id_weather PK
cat catu_weather FK "Table 6.3.1"
txt txt_weather_name "Table 6.3.1"
}
recent_rainfall {
id id_rainfall PK
cat catu_rainfall FK "Table 6.3.2"
txt txt_rainfall_name "Table 6.3.2"
}
%% data %%
disturbances {
id id_site PK
id id_disturbance PK
cat catu_disturbance FK "Section 10.3"
cat catu_disturbance_age FK "Section 10.3"
num[0-Inf] amt_disturbance_age_days "Section 10.3"
}
disturbance_types {
id id_disturbance PK
cat catu_disturbance FK "Table 10.5"
txt txt_disturbance_name "Table 10.5"
}
disturbance_ages {
id id_disturbance_age PK
cat catu_disturbance_age FK "Table 10.6"
txt txt_disturbance_age_name "Table 10.6"
}
D.4 Surface
The surface table stores slope, aspect, surface cover, surface water, surface cracking, microrelief, erosion and deposition data (Chapter 11). It can also store LUC vegetation code (Section 7.1). This table can optionally extend the site table (Section D.3) as it has a 1:1 relationship.
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
site_surface }|--|{ slope_methods : "code comes from"
site_surface }|--|{ measurement_types : "code comes from"
site_surface }|--|{ aspect_methods : "code comes from"
site_surface ||--|{ surface_cover : "has"
surface_cover }|--|{ surface_cover_types : "code comes from"
site_surface }|--|{ surface_water_types : "code comes from"
site_surface }|--|{ surface_water_persts : "code comes from"
site_surface }|--|{ surface_crack_types : "code comes from"
site_surface }|--|{ surface_crack_shapes : "code comes from"
site_surface ||--o{ site_erosion : "may have"
site_surface }|--|{ site_microrelief_nat : "code comes from"
site_surface }|--|{ site_microrelief_ant : "code comes from"
site_erosion }|--|{ erosion_types : "code comes from"
site_erosion }|--|{ erosion_severity : "code comes from"
site_erosion }|--|{ erosion_activity : "code comes from"
site_surface ||--o{ site_deposition : "may have"
site_deposition }|--|{ deposition_types : "code comes from"
site_deposition }|--|{ deposition_severity : "code comes from"
site_deposition }|--|{ deposition_activity : "code comes from"
site_surface }|--|{ water_depths : "code comes from"
site_surface }|--|{ luc_vegetation_codes : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
site_surface }|--|{ slope_methods : "code comes from"
site_surface }|--|{ measurement_types : "code comes from"
site_surface }|--|{ aspect_methods : "code comes from"
site_surface ||--|{ surface_cover : "has"
surface_cover }|--|{ surface_cover_types : "code comes from"
site_surface }|--|{ surface_water_types : "code comes from"
site_surface }|--|{ surface_water_persts : "code comes from"
site_surface }|--|{ surface_crack_types : "code comes from"
site_surface }|--|{ surface_crack_shapes : "code comes from"
site_surface ||--o{ site_erosion : "may have"
site_surface }|--|{ site_microrelief_nat : "code comes from"
site_surface }|--|{ site_microrelief_ant : "code comes from"
site_erosion }|--|{ erosion_types : "code comes from"
site_erosion }|--|{ erosion_severity : "code comes from"
site_erosion }|--|{ erosion_activity : "code comes from"
site_surface ||--o{ site_deposition : "may have"
site_deposition }|--|{ deposition_types : "code comes from"
site_deposition }|--|{ deposition_severity : "code comes from"
site_deposition }|--|{ deposition_activity : "code comes from"
site_surface }|--|{ water_depths : "code comes from"
site_surface }|--|{ luc_vegetation_codes : "code comes from"
%% data %%
site_surface {
id id_site PK "Section 10.2.1"
num[0-90] amt_slope_d "Section 11.1"
cat catu_slope_rec FK "Section D.13.1"
cat catu_slope_method FK "Section 11.1"
num[0-359] amt_aspect_d "Section 11.2"
cat catu_aspect_rec FK "Section D.13.1"
cat catu_aspect_method FK "Section 11.2"
cat catu_surface_water FK "Section 11.4"
cat cato_surface_water_perst FK "Section 11.4"
cat catu_surface_cracks FK "Section 11.5"
cat catu_surface_crack_shp FK "Section 11.5"
cat catu_microrelief_nat FK "Section 11.6.1"
%% optional details %%
num[0-Inf] amt_microrelief_nat_width_cm "Section 11.6.1"
num[0-Inf] amt_microrelief_nat_height_cm "Section 11.6.1"
cat catu_microrelief_ant FK "Section 11.6.2"
%% optional details %%
num[0-Inf] amt_microrelief_ant_width_cm "Section 11.6.1"
num[0-Inf] amt_microrelief_ant_height_cm "Section 11.6.1"
num[0-Inf] amt_depth_water_cm "Section 12.7"
cat catu_depth_water FK "Section 12.7"
%% vegetation could also be stored at landscape and/or landform %%
cat catu_vegetation_luc FK "Section 7.1"
}
slope_methods {
id id_slope_method PK
cat catu_slope_method FK "Table 11.1"
txt txt_slope_method_name "Table 11.1"
txt txt_slope_method_desc "Table 11.1"
}
aspect_methods {
id id_aspect_method PK
cat catu_aspect_method FK "Table 11.2"
txt txt_aspect_method_name "Table 11.2"
txt txt_aspect_method_desc "Table 11.2"
}
measurement_types {
id id_mtype PK
cat catu_mtype FK
txt txt_mtype_name "Table D.1"
txt txt_mtype_desc "Table D.1"
}
%% data %%
surface_cover {
id id_site PK
cat catu_surface_cover FK "Section 11.3"
num[0-100] amt_surface_cover_p "Section 11.3"
}
surface_cover_types {
id id_surface_cover PK
cat catu_surface_cover FK "Table 11.3"
txt txt_surface_cover_name "Table 11.3"
txt txt_surface_cover_desc "Table 11.3"
}
surface_water_types {
id id_surface_water PK
cat catu_surface_water FK "Table 11.4"
txt txt_surface_water_name "Table 11.4"
txt txt_surface_water_desc "Table 11.4"
}
surface_water_persts {
id id_surface_water_perst PK
cat catu_surface_water_perst FK "Table 11.5"
txt txt_surface_water_perst_name "Table 11.5"
txt txt_surface_water_perst_desc "Table 11.5"
}
surface_crack_types {
id id_surface_crack
cat catu_surface_crack "Table 11.6"
txt txt_surface_crack_name "Table 11.6"
txt txt_surface_crack_desc "Table 11.6"
}
surface_crack_shapes {
id id_surface_crack_shp PK
cat catu_surface_crack_shp FK "Table 11.7"
txt txt_surface_crack_shp_name "Table 11.7"
txt txt_surface_crack_shp_desc "Table 11.7"
}
site_microrelief_nat {
id id_microrelief_nat PK
cat catu_microrelief_nat FK "Table 11.8"
txt txt_microrelief_nat_name "Table 11.8"
txt txt_microrelief_nat_desc "Table 11.8"
}
site_microrelief_ant {
id id_microrelief_ant PK
cat catu_microrelief_ant FK "Table 11.9"
txt txt_microrelief_ant_name "Table 11.9"
txt txt_microrelief_ant_desc "Table 11.9"
}
%% data %%
site_erosion {
id id_site PK
id id_erosion PK
cat catu_erosion FK "Section 11.7"
cat cato_erosion_severity FK "Section 11.7"
cat cato_erosion_activity FK "Section 11.7"
}
erosion_types {
id id_erosion PK
cat catu_erosion FK "Table 11.10"
txt txt_erosion_name "Table 11.10"
txt txt_erosion_desc "Table 11.10"
}
erosion_severity {
id id_erosion_severity PK
cat catu_erosion_severity FK "Table 11.11"
txt txt_erosion_severity_name "Table 11.11"
txt txt_erosion_severity_desc "Table 11.11"
}
erosion_activity {
id id_erosion_activity PK
cat catu_erosion_activity FK "Table 11.12"
txt txt_erosion_activity_name "Table 11.12"
txt txt_erosion_activity_desc "Table 11.12"
}
%% data %%
site_deposition {
id id_site PK
id id_deposition PK
cat catu_deposition FK "Section 11.8"
cat cato_deposition_severity FK "Section 11.8"
cat cato_deposition_activity FK "Section 11.8"
}
deposition_types {
id id_deposition PK
cat catu_deposition FK "Table 11.13"
txt txt_deposition_name "Table 11.13"
txt txt_deposition_desc "Table 11.13"
}
deposition_severity {
id id_deposition_severity PK
cat catu_deposition_severity FK "Table 11.14"
txt txt_deposition_severity_name "Table 11.14"
txt txt_deposition_severity_desc "Table 11.14"
}
deposition_activity {
id id_deposition_activity PK
cat catu_deposition_activity FK "Table 11.15"
txt txt_deposition_activity_name "Table 11.15"
txt txt_deposition_activity_desc "Table 11.15"
}
water_depths {
id id_depth_water PK
cat catu_depth_water FK "Table 12.4"
txt txt_depth_water_name "Table 12.4"
txt txt_depth_water_desc "Table 12.4"
}
luc_vegetation_codes {
id id_vegetation_luc PK
cat catu_vegetation_luc FK "Section 7.1"
txt txt_vegetation_luc_name "Section 7.1"
txt txt_vegetation_luc_desc "Section 7.1"
}
D.5 Horizon overview
The horizon overview table stores boundary definitions, soil moisture status at the time of examination, and parent material origin.
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon }|--|{ boundary_shapes : "code comes from"
horizon }|--|{ boundary_widths : "code comes from"
horizon }|--|{ soil_moistures : "code comes from"
horizon }|--|{ nzsc_pmo : "code comes from"
horizon }|--|{ nzsc_pmo_mod : "code comes from"
horizon }|--|{ weathering : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon }|--|{ boundary_shapes : "code comes from"
horizon }|--|{ boundary_widths : "code comes from"
horizon }|--|{ soil_moistures : "code comes from"
horizon }|--|{ nzsc_pmo : "code comes from"
horizon }|--|{ nzsc_pmo_mod : "code comes from"
horizon }|--|{ weathering : "code comes from"
%% data %%
horizon {
id id_site PK
id id_horizon PK
num[0-Inf] amt_upper_depth_cm "Section 12.2"
num[0-Inf] amt_upper_depth_med_cm "Section 12.2"
num[0-Inf] amt_upper_depth_min_cm "Section 12.2"
num[0-Inf] amt_upper_depth_max_cm "Section 12.2"
num[0-Inf] amt_lower_depth_cm "Section 12.2"
num[0-Inf] amt_lower_depth_med_cm "Section 12.2"
num[0-Inf] amt_lower_depth_min_cm "Section 12.2"
num[0-Inf] amt_lower_depth_max_cm "Section 12.2"
cat catu_boundary_shape FK "Section 12.4"
num[0-Inf] amt_boundary_cm "Section 12.5"
cat cato_boundary_width FK "Section 12.5"
cat cato_soil_moisture FK "Section 12.6"
%% pmo %%
cat catu_parent_material_origin FK "Section 12.8"
cat catu_anthropic_origin_modifier FK "Section 12.8"
%% weathering %%
cat cato_horizon_weathering FK "Section 12.9"
}
boundary_shapes {
id id_boundary_shape PK
cat catu_boundary_shape FK "Table 12.1"
txt txt_boundary_shape_name "Table 12.1"
txt txt_boundary_shape_desc "Table 12.1"
}
boundary_widths {
id id_boundary_width PK
cat cato_boundary_width FK "Table 12.2"
txt txt_boundary_width_name "Table 12.2"
txt txt_boundary_width_desc "Table 12.2"
}
soil_moistures {
id id_soil_moisture PK
cat cato_soil_moisture FK "Table 12.3"
txt txt_soil_moisture_name "Table 12.3"
}
nzsc_pmo {
id id_pmo PK
cat catu_pmo FK "Table 24.5"
txt txt_pmo_name "Table 24.5"
txt txt_pmo_desc "Table 24.5"
}
nzsc_pmo_mod {
id id_pmo_mod PK
cat catu_pmo_mod FK "Table 12.5"
txt txt_pmo_mod_name "Table 12.5"
txt txt_pmo_mod_desc "Table 12.5"
}
weathering {
id id_weathering PK
cat catu_weathering FK "Table 12.6"
txt txt_weathering_name "Table 12.6"
txt txt_weathering_desc_rock "Table 12.6"
txt txt_weathering_desc_seds "Table 12.6"
}
D.6 Horizon architecture
The horizon architecture table stores data about pedality, fragments, roots, voids and pores. This table can optionally extend the horizon overview table (Section D.5) as it has a 1:1 relationship.
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon_arch ||--|{ horizon_structures : "may have"
horizon_structures }|--|{ structure_types : "code comes from"
horizon_structures }|--|{ structure_grades : "code comes from"
horizon_structures }|--|{ structure_shapes : "code comes from"
horizon_arch ||--|{ horizon_rock_fragments : "may have"
horizon_rock_fragments }|--|{ fragment_sizes_simple : "code comes from"
horizon_rock_fragments }|--|{ rock_fragment_shapes : "code comes from"
horizon_rock_fragments }|--|{ nzsc_lithologies : "code comes from"
horizon_arch ||--|{ horizon_other_fragments : "may have"
horizon_other_fragments }|--|{ fragment_sizes_simple : "code comes from"
horizon_other_fragments }|--|{ other_fragment_types : "code comes from"
horizon_arch ||--|{ horizon_artefacts : "may have"
horizon_artefacts }|--|{ fragment_sizes_simple : "code comes from"
horizon_artefacts }|--|{ artefact_types: "code comes from"
horizon_arch ||--|{ horizon_plastics : "may have"
horizon_plastics }|--|{ plastic_types: "code comes from"
horizon_plastics }|--|{ plastic_fixations: "code comes from"
horizon_plastics }|--|{ plastic_shapes: "code comes from"
horizon_plastics }|--|{ plastic_degradations: "code comes from"
horizon_arch ||--|{ horizon_roots : "may have"
horizon_arch }|--|{ root_positions: "code comes from"
horizon_roots }|--|{ root_positions: "code comes from"
horizon_roots }|--|{ root_sizes_simple: "code comes from"
horizon_arch ||--|{ horizon_voids : "may have"
horizon_voids }|--|{ void_types : "code comes from"
horizon_voids }|--|{ void_conns : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon_arch ||--|{ horizon_structures : "may have"
horizon_structures }|--|{ structure_types : "code comes from"
horizon_structures }|--|{ structure_grades : "code comes from"
horizon_structures }|--|{ structure_shapes : "code comes from"
horizon_arch ||--|{ horizon_rock_fragments : "may have"
horizon_rock_fragments }|--|{ fragment_sizes_simple : "code comes from"
horizon_rock_fragments }|--|{ rock_fragment_shapes : "code comes from"
horizon_rock_fragments }|--|{ nzsc_lithologies : "code comes from"
horizon_arch ||--|{ horizon_other_fragments : "may have"
horizon_other_fragments }|--|{ fragment_sizes_simple : "code comes from"
horizon_other_fragments }|--|{ other_fragment_types : "code comes from"
horizon_arch ||--|{ horizon_artefacts : "may have"
horizon_artefacts }|--|{ fragment_sizes_simple : "code comes from"
horizon_artefacts }|--|{ artefact_types: "code comes from"
horizon_arch ||--|{ horizon_plastics : "may have"
horizon_plastics }|--|{ plastic_types: "code comes from"
horizon_plastics }|--|{ plastic_fixations: "code comes from"
horizon_plastics }|--|{ plastic_shapes: "code comes from"
horizon_plastics }|--|{ plastic_degradations: "code comes from"
horizon_arch ||--|{ horizon_roots : "may have"
horizon_arch }|--|{ root_positions: "code comes from"
horizon_roots }|--|{ root_positions: "code comes from"
horizon_roots }|--|{ root_sizes_simple: "code comes from"
horizon_arch ||--|{ horizon_voids : "may have"
horizon_voids }|--|{ void_types : "code comes from"
horizon_voids }|--|{ void_conns : "code comes from"
horizon_arch {
id id_site PK
id id_horizon PK
%% structures are in subtable %%
%% all frags super rapid (record this OR the next three) %%
num[0-100] amt_total_frags_p "Section 4.2"
%% rock frags rapid (detailed in subtable) %%
num[0-100] amt_rockfrags_p "Section 13.2"
%% notrock frags rapid %%
num[0-100] amt_otherfrags_p "Section 13.2.1.1"
%% artefacts rapid %%
num[0-100] amt_artefacts_p "Section 13.2.1.2"
%% roots rapid %%
num[0-100] amt_total_roots_p "Section 13.3.1"
cat catu_total_roots_pos FK "Section 13.3.2"
%% void rapid %%
num[0-100] amt_total_voids_p "Section 13.4"
%% pores rapid %%
num[0-10] amt_total_pores_p "Section 13.5"
%% pores detailed (could also spin out to subtable) %%
num[0-10] amt_macropores_p "Section 13.5"
num[0-10] amt_micropores_p "Section 13.5"
}
structure_types {
id id_structure PK
cat catu_structure FK "Table 13.1"
txt txt_structure_name "Table 13.1"
txt txt_structure_desc "Table 13.1"
}
structure_grades {
id id_structure_grade PK
cat cato_structure_grade FK "Table 13.2"
txt txt_structure_grade_name "Table 13.2"
txt txt_structure_grade_desc "Table 13.2"
}
structure_shapes {
id id_structure_shape PK
cat catu_structure_shape FK "Table 13.3"
txt txt_structure_shape_name "Table 13.3"
txt txt_structure_shape_desc "Table 13.3"
}
structure_sizes_rapid {
id id_structure_size_rapid PK "Section 13.1.4"
cat cato_structure_size_rapid FK "C, F"
txt txt_structure_size_rapid_name "Coarse, Fine"
}
%% data %%
horizon_structures {
id id_site PK
id id_horizon PK
id id_structure PK
cat catu_structure_type FK "Section 13.1.1"
cat cato_structure_grade FK "Section 13.1.2"
cat cato_structure_shape FK "Section 13.1.3"
%% rapid size %%
cat cato_structure_size FK "Section 13.1.4"
%% routine size %%
num[0-Inf] amt_structure_size_med_mm "Section 13.1.4"
%% add for detailed size %%
num[0-Inf] amt_structure_size_min_mm "Section 13.1.4"
num[0-Inf] amt_structure_size_max_mm "Section 13.1.4"
}
%% data %%
horizon_rock_fragments {
id id_site PK
id id_horizon PK
id id_rock_fragment PK
cat cato_rock_fragment_size FK "Section 13.2.1"
cat catu_rock_fragment_shape FK "Section 13.2.1"
num[0-100] amt_rock_fragment_p
cat catu_rock_fragment_lithology FK "Section 24.1.2"
}
fragment_sizes_simple {
id id_rockfrag_size PK
cat catu_rockfrag_size FK "Table 13.4"
txt txt_rockfrag_size_name "Table 13.4"
txt txt_rockfrag_size_desc "Table 13.4"
}
rock_fragment_shapes {
id id_rockfrag_shape PK
cat catu_rockfrag_shape FK "Table 13.5"
txt txt_rockfrag_shape_name "Table 13.5"
txt txt_rockfrag_shape_desc "Table 13.5"
}
nzsc_lithologies {
id id_nzsc_lithology PK
cat catu_nzsc_lithology FK "Table 24.2"
txt txt_nzsc_lithology_name "Table 24.2"
}
%% data %%
horizon_other_fragments {
id id_site PK
id id_horizon PK
id id_other_fragment PK
cat cato_other_fragment_size FK "Table 13.4"
cat cato_other_fragment FK "Table 13.6"
num[0-100] amt_rock_fragment_p "Section 13.2.1"
}
other_fragment_types {
id id_other_fragment PK
cat cato_other_fragment FK "Table 13.6"
txt txt_other_fragment_name "Table 13.6"
txt txt_other_fragment_desc "Table 13.6"
}
%% data %%
horizon_artefacts {
id id_site PK
id id_horizon PK
id id_artefact PK
cat cato_artefact_size FK "Table 13.4"
cat cato_artefact FK "Table 13.7"
num[0-100] amt_artefact_p "Section 13.2.2"
}
artefact_types {
id id_artefact PK
cat catu_artefact FK "Table 13.7"
txt txt_artefact_type_name "Table 13.7"
txt txt_artefact_type_desc "Table 13.7"
}
%% %%
horizon_plastics {
id id_site PK
id id_horizon PK
id id_plastic PK
cat catu_plastic FK "Table 13.8"
cat cato_plastic_fixation "Table 13.9"
cat catu_plastic_shape "Table 13.10"
cat cato_plastic_degradation "Table 13.11"
num[0-100] amt_plastic_p "Section 13.2.4"
}
plastic_types {
id id_plastic PK
cat catu_plastic FK "Table 13.8"
txt txt_plastic_name "Table 13.8"
txt txt_plastie_desc "Table 13.8"
}
plastic_fixations {
id id_plastic_fixation PK
cat catu_plastic_fixation FK "Table 13.9"
txt txt_plastic_fixation_name "Table 13.9"
txt txt_plastic_fixation_desc "Table 13.9"
}
plastic_shapes {
id id_plastic_shape PK
cat catu_plastic_shape FK "Table 13.10"
txt txt_plastic_shape_name "Table 13.10"
txt txt_plastic_shape_desc "Table 13.10"
}
plastic_degradations {
id id_plastic_degradations PK
cat catu_plastic_degradations FK "Table 13.11"
txt txt_plastic_degradations_name "Table 13.11"
txt txt_plastic_degradations_desc "Table 13.11"
}
%% data %%
horizon_roots {
id id_site PK
id id_horizon PK
id id_root PK
cat cato_root_size FK "Table 13.12"
cat cato_root_pos FK "Table 13.13"
num[0-100] amt_root_p "Section 13.3.1"
}
root_sizes_simple {
id id_root_size PK
cat catu_root_size FK "Table 13.12"
txt txt_root_size_name "Table 13.12"
txt txt_root_size_desc "Table 13.12"
}
root_positions {
id id_root_pos PK
cat catu_root_pos FK "Table 13.13"
txt txt_root_pos_name "Table 13.13"
txt txt_root_pos_desc "Table 13.13"
}
%% data %%
horizon_voids {
id id_site PK
id id_horizon PK
id id_void PK
cat catu_void FK "Table 13.14"
cat cato_void_conn FK "Table 13.15"
num[0-100] amt_void_p "Section 13.4"
}
void_types {
id id_void PK
cat catu_void FK "Table 13.14"
txt txt_void_name "Table 13.14"
txt txt_void_desc "Table 13.14"
}
void_conns {
id id_void_conn PK
cat cato_void_conn FK "Table 13.15"
txt txt_void_conn_name "Table 13.15"
txt txt_void_conn_desc "Table 13.15"
}
D.7 Horizon colour
These tables store matrix colours (Section 14.2) and colour patterns (Section 14.3).
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon ||--|{ horizon_matrix_colours : "has one or more"
horizon ||--|{ horizon_colour_patterns : "has one or more"
horizon_matrix_colours }|--|{ munsell_colours : "code comes from"
horizon_colour_patterns }|--|{ munsell_colours : "code comes from"
horizon_colour_patterns }|--|{ short_colours : "code comes from"
horizon_colour_patterns }|--|{ pattern_types : "code comes from"
horizon_colour_patterns }|--|{ pattern_shapes : "code comes from"
horizon_colour_patterns }|--|{ pattern_contrasts : "code comes from"
horizon_colour_patterns }|--|{ pattern_edges : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon ||--|{ horizon_matrix_colours : "has one or more"
horizon ||--|{ horizon_colour_patterns : "has one or more"
horizon_matrix_colours }|--|{ munsell_colours : "code comes from"
horizon_colour_patterns }|--|{ munsell_colours : "code comes from"
horizon_colour_patterns }|--|{ short_colours : "code comes from"
horizon_colour_patterns }|--|{ pattern_types : "code comes from"
horizon_colour_patterns }|--|{ pattern_shapes : "code comes from"
horizon_colour_patterns }|--|{ pattern_contrasts : "code comes from"
horizon_colour_patterns }|--|{ pattern_edges : "code comes from"
%% data %%
horizon_matrix_colours {
id id_site PK
id id_horizon PK
id id_colour_matrix PK "Section 14.2"
%% can also split into hue/value/chroma with subtables %%
cat cato_matrix_munsell "Section 14.2"
%% no simple colours on the matrix!! %%
cat catu_colour_moisture "Section 14.2"
}
%% data %%
horizon_colour_patterns {
id id_site PK
id id_horizon PK
id id_colour_pattern PK
%% either %%
cat cato_pattern_munsell FK "Section 14.2"
%% or, %%
cat catu_pattern_short_colour FK "Section 1r.1.1"
cat catu_pattern_type FK "Section 14.3.1"
num[0-100] amt_pattern_p "Section 14.3.2"
cat catu_pattern_shape FK "Section 14.3.3"
num[0-Inf] amt_pattern_size_mm "Section 14.3.4"
cat cato_pattern_contrast FK "Section 14.3.5"
cat cato_pattern_edge FK "Section 14.3.6"
}
munsell_colours {
id id_munsell PK
cat cato_munsell "See charts for allowable values"
%% optional but handy: %%
txt txt_colour_hex
}
short_colours {
id id_colour PK
cat catu_colour FK "Table 14.2"
txt txt_colour_name "Table 14.2"
txt txt_colour_desc "Table 14.2"
}
colour_moistures {
id id_colour_moisture
cat cato_colour_moisture "M or D"
txt txt_colour_moisture_name "Moist or Dry"
}
pattern_types {
id id_pattern PK
cat catu_pattern FK "Table 14.3"
txt txt_pattern_name "Table 14.3"
txt txt_pattern_desc "Table 14.3"
}
pattern_shapes {
id id_pattern_shape PK
cat catu_pattern_shape FK "Table 14.4"
txt txt_pattern_shape_name "Table 14.4"
txt txt_pattern_shape_desc "Table 14.4"
}
pattern_contrasts {
id id_pattern_contrast PK
cat catu_pattern_contrast FK "Table 14.5"
txt txt_pattern_contrast_name "Table 14.5"
txt txt_pattern_contrast_desc "Table 14.5"
}
pattern_edges {
id id_pattern_edge PK
cat catu_pattern_edge FK "Table 14.6"
txt txt_pattern_edge_name "Table 14.6"
txt txt_pattern_edge_desc "Table 14.6"
}
D.8 Horizon texture
The horizon texture table stores texture category as well as field estimates of sand and clay, and optionally lithology and mineralogy of the fine earth fraction. This table can optionally extend the horizon overview table (Section D.5) as it has a 1:1 relationship.
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon_texture }|--|{ simple_textures : "code comes from"
horizon_texture }|--|{ mineral_textures : "code comes from"
horizon_texture }|--|{ sand_sizes : "code comes from"
horizon_texture }|--|{ organic_levels : "code comes from"
horizon_texture }|--|{ organic_types : "code comes from"
horizon_texture }|--|{ organic_decomps : "code comes from"
horizon_texture }|--|{ nzsc_lithologies : "code comes from"
horizon_texture }|--|{ nzsc_mineralogies : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon_texture }|--|{ simple_textures : "code comes from"
horizon_texture }|--|{ mineral_textures : "code comes from"
horizon_texture }|--|{ sand_sizes : "code comes from"
horizon_texture }|--|{ organic_levels : "code comes from"
horizon_texture }|--|{ organic_types : "code comes from"
horizon_texture }|--|{ organic_decomps : "code comes from"
horizon_texture }|--|{ nzsc_lithologies : "code comes from"
horizon_texture }|--|{ nzsc_mineralogies : "code comes from"
%% data %%
horizon_texture {
id id_site PK
id id_horizon PK
cat cato_texture_simple FK "Section 15.1.1"
cat cato_texture FK "Section 15.1"
cat cato_texture_sandmod FK "Section 15.2.1"
cat catu_texture_orgmod FK "Section 15.2.2"
cat cato_organic_type FK "Section 15.4"
cat catu_organic_decomp FK "Section 15.4.1"
cat catu_organic_minmod FK "Section 15.4.2"
num[0-100] amt_sand_est_med_p "Section 15.3"
num[0-100] amt_sand_est_min_p "Section 15.3"
num[0-100] amt_sand_est_max_p "Section 15.3"
num[0-100] amt_clay_est_med_p "Section 15.3"
num[0-100] amt_clay_est_min_p "Section 15.3"
num[0-100] amt_clay_est_max_p "Section 15.3"
cat catu_lithology "Section 5.2.2"
cat catu_mineralogy "Section 5.3"
}
simple_textures {
id id_simple_texture PK
cat cato_simple_texture FK "Table 15.3"
txt txt_simple_texture_name "Table 15.3"
txt txt_simple_texture_desc "Table 15.3"
}
mineral_textures {
id id_texture PK
cat cato_texture FK "Table 15.2"
txt txt_texture_name "Table 15.2"
txt txt_texture_desc "Table 15.2"
}
sand_sizes {
id id_sand_size PK
cat catu_sand_size FK "Table 15.4"
txt txt_sand_size_name "Table 15.4"
txt txt_sand_size_desc "Table 15.4"
}
organic_levels {
id id_organic PK
cat catu_organic FK "Table 15.5"
txt txt_organic_name "Table 15.5"
txt txt_organic_desc "Table 15.5"
}
organic_types {
id id_organic_type PK
cat catu_organic_type FK "Table 15.6"
txt txt_organic_type_name "Table 15.6"
txt txt_organic_type_desc "Table 15.6"
}
organic_decomps {
id id_organic_decomp PK
cat catu_organic_decomp FK "Table 15.7"
txt txt_organic_decomp_name "Table 15.7"
txt txt_organic_decomp_desc "Table 15.7"
}
nzsc_lithologies {
id id_nzsc_lithology PK
cat catu_nzsc_lithology FK "Table 24.2"
txt txt_nzsc_lithology_name "Table 24.2"
}
nzsc_mineralogies {
id id_nzsc_mineralogy PK
cat catu_nzsc_mineralogy FK "Table 5.2"
txt txt_nzsc_mineralogy_name "Table 5.2"
txt txt_nzsc_mineralogy_desc "Table 5.2"
}
D.9 Horizon secondary features
The secondary features tables store essential information in a parent table and more detailed optional descriptions in child tables. The parent table can optionally extend the horizon overview table (Section D.5) as it has a 1:1 relationship.
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon_secondary_features ||--o{ "horizon_concentrations" : "may have some"
horizon_secondary_features ||--o{ "horizon_coatings" : "may have some"
horizon_secondary_features ||--o{ "horizon_bio_features" : "may have some"
horizon_secondary_features }|--|{ concentration_forms : "code comes from"
horizon_secondary_features }|--|{ concentration_forms : "code comes from"
horizon_secondary_features }|--|{ concentration_types : "code comes from"
horizon_secondary_features }|--|{ pan_types : "code comes from"
horizon_secondary_features }|--|{ pan_continuities : "code comes from"
horizon_secondary_features }|--|{ pan_structures : "code comes from"
horizon_secondary_features }|--|{ stress_types : "code comes from"
horizon_concentrations }|--|{ concentration_forms : "code comes from"
horizon_concentrations }|--|{ concentration_types : "code comes from"
horizon_coatings }|--|{ coating_types : "code comes from"
horizon_bio_features }|--|{ bio_feature_types : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon_secondary_features ||--o{ "horizon_concentrations" : "may have some"
horizon_secondary_features ||--o{ "horizon_coatings" : "may have some"
horizon_secondary_features ||--o{ "horizon_bio_features" : "may have some"
horizon_secondary_features }|--|{ concentration_forms : "code comes from"
horizon_secondary_features }|--|{ concentration_forms : "code comes from"
horizon_secondary_features }|--|{ concentration_types : "code comes from"
horizon_secondary_features }|--|{ pan_types : "code comes from"
horizon_secondary_features }|--|{ pan_continuities : "code comes from"
horizon_secondary_features }|--|{ pan_structures : "code comes from"
horizon_secondary_features }|--|{ stress_types : "code comes from"
horizon_concentrations }|--|{ concentration_forms : "code comes from"
horizon_concentrations }|--|{ concentration_types : "code comes from"
horizon_coatings }|--|{ coating_types : "code comes from"
horizon_bio_features }|--|{ bio_feature_types : "code comes from"
%% data %%
horizon_secondary_features {
id id_site PK
id id_horizon PK
%% concentrations_rapid %%
cat catu_concentration_form FK "Section 16.1"
num[0-100] amt_concentration_p "Section 16.1"
%% coatings rapid still has to go in a subtable, see below %%
%% rapid pans (you can only have one) %%
cat catu_pan_type FK "Section 16.3"
%% add this stuff for details %%
cat cato_pan_continuity FK "Section 16.3"
cat catu_pan_structure FK "Section 16.3"
%% stress features (NB you get one or the other because Physics) %%
cat catu_stress_type FK "Section 16.4"
num[0-100] amt_stress_p "Section 16.4"
}
%% data %%
horizon_concentrations {
id id_site PK
id id_horizon PK
id id_concentration PK
cat catu_concentration_form FK "Table 16.1"
cat catu_concentration_type FK "Table 16.2"
num[0-Inf] amt_concentration_size_med_mm "Section 16.1"
num[0-Inf] amt_concentration_size_min_mm "Section 16.1"
num[0-Inf] amt_concentration_size_max_mm "Section 16.1"
num[0-100] amt_concentration_med_p "Section 16.1"
num[0-100] amt_concentration_min_p "Section 16.1"
num[0-100] amt_concentration_max_p "Section 16.1"
}
concentration_forms {
id id_conc_form PK
cat catu_conc_form FK "Table 16.1"
txt txt_conc_form_name "Table 16.1"
txt txt_conc_form_desc "Table 16.1"
}
concentration_types {
id id_conc PK
cat catu_conc FK "Table 16.2"
txt txt_conc_form_name "Table 16.2"
txt txt_conc_form_desc "Table 16.2"
}
pan_types {
id id_pan PK
cat catu_pan FK "Table 16.4"
txt txt_pan_name "Table 16.4"
txt txt_pan_desc "Table 16.4"
}
pan_continuities {
id id_pan_cont PK
cat catu_pan_cont FK "Table 16.5"
txt txt_pan_cont_name "Table 16.5"
txt txt_pan_cont_desc "Table 16.5"
}
pan_structures {
id id_pan_structure PK
cat catu_pan_structure FK "Table 16.6"
txt txt_pan_structure_name "Table 16.6"
txt txt_pan_structure_desc "Table 16.6"
}
stress_types {
id id_stress PK
cat catu_stress FK "Table 16.7"
txt txt_stress_name "Table 16.7"
txt txt_stress_desc "Table 16.7"
}
%% data %%
horizon_coatings {
id id_site PK
id id_horizon PK
id id_coating PK
cat catu_coating FK "Section 16.2"
num[0-100] amt_coating_p "Section 16.2"
}
coating_types {
id id_coating PK
cat catu_coating FK "Table 16.3"
txt txt_coating_name "Table 16.3"
txt txt_coating_desc "Table 16.3"
}
%% data %%
horizon_bio_features {
id id_site PK
id id_horizon PK
id id_bio_feature PK
cat catu_bio_feature FK "Section 16.5"
num[0-100] amt_bio_feature_p "Section 16.5"
}
bio_feature_types {
id id_bio_feature PK
cat catu_bio_feature FK "Table 16.8"
txt txt_bio_feature_name "Table 16.8"
txt txt_bio_feature_desc "Table 16.8"
}
D.10 Horizon consistence tests
The consistence tests table can optionally extend the horizon overview table (Section D.5) as it has a 1:1 relationship.
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
consistence_tests }|--|{ strengths : "code comes from"
consistence_tests }|--|{ sensitivities : "code comes from"
consistence_tests }|--|{ plasticities : "code comes from"
consistence_tests }|--|{ stickinesses : "code comes from"
consistence_tests }|--|{ penrests : "code comes from"
consistence_tests }|--|{ packings : "code comes from"
consistence_tests }|--|{ failures : "code comes from"
consistence_tests }|--|{ fluidities : "code comes from"
consistence_tests }|--|{ indurations : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
consistence_tests }|--|{ strengths : "code comes from"
consistence_tests }|--|{ sensitivities : "code comes from"
consistence_tests }|--|{ plasticities : "code comes from"
consistence_tests }|--|{ stickinesses : "code comes from"
consistence_tests }|--|{ penrests : "code comes from"
consistence_tests }|--|{ packings : "code comes from"
consistence_tests }|--|{ failures : "code comes from"
consistence_tests }|--|{ fluidities : "code comes from"
consistence_tests }|--|{ indurations : "code comes from"
consistence_tests {
id id_site PK
id id_horizon PK
bool ind_cohesive "Section 17.1.1"
cat cato_strength_unconfined FK "Section 17.2.1"
cat cato_strength_ped FK "Section 17.2.2"
cat cato_strength_remoulded FK "Section 17.2.3"
cat cato_strength_plastic FK "Section 17.2.4"
cat cato_sensitivity FK "Section 17.3"
cat cato_plasticity FK "Section 17.4"
cat cato_stickiness FK "Section 17.5"
bool ind_dilatent "Section 17.6"
cat cato_penetration_rest FK "Section 17.7"
cat cato_packing FK "Section 17.8"
cat catu_failure FK "Section 17.9"
cat cato_fluidity FK "Section 17.10"
cat cato_induration FK "Section 17.11"
}
strengths {
id id_strength PK
cat catu_strength FK "Table 17.1"
txt txt_strength_name "Table 17.1"
txt txt_strength_desc "Table 17.1"
}
sensitivities {
id id_sensitivity PK
cat catu_sensitivity FK "Table 17.2"
txt txt_sensitivity_name "Table 17.2"
txt txt_sensitivity_desc "Table 17.2"
}
plasticities {
id id_plasticity PK
cat catu_plasticity FK "Table 17.3"
txt txt_plasticity_name "Table 17.3"
txt txt_plasticity_desc "Table 17.3"
}
stickinesses {
id id_stickiness PK
cat catu_stickiness FK "Table 17.4"
txt txt_stickiness_name "Table 17.4"
txt txt_stickiness_desc "Table 17.4"
}
penrests {
id id_penrest PK
cat catu_penrest FK "Table 17.5"
txt txt_penrest_name "Table 17.5"
txt txt_penrest_desc "Table 17.5"
}
packings {
id id_packing PK
cat catu_packing FK "Table 17.6"
txt txt_packing_name "Table 17.6"
txt txt_packing_desc "Table 17.6"
}
failures {
id id_failure PK
cat catu_failure FK "Table 17.7"
txt txt_failure_name "Table 17.7"
txt txt_failure_desc "Table 17.7"
}
fluidities {
id id_fluidity PK
cat catu_fluidity FK "Table 17.8"
txt txt_fluidity_name "Table 17.8"
txt txt_fluidity_desc "Table 17.8"
}
indurations {
id id_induration PK
cat cato_induration FK "Table 17.9"
txt txt_induration_name "Table 17.9"
txt txt_induration_desc "Table 17.9"
}
D.11 Field tests
Field tests are conducted from samples taken at set depths so in this example are connected to sites rather than horizons. It is also possible to nest them within horizons.
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
field_tests }|--|{ pH_raupachs : "code comes from"
field_tests }|--|{ h2o2_rxns : "code comes from"
field_tests }|--|{ hcl_rxns : "code comes from"
field_tests }|--|{ naf_rxns : "code comes from"
field_tests }|--|{ slaking_rxns : "code comes from"
field_tests }|--|{ dispersion_rxns : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
field_tests }|--|{ pH_raupachs : "code comes from"
field_tests }|--|{ h2o2_rxns : "code comes from"
field_tests }|--|{ hcl_rxns : "code comes from"
field_tests }|--|{ naf_rxns : "code comes from"
field_tests }|--|{ slaking_rxns : "code comes from"
field_tests }|--|{ dispersion_rxns : "code comes from"
field_tests {
id id_site PK
num[0-Inf] amt_test_depth_cm
cat cato_pH_raupach FK "Section 18.1.1"
num[0-14] val_pH_water "Section 18.1.2"
num[0-14] val_pH_h2o2 "Section 18.1.3"
cat cato_h2o2_rxn FK "Section 18.1.3"
num[0-Inf] amt_ec_dSm "Section 18.1.4"
cat cato_hcl_rxn FK "Section 18.1.5"
bool ind_mn_rxn "Section 18.1.6"
cat cato_naf_rxn FK "Section 18.1.7"
bool ind_22bip_rxn "Section 18.1.8"
bool ind_water_repellent "Section 18.1.9"
cat cato_slaking FK "Section 18.1.10"
cat cato_dispersion FK "Section 18.1.10"
}
pH_raupachs {
id id_raupach PK
cat catu_raupach FK "2.0, 2.5 etc"
}
h2o2_rxns {
id id_h2o2_rxn PK
cat catu_h2o2_rxn FK "Table 18.1"
txt txt_h2o2_rxn_name "Table 18.1"
txt txt_h2o2_rxn_desc "Table 18.1"
}
hcl_rxns {
id id_hcl_rxn PK
cat catu_hcl_rxn FK "Table 18.2"
txt txt_hcl_rxn_name "Table 18.2"
txt txt_hcl_rxn_desc "Table 18.2"
}
naf_rxns {
id id_naf_rxn PK
cat catu_naf_rxn FK "Table 18.3"
txt txt_naf_rxn_name "Table 18.3"
txt txt_naf_rxn_desc "Table 18.3"
}
slaking_rxns {
id id_slaking_rxn PK
cat catu_slaking_rxn FK "Table 18.4"
txt txt_slaking_rxn_name "Table 18.4"
txt txt_slaking_rxn_desc "Table 18.4"
}
dispersion_rxns {
id id_dispersion_rxn PK
cat catu_dispersion_rxn FK "Table 18.5"
txt txt_dispersion_rxn_name "Table 18.5"
txt txt_dispersion_rxn_desc "Table 18.5"
}
D.12 Interpretation data
D.12.1 NZSC
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
nzsc }|--|{ nzsc_orders : "code comes from"
nzsc }|--|{ nzsc_groups : "code comes from"
nzsc }|--|{ nzsc_subgroups : "code comes from"
nzsc }|--|{ nzsc_fam_profile_materials : "code comes from"
nzsc }|--|{ nzsc_lithologies : "code comes from"
nzsc }|--|{ nzsc_fam_textures : "code comes from"
nzsc }|--|{ nzsc_fam_permeabilities : "code comes from"
nzsc }|--|{ nzsc_fam_pm_origins : "code comes from"
nzsc }|--|{ nzsc_sib_depths : "code comes from"
nzsc }|--|{ nzsc_sib_tsstones : "code comes from"
nzsc }|--|{ nzsc_sib_textures : "code comes from"
nzsc }|--|{ nzsc_sib_drainages : "code comes from"
nzsc }|--|{ nzsc_sib_drainage_mods : "code comes from"
nzsc }|--|{ rooting_barriers : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
nzsc }|--|{ nzsc_orders : "code comes from"
nzsc }|--|{ nzsc_groups : "code comes from"
nzsc }|--|{ nzsc_subgroups : "code comes from"
nzsc }|--|{ nzsc_fam_profile_materials : "code comes from"
nzsc }|--|{ nzsc_lithologies : "code comes from"
nzsc }|--|{ nzsc_fam_textures : "code comes from"
nzsc }|--|{ nzsc_fam_permeabilities : "code comes from"
nzsc }|--|{ nzsc_pm_origins : "code comes from"
nzsc }|--|{ nzsc_sib_depths : "code comes from"
nzsc }|--|{ nzsc_sib_tsstones : "code comes from"
nzsc }|--|{ nzsc_sib_textures : "code comes from"
nzsc }|--|{ nzsc_sib_drainages : "code comes from"
nzsc }|--|{ drainage_mods : "code comes from"
nzsc }|--|{ rooting_barriers : "code comes from"
nzsc {
id id_site PK
cat catu_nzsc_order FK "Hewitt et al, 2024"
cat catu_nzsc_group FK "Hewitt et al, 2024"
cat catu_nzsc_subgroup FK "Hewitt et al, 2024"
cat catu_nzsc_fam_profile_material FK "Section 24.1.1"
cat catu_nzsc_fam_stones_rock FK "Section 24.1.2"
cat catu_nzsc_fam_fines_rock FK "Section 24.1.2"
cat catu_nzsc_fam_texture FK "Section 24.1.3"
cat catu_nzsc_fam_permeability FK "Section 24.1.4"
cat catu_nzsc_fam_pm_origin FK "Section 24.1.5"
cat catu_nzsc_sib_depth FK "Section 24.2.1"
cat catu_nzsc_sib_tsstone FK "Section 24.2.2"
cat catu_nzsc_sib_texture FK "Section 24.2.3"
cat catu_nzsc_sib_drainage FK "Section 24.2.4"
cat catu_nzsc_sib_drainage_mod FK "Section 24.2.4.1"
cat catu_rooting_barrier FK "Section 24.3"
int[0-Inf] val_rooting_barrier_horizon "Section 24.3.1"
}
nzsc_orders {
id id_nzsc_order PK "Hewitt et al, 2024"
cat catu_nzsc_order FK "Hewitt et al, 2024"
txt txt_nzsc_order_name "Hewitt et al, 2024"
}
nzsc_groups {
id id_nzsc_group PK "Hewitt et al, 2024"
cat catu_nzsc_group FK "Hewitt et al, 2024"
txt txt_nzsc_group_name "Hewitt et al, 2024"
}
nzsc_subgroups {
id id_nzsc_subgroup PK "Hewitt et al, 2024"
cat catu_nzsc_subgroup FK "Hewitt et al, 2024"
txt txt_nzsc_subgroup_name "Hewitt et al, 2024"
}
nzsc_fam_profile_materials {
id id_nzsc_fam_pm PK
cat catu_nzsc_fam_pm FK "Table 24.1"
txt txt_nzsc_fam_pm_name "Table 24.1"
txt txt_nzsc_fam_pm_desc "Table 24.1"
}
nzsc_lithologies {
id id_nzsc_lithology PK
cat catu_nzsc_lithology FK "Table 24.2"
txt txt_nzsc_lithology_name "Table 24.2"
}
nzsc_fam_textures {
id id_nzsc_fam_texture PK
cat catu_nzsc_fam_texture FK "Table 24.3"
txt txt_nzsc_fam_texture_name "Table 24.3"
}
nzsc_fam_permeabilities {
id id_nzsc_fam_perm PK
cat catu_nzsc_fam_perm FK "Table 24.4"
txt txt_nzsc_fam_perm_name "Table 24.4"
txt txt_nzsc_fam_perm_desc "Table 24.4"
}
nzsc_pm_origins {
id id_nzsc_pm_origin PK
cat catu_nzsc_pm_origin FK "Table 24.5"
txt txt_nzsc_pm_origin_name "Table 24.5"
txt txt_nzsc_pm_origin_desc "Table 24.5"
}
nzsc_sib_depths {
id id_nzsc_sib_depth PK
cat catu_nzsc_sib_perm FK "Table 24.6"
txt txt_nzsc_sib_perm_name "Table 24.6"
txt txt_nzsc_sib_perm_desc "Table 24.6"
}
nzsc_sib_tsstones {
id id_nzsc_sib_tsstone PK
cat catu_nzsc_sib_tsstone FK "Table 24.7"
txt txt_nzsc_sib_tsstone_name "Table 24.7"
txt txt_nzsc_sib_tsstone_desc "Table 24.7"
}
nzsc_sib_textures {
id id_nzsc_sib_texture PK
cat catu_nzsc_sib_texture FK "Table 24.8"
txt txt_nzsc_sib_texture_name "Table 24.8"
}
nzsc_sib_drainages {
id id_nzsc_sib_drainage PK
cat catu_nzsc_sib_drainage FK "Table 24.9"
txt txt_nzsc_sib_drainage_name "Table 24.9"
txt txt_nzsc_sib_drainage_desc "Table 24.9"
}
drainage_mods {
id id_drainage_mod PK
cat catu_drainage_mod FK "Table 24.10"
txt txt_drainage_mod_name "Table 24.10"
txt txt_drainage_mod_desc "Table 24.10"
}
rooting_barriers {
id id_rooting_barrier PK
cat catu_rooting_barrier FK "Table 24.11/12"
txt txt_rooting_barrier_name "Table 24.11/12"
txt txt_rooting_barrier_desc "Table 24.11/12"
}
D.12.2 Horizon names
The horizon names tables stores conventional and functional names. This table can optionally extend the horizon overview table as it has a 1:1 relationship (Section D.5). Horizon drainage class is also stored here.
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon_names }|--|{ horizon_names_primary : "code comes from"
horizon_names }|--|{ horizon_names_secondary : "code comes from"
horizon_names }|--|{ horizon_suffix_redox : "code comes from"
horizon_names ||--o{ horizon_names_suffix_other : "may have some"
horizon_names_suffix_other }|--|{ horizon_suffix_other : "code comes from"
horizon_names }|--|{ horizon_names_functional : "code comes from"
horizon_names }|--|{ functional_horizon_permeabilities : "code comes from"
horizon_names }|--|{ horizon_drainage_classes : "code comes from"
%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
horizon_names }|--|{ horizon_names_primary : "code comes from"
horizon_names }|--|{ horizon_names_secondary : "code comes from"
horizon_names }|--|{ horizon_suffix_redox : "code comes from"
horizon_names ||--o{ horizon_names_suffix_other : "may have some"
horizon_names_suffix_other }|--|{ horizon_suffix_other : "code comes from"
horizon_names }|--|{ horizon_names_functional : "code comes from"
horizon_names }|--|{ functional_horizon_permeabilities : "code comes from"
horizon_names }|--|{ horizon_drainage_classes : "code comes from"
%% data %%
horizon_names {
id id_site PK
id id_horizon PK
%% for very rapid assessment %%
cat catu_horizon_primary FK "Section 20.1.1"
cat catu_horizon_secondary FK "Section 20.1.2"
cat catu_horizon_suffix_redox FK "Section 20.1.3"
%% 'other' 1:n so in subtable see below %%
int[2-Inf] val_lith_discont "Section 20.1.5"
int[2-Inf] val_duplicate "Section 20.1.6"
bool ind_buried "Section 20.1.7"
%% for edge cases %%
txt txt_horizon_name_full
%% FH
cat catu_functional_name FK "Section 20.2"
cat cato_functional_perm FK "Section 20.2.1.8"
bool ind_topsoil "Table 20.1"
bool ind_tephra_acid "Table 20.2"
bool ind_tephra_basic "Table 20.2"
cat cato_horizon_drainage "Section 21.3"
}
%% data %%
horizon_names_suffix_other {
id id_site PK
id id_horizon PK
id id_suffix
cat catu_horizon_suffix "Section 20.1.4"
}
horizon_names_primary {
id id_horizon_primary PK
cat catu_horizon_primary FK "Table 20.1"
txt txt_horizon_primary_name "Table 20.1"
txt txt_horizon_primary_desc "Table 20.1"
}
horizon_names_secondary {
id id_horizon_secondary PK
cat catu_horizon_secondary FK "Tables 20.2-20.7"
txt txt_horizon_secondary_name "Tables 20.2-20.7"
txt txt_horizon_secondary_desc "Tables 20.2-20.7"
}
horizon_suffix_redox {
id id_horizon_suffix_redox PK
cat catu_horizon_suffix_redox FK "Tables 20.8"
txt txt_horizon_suffix_redox_name "Tables 20.8"
txt txt_horizon_suffix_redox_desc "Tables 20.8"
}
horizon_suffix_other {
id id_horizon_suffix_other PK
cat catu_horizon_suffix_other FK "Tables 20.9"
txt txt_horizon_suffix_other_name "Tables 20.9"
txt txt_horizon_suffix_other_desc "Tables 20.9"
}
horizon_names_functional {
id id_horizon_name_func PK
cat catu_horizon_name_func FK "Table 20.18"
txt txt_horizon_name_func_desc "Table 20.18"
}
functional_horizon_permeabilities {
id id_horizon_perm PK
cat cato_functional_perm FK "Table 20.17"
txt txt_functional_perm_name "Table 20.17"
txt txt_horizon_name_func_desc "Table 20.17"
}
horizon_drainage_classes {
id id_horizon_drainage PK
cat cato_horizon_drainage FK "Table 21.5"
txt txt_horizon_drainage_name "Table 21.5"
}
D.13 Helper tables
D.13.1 Measurement type
Some numeric parameters require a separate specification for types of missing data (e.g. aspect at the site, Section 11.2). These codes are built in to most categorical code lists in this standard (where it makes sense to include them). For numeric data, a companion text column is required. It is generally expected that it will be populated in a semi-automated manner - for each parameter, it should default to NR, and be automatically updated to NM if a value is supplied. Users may also update to ND as needed.
| Code | Name |
|---|---|
| NM | Parameter was recorded normally |
| ND | Parameter could not be reliably determined or does not apply |
| NR | Parameter was not recorded |