Adding a column and update values using SQL in QGIS

Question

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 :

ALTER TABLE data 
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 '19 at 7:57
@snaileater is there any way to use sql commands through QGIS? — Nat, Feb 22 '19 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 '19 at 8:05
@user30184 Yes, I want to add the new columns into the shapefile permanently. — Nat, Feb 22 '19 at 8:08
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). — geozelot, Feb 22 '19 at 8:22

user30184 · Accepted Answer · 2019-02-22 08:24:18Z

4

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

answered Feb 22 '19 at 8:24

user30184

35.3k2 gold badges34 silver badges70 bronze badges

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 '19 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 '19 at 9:59

add a comment |

1 Answer 1