11

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.
4
  • What error are you getting?
    – R.K.
    Commented May 2, 2013 at 11:19
  • @R.K., I added the error message to the op.
    – Kay
    Commented May 2, 2013 at 12:51
  • have you tried CAST instead of CONVERT?
    – dmci
    Commented May 2, 2013 at 13:08
  • I did without success..
    – Kay
    Commented May 2, 2013 at 14:23

2 Answers 2

15

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))"
0
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!

4
  • 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
    Commented May 2, 2013 at 14:23
  • What version of GDAL are you using?
    – R.K.
    Commented May 2, 2013 at 15:13
  • I'm using gdal-17
    – Kay
    Commented May 2, 2013 at 15:38
  • 1
    The SQLite dialect needs GDAL/OGR 1.10.
    – scruss
    Commented Aug 31, 2013 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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