9

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.
  • 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
13

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))"
|improve this answer|||||
3

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!

|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

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.