3

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'))
  • How doesn't it work? No data output? Error message? Have you seen an example of CASE being used like this in ogr2ogr? Can you make a simple example work? – Spacedman Jan 16 '18 at 18:44
  • I can't find any evidence that CASE is supported in the ogr SQL doc: gdal.org/ogr_sql.html – Spacedman Jan 16 '18 at 18:46
  • Yep not data output (simple queries work°. I thought it existed like in any sql version Probably it doesn't exist then Thanks for your help – Mehdi Mike Jan 16 '18 at 18:46
  • 1
    I can make it work, but you have to specify the SQLITE dialect and your syntax is a bit wrong... Hang on... – Spacedman Jan 16 '18 at 18:53
4

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
| improve this answer | |
  • Can you tick the little tick next to my answer to make it accepted? Then we get some points. – Spacedman Jan 16 '18 at 20:35
  • I like your approach here, @Spacedman. Trying a similar approach but with an Oracle datasource (so instead of reading from landuse_example.shp, I'm reading OCI:user/password@DBP01). My problem is that any query I do, however simple, can't seem to read a table from that database; I get a 'no such table: invasive_plant_site' message. Any thoughts here? – grego May 28 at 19:34
  • @grego you should make a new question, with as much detail as possible, showing your command line and maybe simpler ones that do work? Tag it [ogr2ogr] and [oracle] and someone will pick it up! – Spacedman May 28 at 21:25
0

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.

| improve this answer | |

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.