SoilWise Geopackage

Other Soil Name Type

Definition

“An identification of the soil profile according to a specific classification scheme.”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: othersoilnametype

Columns

Name Type Constraints Description
id INTEGER PRIMARY KEY Primary Key of the Table.
guid TEXT   Universally unique identifier.
othersoilname_type TEXT NOT NULL Name of the soil profile according to a specific classification scheme.
othersoilname_class TEXT   Specific classification scheme.
isoriginalclassification BOOLEAN NOT NULL, DEFAULT 0 Boolean value to indicate whether the specified classification scheme was the original classification scheme to describe the profile.
othersoilname TEXT   Foreign key to the SoilProfile table, guid field.

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:

  • othersoilnametypeguid (INSERT) trigger: if GUID is missing or empty, a correctly formatted UUID is generated and inserted automatically.
  • othersoilnametypeguidupdate (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.

Coded Fields

The ­othersoilname_type field is a coded field (codelist-based attribute), meaning that it can only contain values belonging to a predefined codelist, in accordance with the INSPIRE specifications.

Warning

Any attempt to insert a value that is not included in the corresponding codelist is considered invalid by the system and will result in the failure of the data insertion operation.

Warning

The INSPIRE codelist associated with the ­othersoilname_type field is defined; however, it is currently empty and does not contain any code values. Consequently, this table cannot be populated. Users are advised to define a project-specific codelist and implement it within the GeoPackage in accordance with the schema and guidance described on documentation page.

Codelist Definition

The complete list of allowed codes is stored in the codelist table.
The associated documentation, provides a detailed description of:

  • which codes are available (codelist URL),
  • the database tables to which each codelist applies,
  • the fields for which each code is valid,

in accordance with the adopted conceptual model.

Validation and Data Entry

The semantic and syntactic validation of the inserted values is enforced at the database level through dedicated control triggers (i_soilname/u_soilname), ensuring compliance with the defined codelists.

Important

During data entry via the QGIS interface, users are supported by dropdown menus that display only the valid codes for the selected field.

Note

This mechanism reduces the risk of data entry errors and guarantees alignment with the constraints imposed by the INSPIRE codelists.

Relationships (as child)

  • othersoilnametype.othersoilname → soilprofile.guid (ON UPDATE CASCADE, ON DELETE CASCADE)
    • Note: delete on soilprofile cascades to othersoilnametype.

Referenced by (as parent)

  • None

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)

othersoilnametypeguid / othersoilnametypeguidupdate

When they run: AFTER INSERT / AFTER UPDATE OF guid

What they do: Auto-generate a GUID at insert if missing; prevent changing it later.

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

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

i_soilname / u_soilname

When they run: BEFORE INSERT / BEFORE UPDATE

What they do: Validate that othersoilname_type exists in the OtherSoilNameTypeValue codelist.

If the check passes: Statement proceeds.

If the check fails: Aborts with: Table othersoilnametype: Invalid value for othersoilname_type. Must be present in id of othersoilnametypevalue codelist.

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