Case statement in ogr2ogr

Question

I am trying to import ways from OSM maps and I am trying to classify them by type of roads, for example if highway='service' then 'cat1'. I tried to use a case statement but it doesn't work For info, I am using ogr2ogr in python. Here is my code:

import os
os.system('ogr2ogr -overwrite -skipfailures -f "ESRI Shapefile"  -progress -
sql "SELECT highway,CASE highway WHEN %s THEN %s ELSE %s END FROM lines" -
lco ENCODING=UTF-8 %s %s' %('service','cat1','cat2','C:/Users/mehdi/Desktop/haiti1.shp','C:/Users/mehdi/Desktop/haiti.pbf'))

Answer 1

Given a shapefile, landuse_example.shp that has attributes and geometry like this:

   DN     Class                       geometry
1 258  Class II POLYGON ((588421.52638439 2...
2 258  Class II POLYGON ((588355.38055099 2...
3 255   Class I POLYGON ((588487.67221779 2...
4 342  Class II POLYGON ((588355.38055099 2...
5 523 Class III POLYGON ((588619.9638846 29...
6 258  Class II POLYGON ((588355.38055099 2...

I want to make a similar shapefile but with a new column called status which is "FIRST" for all Class I values and "LAST" for everything else.

ogr2ogr -dialect SQLITE -sql "SELECT *,CASE Class  WHEN 'Class I' THEN 'FIRST' ELSE 'LAST' END AS status FROM landuse_example" withstatus.shp landuse_example.shp

The resulting shapefile attribute and geometry looks like this:

   DN     Class status                       geometry
1 258  Class II   LAST POLYGON ((588421.52638439 2...
2 258  Class II   LAST POLYGON ((588355.38055099 2...
3 255   Class I  FIRST POLYGON ((588487.67221779 2...
4 342  Class II   LAST POLYGON ((588355.38055099 2...
5 523 Class III   LAST POLYGON ((588619.9638846 29...
6 258  Class II   LAST POLYGON ((588355.38055099 2...

Note the status column.

Here's the code spaced out so you can see where the conditions and values go - don't try and run this, the line breaks will break it!

ogr2ogr -dialect SQLITE 
-sql "SELECT *,
       CASE Class 
        WHEN 'Class I' THEN 'FIRST'
        ELSE 'LAST' 
       END AS status 
      FROM landuse_example"
withstatus.shp landuse_example.shp

Answer 2

Although it's not clearly stated in the OGR documentation, CASE is supported in OGR SQL.

I was able to use CASE in an SQL statement successfully in ogr2ogr (command line) using this syntax:
(the CASE syntax I've used here works in Oracle and OGR SQL)

case when <argument1> then 'Value1' 
when  <argument2> then 'Value1' 
when <argument3> then 'Value2'
else 'Value2' end as COLUMN_NAME

Here is how the full SQL statement looks in my case:

select p.site_id, s.utm_zone, s.utm_easting, s.utm_northing,

case when 'Y' in (bd.foliar_feeding_damage_ind, bd.root_feeding_damage_ind, bd.seed_feeding_damage_ind,
bd.oviposition_marks_ind, bd.eggs_present_ind, bd.larvae_present_ind, bd.pupae_present_ind,
bd.adults_present_ind, bd.tunnels_present_ind) then 'Found' 
when  bd.agent_count > 0 then 'Found' 
when ('Y' not in  (bd.foliar_feeding_damage_ind,bd.root_feeding_damage_ind, bd.seed_feeding_damage_ind,
bd.oviposition_marks_ind, bd.eggs_present_ind, bd.larvae_present_ind, bd.pupae_present_ind,
bd.adults_present_ind, bd.tunnels_present_ind) and (bd.agent_count = 0 or bd.agent_count is null)) then 'Not Found'
else 'Not Found' end as BIO_DISPERSAL

from invasive_plant p
join  invasive_plant_site s on (p.site_id = s.site_id)
join biological_dispersal bd on (p.INVASIVE_PLANT_ID= bd.invasive_plant_id)
join invasive_plant_species sp on (p.GENUS_CODE = sp.GENUS_CODE and p.SPECIES_CODE = sp.SPECIES_CODE)

With the sql above saved to T:\tempQueryFolder\query1.sql, I can successfully run the following ogr2ogr line (Note: I am reading an Oracle database using the OCI driver, and writing to a CSV):

ogr2ogr.exe -f "CSV" T:\_test100\biotreatment_CIRS_ARV_LARIPLA_UTM_10_attempt3.csv OCI:user/password@ORACLE_DB_NAME -progress -sql @T:\tempQueryFolder\query1.sql -overwrite

Watch out for the following error:
ERROR 1: Terminating translation prematurely after failed translation from sql statement.

This means the problem lies in your SQL statement.