AI4SH in-situ data handling and modelling with Xspatula
The EU-funded AI4SoilHealth (AI4SH) project postgreSQL database stores soil data collected from field sampling across Europe, analysed by wet laboratory, spectroscopy, and a range of other laboratory, home and field based sensors. To accommodate this data with FAIR (Findability, Accessibility, Interoperability, and Reuse) principles the Xspatula framework provides a comprehensive PostgreSQL database and a JSON-driven Python workflow for seeding a database, loading AI4SH data and then apply Machine Learning for modelling key soil properties from spectra and with various home and field based sensors.
Prerequisites
The first two stages, setting up a postgreSQl database and register processes in the database, are introduced in more detail in the general introduction to the Xspatula framework:
- Database setup — schemas and tables created; see Setup core db
- Process registration — all framework processes registered; see Setup processes
These two stages are also covered, with focus on creating the AI4SH database and its core processes, in this site.
To get started you need to clone or download the AI4SH data loading package from GitHub:
git clone https://github.com/xspatula/load_ai4sh_db
Outline of the AI4SH postgreSQL database
The AI4SH postgres database contains 9 schemas:
- utility — support tables for general information used across schemas (default framework schema)
- community — organisations and users; all users logging into the system must be registered here (default framework schema)
- process — all processes defined for the AI4SH database (default framework schema)
- landscape_utility — reference tables for landscape classification
- landscape — landscape observations
- observation_utility — catalogues and reference data required for FAIR-compliant soil observations (units, methods, instruments, taxa, etc.)
- observation — actual soil property data, organised through datasets, campaigns, samples and observations
- edna_utility — reference tables for environmental DNA methods
- edna — eDNA observations
Seeding the database
The AI4SH database is seeded in two stages:
- Setup DB — defines all schemas and tables using the Jupyter notebook
setup/setup_db.ipynb - Setup processes — registers all framework processes in the database using the notebook
setup/setup_processes.ipynb
Both stages use the Xspatula JSON-driven workflow: a scheme file points to a job file, which links to a pilot file listing the individual process files to execute. Alternatively, if you only have one process_file, you can point directly from the job_file to this process_file and skip a pilot_file. For a detailed explanation of this hierarchy, see the Xspatula framework documentation.
Data loading overview
Data is loaded in a mandatory sequence — each stage depends on records from the previous one:
| Stage | Notebook | Pages |
|---|---|---|
| Utility data | load_ai4sh_utility_data.ipynb |
7 |
| Dataset metadata | load_ai4sh_dataset_meta.ipynb |
7 |
| Sample data | load_ai4sh_sample_data.ipynb |
4 |
| Wetlab data | load_ai4sh_wetlab_data.ipynb |
4 |
| Spectral data | load_ai4sh_spectral_data.ipynb |
13 |
Alternatively, all stages can be run from a single notebook (load_ai4sh_data.ipynb).
Two-step pattern
Every data loading operation follows the same translate-then-manage pattern:
Excel source data
↓ [translate cell]
JSON process files
↓ [manage cell]
PostgreSQL database
The translate step reads Excel files and writes JSON process files to disk. The manage step reads those JSON files and executes them against the database. The two steps are decoupled so you can inspect the JSON before committing to the database.
Acknowledgments and Funding
This work was done as part of the AI4SoilHealth project, funded by the European Union’s Horizon Europe Research and Innovation Programme under Grant Agreement No. 101086179.
Funded by the European Union. The views and opinions expressed are those of the authors only and do not necessarily reflect those of the European Union or the European Research Executive Agency.