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 *,
                                 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

You have field "assessment" in the table and is gets selected by select *. Then you try to make another selection with the same name with your CASE statement but the result from selecting "assessment" for the first time is not overwritten.

Same query but edited into END AS assessment2gives expected results.

Layer name: SELECT
Geometry: None
Feature Count: 3
Layer SRS WKT:
proportionclass1: Integer (0.0)
proportionclass2: String (0.0)
proportionclass3: String (0.0)
assessment: String (0.0)
assessment2: String (0.0)
  proportionclass1 (Integer) = 70
  proportionclass2 (String) = (null)
  proportionclass3 (String) = (null)
  assessment (String) = (null)
  assessment2 (String) = Mixed

  proportionclass1 (Integer) = (null)
  proportionclass2 (String) = 70
  proportionclass3 (String) = (null)
  assessment (String) = (null)
  assessment2 (String) = Mixed

  proportionclass1 (Integer) = (null)
  proportionclass2 (String) = (null)
  proportionclass3 (String) = 70
  assessment (String) = (null)
  assessment2 (String) = Mixed

If you want to store the values into the database you must write SQL as -sql "update layer_name set assessment=...

  • Thank you very much for your reply but i was hoping for something more compact. Maybe something like ogrinfo -dialect SQLite -sql "ALTER TABLE classified_mayo_area ADD COLUMN assessment Text" classified_mayo_area.sqlite ogrinfo -dialect SQLite -sql "UPDATE classified_mayo_area SET assessment= SELECT assessment CASE, WHEN proportionclass1 > 70 THEN 'NutrientDeficient' WHEN proportionclass2 > 70 THEN 'Healthy' WHEN proportionclass3 > 70 THEN 'BareSoil' ELSE 'Mixed' END" classified_mayo_area.sqlite – Walshe_d Dec 14 '17 at 12:39
  • Your question was not clear about that. You wrote ogrinfo -dialect SQLite -sql "SELECT *, ... so I thought that select is what you want. But your own ogrinfo command with UPDATE should do it if you write it right. By a quick look you must at least remove the comma from here SELECT assessment CASE, WHEN – user30184 Dec 14 '17 at 13:16
  • Apologies about that. Let me re-frame my question so: can you help me with my original script to keep it as compact as that (within reason) to achieve a script that will classify healthy, nutrient deficiency, and bare soil based on 70% threshold from the proportionclass columns and output it into the assessment column please? – Walshe_d Dec 14 '17 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.