description strings of stationmeta_global make use of the controlled vocabulary
User functions can be defined to make use of the controlled vocabulary.
This is a template (pseudo code!) to check for the right values within descriptive strings:
CREATE OR REPLACE FUNCTION check_descriptive_landcover_string(description str) RETURNS boolean
AS
$$
# prepare everything to return the aggregated value from loop
...
# now do the loop
for desc in description.split(','):
code, perc = desc.split(':')
# next_command to be done --> only one aggregated value from loop to be returned!
SELECT code = ANY(SELECT enum_val FROM lc_vocabulary);
# return aggregated value from loop
...
$$
LANGUAGE python;
This function should now be used as a check constraint and should then avoid wrong values being ingested.
It is added to the database with the following command:
ALTER TABLE stationmeta_global
ADD CONSTRAINT stglob_landcover_description_fk_lc_vocabulary_enum_val
CHECK(check_descriptive_landcover_string(landcover_description_year2012));