Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I try to make a new field in my attribute table and put in values from another field after converting from character to integer but can't get the right syntax. I run this as shell-script in MSYS.

cd D:/GIS_DataBase/CorineLC/shps_app_and_extr/
myfile=extr_and_app.shp

name=${myfile%.shp}

ogrinfo $myfile -sql "ALTER TABLE $name ADD COLUMN code_num int(3)"
ogrinfo $myfile -sql "UPDATE TABLE $name SET code_num = CONVERT(code_06 As int(3))"

Error Message (MSYS):

Kay@KAY-PC /c/users/kay/desktop/bash
$ sh calc_field_shp.sh
Warning 6: Unsupported column type 'int'. Defaulting to VARCHAR
INFO: Open of `extr_and_app.shp'
      using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error
INFO: Open of `extr_and_app.shp'
      using driver `ESRI Shapefile' successful.

Edit - Another go with SQLite:

cd D:/GIS_DataBase/CorineLC/shps_app_and_extr/
myfile=extr_and_app.dbf

name=${myfile%.dbf}

ogrinfo $myfile -sql "ALTER TABLE $name DROP COLUMN code_num"

ogrinfo $myfile -sql "ALTER TABLE $name ADD COLUMN code_num integer(3)"
ogrinfo $myfile -dialect SQLite -sql "UPDATE $name SET code_num = CAST(code_06 As integer(3))"

Error message:

Kay@KAY-PC /c/users/kay/desktop/bash
$ sh calc_field_shp.sh
INFO: Open of `extr_and_app.dbf'
      using driver `ESRI Shapefile' successful.
INFO: Open of `extr_and_app.dbf'
      using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error
INFO: Open of `extr_and_app.dbf'
      using driver `ESRI Shapefile' successful.
share|improve this question
    
What error are you getting? –  R.K. May 2 '13 at 11:19
    
@R.K., I added the error message to the op. –  Kay May 2 '13 at 12:51
    
have you tried CAST instead of CONVERT? –  dmci May 2 '13 at 13:08
    
I did without success.. –  Kay May 2 '13 at 14:23

2 Answers 2

up vote 2 down vote accepted

Because UPDATE is not supported in OGR SQL, as you stated in a comment, you should update the table using the SQLite SQL dialect available in GDAL >= 1.10 with SQLite and SpatiaLite support:

ogrinfo $myfile -sql "ALTER TABLE $name ADD COLUMN code_num integer(3)"
ogrinfo $myfile -dialect SQLite -sql "UPDATE $name SET code_num = CAST(code_06 AS integer(3))"

Tested and it works!

share|improve this answer
    
Thanks - I'll go with that! –  Kay Dec 18 '13 at 22:48

You can try using the CAST operator as dmci has mentioned like so

ogrinfo $myfile -sql "UPDATE TABLE $name SET code_num = CAST(code_06 as int(3))"

The SQL dialect supported by OGR does not have CONVERT if recall correctly. You can check the docs for more info. Good luck!

share|improve this answer
1  
Seemingly UPDATE is not supported, see here: osgeo-org.1560.x6.nabble.com/… However, it should be in SQLite dialect but I had no luck with that, either.. –  Kay May 2 '13 at 14:23
    
What version of GDAL are you using? –  R.K. May 2 '13 at 15:13
    
I'm using gdal-17 –  Kay May 2 '13 at 15:38
1  
The SQLite dialect needs GDAL/OGR 1.10. –  scruss Aug 31 '13 at 12:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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