3

I am trying to complete column null values on a .shp file with one default value. My column has a DATE type.

Files schema : "COLUMN_NAME TYPE(SIZE)"

  • Input (with null values) :

    CLICHE VARCHAR(80) | DATE DATE(10) | HEURE_TU VARCHAR(80)
    
  • Output (with defautl value) :

    CLICHE VARCHAR(80) | DATE_PVA DATE(10) | HEURE_TU VARCHAR(80)
    

I tried:

ogr2ogr -dialect sqlite -sql "SELECT geometry, CLICHE, CASE WHEN DATE IS NULL THEN '2012-01-01' ELSE DATE END as DATE_PVA, HEURE_TU FROM 'my_table'" outputfile.shp inputfile.shp

But I get a file with a VARCHAR(80) typed DATE_PVA column.

So I tried:

ogr2ogr -dialect sqlite -sql "SELECT geometry, CLICHE, CAST((CASE WHEN DATE IS NULL THEN '2012-01-01' ELSE DATE END) as DATE) as DATE_PVA, HEURE_TU FROM 'my_table'" outputfile.shp inputfile.shp

But I get a output file with a INT(9) type DATE_PVA column.

I am pretty sure that the 'aaaa-mm-dd' format is the good one, however I have also tried to modify date format '01-01-2012' and '01/01/2012'. No better results...

So I do not know what I can do. I need a command line solution to be able to run it in a script. However, I can try an other solution than ogr2ogr if needed.

1

Both shapefiles and SQLite have some oddities in handling times. It may be possible to do what you want by doing a conversion from one shapefile into another. However, myself I would first convert the shapefile into Spatialite database and update time field with SQL afterwards. Another option is to update your shapefile in place.

ogrinfo -dialect sqlite -sql "update inputfile set date=
'2016/10/10' where date is null" inputfile.shp
  • Ok it is find, thank you! I did not know ogrinfo... It is so simple with this solution. – vsasyan Oct 28 '16 at 9:38

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.