I have a vector file "data.shp" containing the following fields:

ID, value1, AREA

I want to add a new column "value2" and set it as value1/AREA. Since I want to do that for many columns and files I want to use SQL.

I tried with virtual layer in QGIS with no success. I wrote in the query the following :

ADD value2 float;
UPDATE data 
SET value2 = value1/AREA

Are alter and update commands working with virtual layer? Is there any other way?

  • AFAIK update statement is not available in virtual layers ... – snaileater Feb 22 at 7:57
  • @snaileater is there any way to use sql commands through QGIS? – Nat Feb 22 at 7:59
  • QGIS virtual layers are using SQLite virtual table mechanism and then ALTER TABLE ... ADD COLUMN is not supported sqlite.org/vtab.html. But I am not sure what is your aim. Do you want to add the new column into your shapefiles permanently? – user30184 Feb 22 at 8:05
  • @user30184 Yes, I want to add the new columns into the shapefile permanently. – Nat Feb 22 at 8:08
  • 2
    you can create a new layer (and then export as new file) using SQL in the Virtual Layer, with all sorts of columns, using the data from your shapefile, but you can't alter the shapefile itself with SQL statements. note that CRUD is not directly supported; use sth. like SELECT col1, col2 [AS col_name] [, FUNC(col3) AS other_col_name], geometry, ... FROM <shapefile_name> [WHERE ...]; in the Virtual Layer SQL dialog (or DB Manager). – ThingumaBob Feb 22 at 8:22

If you want to use just SQL, do it with ogrinfo and -sql paramater.

First command must use the GDAL OGR SQL dialect https://www.gdal.org/ogr_sql.html

ogrinfo -sql "alter table my_shape add column value2 double" my_shape.shp

Second command must use the GDAL SQLite SQL dialect https://www.gdal.org/ogr_sql_sqlite.html

ogrinfo -dialect SQLite -sql "update my_shape set value2=value/(ST_Area(geometry)" my_shape.shp
  • thank you. The problem I have if I use ogrinfo is that it does not calculate the geography area but only the planimetric one (geometry). I have already made a question about this without finding any solution. – Nat Feb 22 at 8:49
  • 2
    If you have GDAL that is compiled with SpatiaLite that is compiled with LWGeom you should be able to compute also geography area gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p8. Getting such GDAL build may be difficult. When SpatiaLite 5 is released that should be easier because LWGeom is no more needed. – user30184 Feb 22 at 9:59

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.