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 JakimowEarth Observation Lab | Geography Department | Humboldt-Universität zu Berlin
e-mail: benjamin.jaki...@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
smime.p7s
Description: S/MIME Cryptographic Signature
_______________________________________________ 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
