SoilWise Geopackage

Soil Body

Definition

“Part of the soil cover that is delineated and that is homogeneous with regard to certain soil properties and/or spatial patterns. The soils present in the soil body are characterized by one or more derived soil profiles that are found together in the area specified by the “geometry” attribute of the SoilBody.”1

INSPIRE Data Specification on Soil – Technical Guidelines,
D2.8.III.3.
https://inspire-mif.github.io/technical-guidelines/data/so/dataspecification_so.pdf

 

TABELLA SOILSITE EXP


 

Table: soilbody

Columns

Name Type Constraints Description
id INTEGER PRIMARY KEY Primary Key of the Table.
guid TEXT   Universally unique identifier.
inspireid_localid TEXT   A local identifier, assigned by the data provider. The local identifier is unique within the namespace, that is no other spatial object carries the same unique identifier.
inspireid_namespace TEXT   Namespace uniquely identifying the data source of the spatial object.
inspireid_versionid TEXT   The identifier of the particular version of the spatial object, with a maximum length of 25 characters. If the specification of a spatial object type with an external object identifier includes life-cycle information, the version identifier is used to distinguish between the different versions of a spatial object. Within the set of all versions of a spatial object, the version identifier is unique.
beginlifespanversion DATETIME NOT NULL, DEFAULT strftime(‘%Y-%m-%dT%H:%M:%fZ’, ‘now’) Date and time at which this version of the spatial object was inserted or changed in the spatial data set.
endlifespanversion DATETIME   Date and time at which this version of the spatial object was superseded or retired in the spatial data set.
soilbodylabel TEXT NOT NULL Label to identify the soil body according to the specified reference framework (metadata).

Table Identifiers

In this table, the primary key is the id field (integer, auto-incrementing).
There is also a text field named GUID, which stores a UUID (Universally Unique Identifier) compliant with RFC 4122.

Although GUID is not mandatory at the schema level (it is not declared NOT NULL), its functional requirement is enforced by two triggers:

  • soilbodyguid (INSERT) trigger: if GUID is missing or empty, a correctly formatted UUID is generated and inserted automatically.
  • soilbodyguidupdate (UPDATE) trigger: prevents any modification of GUID after insertion, making it immutable (effectively behaving as a stable key).

Any foreign keys (FK) from other tables reference this table’s GUID field rather than the id field, ensuring stable and interoperable references across datasets and database instances.

Note

GUID management is handled by database triggers, which ensure their automatic generation at the time of record insertion, without any user involvement.

Relationships (as child)

  • None

Referenced by (as parent)

  • derivedprofilepresenceinsoilbody.guid_soilbody → soilbody.guid (ON UPDATE CASCADE, ON DELETE CASCADE)
  • isbasedonsoilbody.guid_soilbody → soilbody.guid (ON UPDATE CASCADE, ON DELETE CASCADE)
  • soilbody_geom.guid_soilbody → soilbody.guid (ON UPDATE CASCADE, ON DELETE CASCADE)

Indexes

  • None

Triggers

For every trigger you will find:

  • When it runs (BEFORE/AFTER, INSERT/UPDATE/DELETE)
  • What it reads and compares (columns, lookups in other tables)
  • What happens on success (the statement proceeds, optional updates)
  • What happens on failure (the exact error text raised)

soilbodyguid / soilbodyguidupdate

When they run: AFTER INSERT / AFTER UPDATE OF guid

What they do: Generate a GUID at insert when missing; block GUID changes later.

If the check passes: Insert writes GUID; unchanged updates proceed.

If the check fails: On change, abort with: Cannot update guid column.

i_ceckvalidversionsoilbody

When it runs: BEFORE INSERT

What it does: Require beginlifespanversion < endlifespanversion.

If the check passes: Insert proceeds.

If the check fails: Aborts with: Table soilbody: beginlifespanversion must be less than endlifespanversion.

u_begin_today_soilbody / u_begin_today_soilbody_error

When they run: AFTER UPDATE

What they do: Refresh beginlifespanversion when still active; block edits if end-of-lifespan is past.

If the check passes: Timestamp refreshed / update continues.

If the check fails: Aborts with: If you change record endlifespanversion must be greater than today.

  1. European Commission – Joint Research Centre (JRC), ↩