Data model for the ISO28258:2013 domain model

This software proposes (and deploys) a PostGres database implementing the ISO28258:2013 domain model. ISO28258:2013 proposes a domain model to store & exchange data related to soil and land observations, observed on-site or analysed in a laboratory.

Storing data according to the ISO28258:2013 model facilitates efficient and consistent storage of soil and land data across various observation campaigns. Continue reading on the background to this initiative in an accompanying document. ISRIC - World Soil endorses the use of standardised domain models, to facilitate efficient cooperation in the soil domain.

The software is released under an MIT license

Copyright (C) 2022-2025 ISRIC - World Soil Information

Citation: de Sousa, L. M., Calisto, L., van Genuchten P., Turdukulov, U., Kempen, B., 2023. Data model for the ISO 28258 domain model. ISRIC World Soil Information. Available at: https://iso28258.isric.org/

Datamodel Documentation

The iso28258 datamodel includes a number of concepts. Most of these are included in this database implementation:

• Project: An organisational clustering of a set of observations, like a sampling campaign
• Site: A defined area where a phenomena is observed
• Plot: A location within the site where an observation is made
• Surface: The boundary between soil and air/water
• Profile: A cross section through the layers of the soil by drilling a hole or digging a pit
• Element: A layer or horizon in a soil profile
• Specimen: A physical sample, typically analysed in a laboratory
• Property: A property being observed or measured, e.g. texture, pH, volume percentage of pores
• Procedure: A procedure how the observation has been made
• Observation: The act of observing or measuring a property
• Result: The observed value or class for a property

However we've decided to not inclode:

• Horizon & Layer: as they are essentially the same in the domain, for now only ProfileElement is included.
• Sample: because in soil science it is often not distinguishable from the specimen.
• SoilMap, SoilMappingUnit, SoilTopologicalUnit: This part may be added in future

Diagrams

The diagrams below explain how the concepts relate to each other.

Project and Site

erDiagram
  project {
    INTEGER project_id
    VARCHAR name
  }
  site_project {
    INTEGER site_id
    INTEGER project_id
  }
  project_related {
    INTEGER project_source_id
    INTEGER project_target_id
    VARCHAR role
  }
  site {
    INTEGER site_id
    VARCHAR site_code
    INTEGER typical_profile
    GEOGRAPHY position
    GEOGRAPHY extent
  }
  site_project ||--o{ site : fk_site
  site_project ||--o{ project : fk_project
  project_related ||--o{ project : fk_project_source
  project_related ||--o{ project : fk_project_target

Loading

Features of Interest

erDiagram
  surface {
    INTEGER surface_id
    INTEGER super_surface_id
    INTEGER site_id
    GEOMETRY shape
    TIMESTAMP time_stamp
  }
  specimen {
    INTEGER specimen_id
    VARCHAR code
    INTEGER plot_id
    INTEGER specimen_prep_process_id
    INTEGER upper_depth
    INTEGER lower_depth
    INTEGER organisation_id
  }
  site {
    INTEGER site_id
    VARCHAR site_code
    INTEGER typical_profile
    GEOGRAPHY position
    GEOGRAPHY extent
  }
  element {
    INTEGER element_id
    VARCHAR type
    INTEGER profile_id
    INTEGER order_element
    INTEGER upper_depth
    INTEGER lower_depth
  }
  specimen_prep_process {
    INTEGER specimen_prep_process_id
    INTEGER specimen_transport_id
    INTEGER specimen_storage_id
    VARCHAR definition
  }
  profile {
    INTEGER profile_id
    VARCHAR profile_code
    INTEGER plot_id
    INTEGER surface_id
  }
  plot {
    INTEGER plot_id
    VARCHAR plot_code
    INTEGER site_id
    NUMERIC altitude
    DATE time_stamp
    VARCHAR map_sheet_code
    NUMERIC positional_accuracy
    GEOGRAPHY position
  }
  plot ||--o{ site : fk_site
  surface ||--o{ surface : fk_surface
  surface ||--o{ site : fk_site
  profile ||--o{ plot : fk_plot_id
  profile ||--o{ surface : fk_surface_id
  element ||--o{ profile : fk_profile
  specimen ||--o{ plot : fk_plot
  specimen ||--o{ specimen_prep_process : fk_specimen_prep_process
  site ||--o{ profile : fk_typical_profile

Loading

Descriptive Observations

A similar structure applies to the main Features of Interest: Surface, Plot, Profile, Element and Specimen.

erDiagram
  observation_desc_surface {
    INTEGER property_desc_surface_id
    INTEGER thesaurus_desc_surface_id
    INTEGER procedure_desc_id
  }
  procedure_desc {
    INTEGER procedure_desc_id
    VARCHAR label
    VARCHAR reference
    VARCHAR uri
  }
  surface {
    INTEGER surface_id
    INTEGER super_surface_id
    INTEGER site_id
    GEOMETRY shape
    TIMESTAMP time_stamp
  }
  property_desc_surface {
    INTEGER property_desc_surface_id
    VARCHAR label
    VARCHAR uri
  }
  result_desc_surface {
    INTEGER surface_id
    INTEGER property_desc_surface_id
    INTEGER thesaurus_desc_surface_id
  }
  thesaurus_desc_surface {
    INTEGER thesaurus_desc_surface_id
    VARCHAR label
    VARCHAR uri
  }
  observation_desc_surface ||--o{ thesaurus_desc_surface : fk_thesaurus_desc_surface
  observation_desc_surface ||--o{ property_desc_surface : fk_property_desc_surface
  observation_desc_surface ||--o{ procedure_desc : fk_procedure_desc
  result_desc_surface ||--o{ observation_desc_surface : fk_observation_desc_surface
  result_desc_surface ||--o{ surface : fk_surface

Loading

Physio-chemical Observations

Physio-chemical (numeric) observations apply to three Features of Interest: Element, Plot and Specimen. Each has its own observation and result tables (observation_phys_chem_element / result_phys_chem_element, and the equivalent _plot and _specimen tables), while the property, procedure and unit of measure are shared across all of them. Surface and Profile have no physio-chemical observations for the time being.

erDiagram
  property_phys_chem {
    INTEGER property_phys_chem_id
    VARCHAR label
    VARCHAR uri
  }
  procedure_phys_chem {
    INTEGER procedure_phys_chem_id
    INTEGER broader_id
    VARCHAR label
    VARCHAR uri
  }
  unit_of_measure {
    INTEGER unit_of_measure_id
    VARCHAR label
    VARCHAR uri
  }
  observation_phys_chem_element {
    INTEGER observation_phys_chem_element_id
    INTEGER property_phys_chem_id
    INTEGER procedure_phys_chem_id
    INTEGER unit_of_measure_id
    NUMERIC value_min
    NUMERIC value_max
  }
  result_phys_chem_element {
    INTEGER result_phys_chem_element_id
    INTEGER observation_phys_chem_element_id
    INTEGER element_id
    NUMERIC value
    INTEGER individual_id
  }
  observation_phys_chem_plot {
    INTEGER observation_phys_chem_plot_id
    INTEGER property_phys_chem_id
    INTEGER procedure_phys_chem_id
    INTEGER unit_of_measure_id
    NUMERIC value_min
    NUMERIC value_max
  }
  result_phys_chem_plot {
    INTEGER result_phys_chem_plot_id
    INTEGER observation_phys_chem_plot_id
    INTEGER plot_id
    NUMERIC value
    INTEGER organisation_id
  }
  observation_phys_chem_specimen {
    INTEGER observation_phys_chem_specimen_id
    INTEGER property_phys_chem_id
    INTEGER procedure_phys_chem_id
    INTEGER unit_of_measure_id
    NUMERIC value_min
    NUMERIC value_max
  }
  result_phys_chem_specimen {
    INTEGER result_phys_chem_specimen_id
    INTEGER observation_phys_chem_specimen_id
    INTEGER specimen_id
    NUMERIC value
    INTEGER organisation_id
  }
  procedure_phys_chem ||--o{ procedure_phys_chem : fk_broader

  observation_phys_chem_element ||--o{ property_phys_chem : fk_property_phys_chem
  observation_phys_chem_element ||--o{ procedure_phys_chem : fk_procedure_phys_chem
  observation_phys_chem_element ||--o{ unit_of_measure : fk_unit_of_measure
  result_phys_chem_element ||--o{ observation_phys_chem_element : fk_observation_phys_chem_element
  result_phys_chem_element ||--o{ element : fk_element

  observation_phys_chem_plot ||--o{ property_phys_chem : fk_property_phys_chem
  observation_phys_chem_plot ||--o{ procedure_phys_chem : fk_procedure_phys_chem
  observation_phys_chem_plot ||--o{ unit_of_measure : fk_unit_of_measure
  result_phys_chem_plot ||--o{ observation_phys_chem_plot : fk_observation_phys_chem_plot
  result_phys_chem_plot ||--o{ plot : fk_plot

  observation_phys_chem_specimen ||--o{ property_phys_chem : fk_property_phys_chem
  observation_phys_chem_specimen ||--o{ procedure_phys_chem : fk_procedure_phys_chem
  observation_phys_chem_specimen ||--o{ unit_of_measure : fk_unit_of_measure
  result_phys_chem_specimen ||--o{ observation_phys_chem_specimen : fk_observation_phys_chem_specimen
  result_phys_chem_specimen ||--o{ specimen : fk_specimen

Loading

Meta-data model (VCard)

Concepts derived from the VCard specification:

• Address
• Individual
• Organisation
• Organisation unit

erDiagram
  organisation_individual {
    INTEGER individual_id
    INTEGER organisation_id
    INTEGER organisation_unit_id
    VARCHAR role
  }
  organisation {
    INTEGER organisation_id
    INTEGER parent_id
    VARCHAR name
    VARCHAR email
    VARCHAR telephone
    VARCHAR url
    INTEGER address_id
  }
  organisation_unit {
    INTEGER organisation_unit_id
    VARCHAR name
    INTEGER organisation_id
  }
  address {
    INTEGER address_id
    VARCHAR street_address
    VARCHAR postal_code
    VARCHAR locality
    VARCHAR country
  }
  individual {
    INTEGER individual_id
    VARCHAR name
    VARCHAR honorific_title
    VARCHAR email
    VARCHAR telephone
    VARCHAR url
    INTEGER address_id
  }
  individual ||--o{ address : fk_address_id
  organisation ||--o{ address : fk_address_id
  organisation ||--o{ organisation : fk_parent_id
  organisation_unit ||--o{ organisation : fk_organisation_id
  organisation_individual ||--o{ organisation_unit : fk_organisation_unit_id
  organisation_individual ||--o{ individual : fk_individual_id
  organisation_individual ||--o{ organisation : fk_organisation_id

Loading

Installation instructions

The tool uses graphile-migrate as a database configuration/migration tool. This project uses Yarn 4 with zero-install - all dependencies are committed to the repository, so no yarn install is needed after cloning.

Using it the 1st time

1. Copy .env.dist into .env and edit the connection parameters: user, password, database name
2. Make sure you have Node.js (LTS) installed and run corepack enable from the terminal
3. Run yarn gm reset --erase to create the database with all migrations applied
4. You should now have a new database ready to go

Using it for development

After running the previous steps you should have a database ready. Development workflow:

1. Edit migrations/current.sql and insert your SQL code
2. Run yarn gm watch to apply changes automatically as you edit
3. Once satisfied, commit the migration: yarn gm commit --message "Your message"
4. Check and test your implementation

Please read migrations.md for detailed information about the migration tool configuration.

Creating a release

To create a new release (schema dump for downstream databases):

yarn make-release iso28258_v1.9.sql

This runs pg_dump with --inserts (data as INSERT statements), --no-owner, and exports the core and metadata schemas to the releases/ folder.

Using Release Files (Downstream Projects)

Release files contain only the core and metadata schemas. PostgreSQL extensions must be created separately before applying the schema.

Manual setup:

psql -d your_db -f migrations/setup/extensions.sql
psql -d your_db -f releases/iso28258_v1.9.sql

Using graphile-migrate (.gmrc):

"afterReset": [
  "setup/extensions.sql",
  "setup/iso28258_v1.9.sql"
]

Bridge Functions (v1.9+)

The schema includes bridge functions that create materialized views for simplified data export:

-- Create export views
SELECT core.bridge_process_all(NULL, 'specimen', NULL);

Optional extensions

Optional add-ons live in migrations/manual/ and are applied manually with psql, after the base schema is in place. They are not included in the release dumps.

The spectral extension adds spectral data and spectral-derived physico-chemical results for the Specimen feature of interest (nine core.* tables, a validation trigger and ETL helper functions). It is idempotent and depends only on existing base tables.

Status: Work in progress. The spectral extension is a working version and still needs to be validated by domain experts before production use.

Apply it with psql:

psql -d your_db -f migrations/manual/spectral_extension/spectral_extension.sql

or via graphile-migrate's run command (runs against DATABASE_URL; --shadow / --root available):

yarn gm run migrations/manual/spectral_extension/spectral_extension.sql

See migrations/manual/README.md and the spectral extension docs for the data model and usage examples.