I'm having problems classifying an area of mixed forest using ogrinfo.
I have an .sqlite file which contains the three columns; proportionclass1. proportionclass2, proportionclass3.
I'm trying to assign a description for each observation in a new column (assessment) based on if the percentage of any of the classes is over a certain threshold (this case 70). So if proportionclass1 is over 70 I want it to write NutrientDeficient to the assessment column, if proportionclass2 is over 70 I want it to write Healthy to the assessment column, if proportionclass3 is over 70 I want it to write BareSoil to the assessment column, and if none of the cells for any observations of the three proportion classes are over 70 then i want it to write mixed in the assessment column.
The code i have is below, could anyone point out what im missing or what i've done wrong as the code will run without an error but the assessment column will remain blank.
ogrinfo -dialect SQLite -sql "ALTER TABLE table_name ADD COLUMN assessment Text" classified_area.sqlite
ogrinfo -dialect SQLite -sql "SELECT *,
CASE
WHEN proportionclass1 > 70 THEN 'NutrientDeficient'
WHEN proportionclass2 > 70 THEN 'Healthy'
WHEN proportionclass3 > 70 THEN 'BareSoil'
ELSE 'Mixed'
END AS assessment
FROM layer_name" classified_area.sqlite