Goal. Provide a complete, deterministic loading order and data entry rules to populate the database that implements the INSPIRE Soil application schema. The guide covers the logical flow Site → Plot → Profile → Elements, the Observed vs Derived distinction, Horizon vs Layer behaviour, classifications, associations, and STA2 (datastream/observation) integration. References to the INSPIRE Technical Guidelines and Feature Concept Dictionary are provided as footnotes.1
(See official INSPIRE definitions.)23
(See official INSPIRE definitions.)45
codelist table. You must preload every collection you plan to use before you insert domain records (e.g., SoilInvestigationPurposeValue, SoilPlotTypeValue, LayerTypeValue, EventProcessValue, EventEnvironmentValue, LayerGenesisProcessStateValue, LithologyValue, FAO/WRB codelists, wrbversion, Category).beginlifespanversion = now() on insert; beginlifespanversion auto‑update upon relevant changes; checks on validfrom ≤ validto and beginlifespanversion < endlifespanversion.guid is NULL on insert, the DB generates a UUIDv4‑like lowercase value; updates to guid are blocked by triggers.codelist, unitofmeasuresoilsitesoilplot (FK → soilsite)soilprofile — choose type via isderived (Observed or Derived)isderivedfrom — link Derived ↔ Observed profilessoilbody, then soilbody_geomderivedprofilepresenceinsoilbody — assign Derived profiles with shares (≤ 100 per soilbody)profileelement (then, for Horizons only: faohorizonnotationtype, otherhorizonnotationtype + otherhorizon_profileelement)wrbqualifiergrouptype, then wrbqualifiergroup_profileobservedproperty, sensor, observingprocedure, obsprocedure_obsdproperty, obsprocedure_sensordatastream — set the FOI; triggers resolve and/or create the target thingobservation — value‑level checks per datastream.typesoilderivedobject and its relations: isbasedonobservedsoilprofile, isbasedonsoilbody, isbasedonsoilderivedobjectWhy this order? Each step unlocks the next (FKs and triggers). For instance: Plots need Sites; Observed Profiles need Plots; Derived–Observed links need both sides; Elements need Profiles; Notations and WRB need both codelists and target types; Datastreams require master data and FOI; Observations require Datastreams and, for Category, a codespace collection.
soilsite (POLYGON)geometry, soilinvestigationpurpose ∈ SoilInvestigationPurposeValue.validfrom ≤ validto; beginlifespanversion < endlifespanversion.soilplot (POINT)soilplotlocation, soilplottype ∈ SoilPlotTypeValue.locatedon → soilsite.guid (Site must exist first).soilprofile (Observed vs Derived)isderived → 0 = Observed, 1 = Derived.23isderived=0): location NOT NULL and references soilplot.guid; location is UNIQUE (one observed profile per plot).isderived=1): location MUST be NULL (non point‑located profile).wrbversion ∈ wrbversion collection; wrbreferencesoilgroup must match the year‑specific collection coherent with wrbversion (INSPIRE/2014/2022).isderivedfrom (Derived → Observed)guid_base → soilprofile.guid with isderived=1; guid_related → soilprofile.guid with isderived=0. Triggers enforce types and uniqueness.soilbody & soilbody_geomsoilbody first, then soilbody_geom (MULTIPOLYGON) referencing it.derivedprofilepresenceinsoilbodyguid_soilprofile must be Derived.lowervalue for the same guid_soilbody must not exceed 100.00 (trigger).profileelement → Horizon vs Layerprofileelementtype → 0 = HORIZON, 1 = LAYER. (See INSPIRE definitions.)45…_uppervalue / …_lowervalue must be set; if both are set, uppervalue < lowervalue (depth increases downward).ispartof → soilprofile.guid.Horizon (profileelementtype=0)
layertype, layerrocktype, layergenesisprocess, layergenesisenviroment, layergenesisprocessstate.faohorizonnotationtype (1:1) and to otherhorizonnotationtype via otherhorizon_profileelement (N:M). Triggers enforce that the target is a Horizon.Layer (profileelementtype=1)
layertype ∈ LayerTypeValue; layerrocktype ∈ LithologyValue;layergenesisprocess ∈ EventProcessValue; layergenesisenviroment ∈ EventEnvironmentValue;layergenesisprocessstate ∈ LayerGenesisProcessStateValue.layertype ≠ geogenic, genesis‑related fields must be NULL.faohorizonnotationtype (1:1 to Horizon via guid_profileelement): FAO masters/subordinates/prime from their codelists; trigger ensures the PE is a Horizon.otherhorizonnotationtype + otherhorizon_profileelement (N:M): horizonnotation ∈ OtherHorizonNotationTypeValue; diagnostichorizon must belong to the collection indicated by horizonnotation.wrbqualifiergrouptype (qualifier group library):
wrbversion ∈ wrbversion; qualifierplace ∈ WRBQualifierPlaceValue;wrbqualifier and wrbspecifier_1/_2 must come from year‑specific collections coherent with wrbversion.(wrbversion, qualifierplace, wrbqualifier, wrbspecifier_1, wrbspecifier_2)._1 required if _2 is set; _1 and _2 must differ.wrbqualifiergroup_profile (attach groups to profiles): version match (profile vs group) enforced; qualifierposition unique per (soilprofile, qualifierplace).soilderivedobject (POLYGON) can represent thematic outputs (derived maps/envelopes).isbasedonobservedsoilprofile (link to Observed profiles);isbasedonsoilbody;isbasedonsoilderivedobject (hierarchies of derived objects).observedproperty: name; definition must be a well‑formed URI (checked). Optional JSON properties.sensor: name; if metadata is set, encodingtype must be PDF or HTML; otherwise both NULL.observingprocedure: name; optional definition URI.obsprocedure_obsdproperty (procedure ↔ property) — the pair is required if a procedure is set on a datastream;obsprocedure_sensor (procedure ↔ sensor) — optional.datastream (series definition & FOI)type ∈ {'Quantity','Category','Boolean','Count','Text'} with table‑level checks:
code_unitofmeasure and forbids codespace and categorical bounds;codespace (well‑formed URI and a codelist.collection), forbids units and numeric bounds;observation level (see below).guid_soilsite, guid_soilprofile, guid_profileelement, guid_soilderivedobject.thing (triggered):
guid_thing = SoilSite.guid;guid_thing = SoilProfile.guid;SoilPlot (Profile→Plot→Site);guid_thing = Soilderivedobject.guid.
Missing thing rows are created on demand with reasonable defaults.observation (values)(phenomenontime_start, guid_datastream).datastream.type (enforced by triggers):
result_real only; apply numeric bounds if defined;result_text only; must belong to codelist.id within the codespace collection;result_boolean ∈ {0,1} only;result_real must be integer in value; obey numeric bounds if defined;result_text only.datastream.phenomenontime_* is recalculated from member observations on insert/update/delete.collection and id values referenced by the target column.soilprofile rejects insert → Check isderived vs location rule; verify WRB version/group pairing.derivedprofilepresenceinsoilbody fails → The sum of lowervalue for the same soilbody would exceed 100.profileelement fails → Depth range order (upper < lower), Horizon/Layer field constraints, geogenic rule.wrbqualifiergroup_profile fails → wrbversion mismatch or duplicate qualifierposition for the same qualifierplace.datastream fails → FOI multiplicity (must be 0 or 1); type/combination checks; required (procedure, property) pair.observation fails → Shape/type mismatch, bounds violation, or Category value not in the codespace collection.