Following Nathan's SpatiaLite/DBManager solution in this answer, I find that once the layer is loaded from DB Manager into QGIS (2.6.1), any calculated fields are appearing as text values instead of numeric types. This is especially a problem because I would like to use those fields as the basis of style using graduated colors.
In the original table, the "pop" column is an integer datatype. But when I look at the Layer Properties > Fields tab in QGIS, it clearly shows the field having type=QString, "Type name"=TEXT for these fields that were computed in the query, such as:
COALESCE(SUM(a2.pop),0) AS pop_neighbours
This seems to happen anytime there is a calculation in the SELECT statement, even for something as simple as:
a.pop + 1 AS pop1
I get the same result when trying to force the datatype:
CAST (a1.pop + 1 AS INTEGER) AS pop1
The datatype of any unmodified columns (such as a.pop) seems to be preserved without any problem. The QGIS Layer Properties > Fields tab shows type=qlonglong, "Type name"=INTEGER.
Is there a way to preserve the datatypes of calculated fields?
For what it's worth, I see the same issue sometimes when using QSpatiaLite, depending on the method. These work fine in QSpatiaLite:
- Create Table & Load in QGIS
- Create Spatial Table & Load in QGIS
- Create Spatial View & Load in QGIS
But these don't:
- Load in QGIS as Spatial Layer
- Create View & Load in QGIS
- "CREATE TABLE AS" within a query