Benjamin,
Explicit casts like "(CAST(SUM(D.QAI) as INTEGER) as n_qai" did not
solve my problem.
Yeah, I recently came across a similar issue, and the finding is that it
is related to the SQLite version. Such casts apparently work since
SQLite 3.42. So if your QGIS build uses an older SQLite version, they
won't be honored.
$ ogr2ogr poly.gpkg autotest/ogr/data/poly.shp
$ ogrinfo poly.gpkg -sql "create view v as select fid as ogc_fid, geom,
cast(1 as int) as my_int, cast(1 as real) as my_real from poly"
$ LD_LIBRARY_PATH=/home/even/install-sqlite-3.41.0/lib:$LD_LIBRARY_PATH
ogrinfo poly.gpkg -al -so | grep my_
my_int: String (0.0)
my_real: String (0.0)
vs
$ LD_LIBRARY_PATH=/home/even/install-sqlite-3.42.0/lib:$LD_LIBRARY_PATH
ogrinfo poly.gpkg -al -so | grep my_
my_int: Integer64 (0.0)
my_real: Real (0.0)
This is really a SQLite3 issue as demonstrated by:
$ ~/install-sqlite-3.41.0/bin/sqlite3 poly.gpkg "pragma table_info(v)"
0|ogc_fid|INTEGER|0||0
1|geom|POLYGON|0||0
2|my_int|NUM|0||0
3|my_real|NUM|0||0
vs
$ ~/install-sqlite-3.42.0/bin/sqlite3 poly.gpkg "pragma table_info(v)"
0|ogc_fid|INTEGER|0||0
1|geom|POLYGON|0||0
2|my_int|INT|0||0
3|my_real|REAL|0||0
*However* this only works for TEXT, INTEGER and REAL. As there is no
SQLite native DATETIME "affinity" (cf
https://www.sqlite.org/datatype3.html), there's no way currently to
force view columns to DATETIME
Interestingly, the OGRDataSource::ExecuteSQL does returns correct
integer value for COUNT and SUM results. DATE(...) results are
returned as STRINGS as well.
with ds.ExecuteSQL('SELECT * FROM ard_data_byTile') as lyr:
yes, that's because in that mode, the GPKG driver uses the value of the
first row to infer the data type, but this isn't super bullet proof
(particularly if the first row contains NULL values)
Even
--
http://www.spatialys.com
My software is free, but my time generally not.
_______________________________________________
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
