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
  • Just a comment (I can't post comments 'cause not enough rep): I observe the same behaviour in 2.7.0-master (759a148). A little googling suggests this issue has existed from as far back as 2010(!) I'll file a qgis bug report when I get a chance... – Ingo Dec 8 '14 at 13:35
  • @Ingo do you have a link to the 2010 mention? – kgjenkins Dec 8 '14 at 17:34
  • This may be a bug, as reported here: http://hub.qgis.org/issues/11809 – kgjenkins Dec 10 '14 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.