[QGIS-Developer] qgis / ogr converts INT & DATE values from spatial view to STRING

[Benjamin Jakimow]

Dear Developers,

I like to use a GPKG to store and visualize statistical information on 
files in a remote sensing data cube. The GPKG contains three layers:

1. ard_data - a table with all kinds of information but without geometries
2. ard_tiles - a table with polygon geometries.
3. ard_data_byTile - a spatial view that joins both table by a string 
attribute "tileid" and aggregates some rows, like the number of file per 
tile (COUNT(*)):

CREATE VIEW ard_data_byTile AS
SELECT
     T.fid AS OGC_FID,
     T.geom AS geom,
     T.tileid AS tileid,
     COUNT(*) as n,
     SUM(D.QAI) as n_qai,
     SUM(D.OVR) as n_ovr,
     DATE(MIN(D.date)) as obs_first,
     DATE(MAX(D.date)) as obs_last,
     DATE(MIN(D.created)) as created_first,
     DATE(MAX(D.created)) as created_last
FROM ard_data as D JOIN ard_tiles as T
ON D.tileid = T.tileid
GROUP BY D.tileid


The view is registered as spatial layer 
(https://gdal.org/drivers/vector/gpkg.html) and its polygons can be 
visualized in QGIS well.

My problem is that the COUNT and SUM field values are cast to STRING. 
This happens in QGIS and GDAL - in the former presumably because of the 
latter:

ds: ogr.DataSource
lyr = ds.GetLayer('ard_daty_byTile')
lyrDefn: FeatureDefn = lyr.GetLayerDefn()
for i in range(lyrDefn.GetFieldCount()):
     fDefn: FieldDefn = lyrDefn.GetFieldDefn(i)
     print(f'{i}: {fDefn.GetName()} {fDefn.GetTypeName()}')

prints:
0: tileid String
1: n String
2: n_qai String
3: n_ovr String
4: obs_first String
5: obs_last String
6: created_first String
7: created_last String

Do you have any idea how I can ensure that QGIS reads integer values 
instead strings?

Explicit casts like "(CAST(SUM(D.QAI) as INTEGER) as n_qai" did not 
solve my problem.

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:
     lyrDefn: FeatureDefn = lyr.GetLayerDefn()
     for i in range(lyrDefn.GetFieldCount()):
         fDefn: FieldDefn = lyrDefn.GetFieldDefn(i)
         print(f'{i}: {fDefn.GetName()} {fDefn.GetTypeName()}')

prints:
0: tileid String
1: n Integer
2: n_qai Integer
3: n_ovr Integer
4: obs_first String
5: obs_last String
6: created_first String
7: created_last String


I uploaded an example GPKG and a python script to: 
https://box.hu-berlin.de/d/1fca8c2f1ba44162bd60/


Greetings,
Benjamin

-- 
Dr. Benjamin Jakimow
Earth Observation Lab | Geography Department | Humboldt-Universität zu 
Berlin

e-mail: benjamin.jakimow at geo.hu-berlin.de
web:    https://hu-berlin.de/eo-lab
phone:  +49 (0) 30 2093 6846
mobile: +49 (0) 157 5656 8477
fax:    +49 (0) 30 2093 6848
mail:   Unter den Linden 6 | 10099 Berlin | Germany
linkedin: https://www.linkedin.com/in/benjamin-jakimow
matrix: @jakimowb:hu-berlin.de
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4745 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20240722/3233a562/attachment.bin>