2

How can I ensure that the SQL query will return a field containing integers and not text?

I am using a Spatialite DB for my QGIS project (QGIS 2.18). One of the task is to count the occurrences of values in an attribute (group), to join the resulting view table ("countvalues") to a spatial layer, and display the values based on the joined values.

Example of the grouping query in QGIS DB Browser:

CREATE VIEW countvalues AS 
SELECT "items"."group", COUNT("items"."group") AS "cnt"  
FROM "items"
GROUP BY "items"."group"

However, the problem is that the type of the type of the newly created column ("cnt") is unknown. So, the symbology in QGIS will allow to symbolize the values as "Categorized" but not as "Graduated". (I assume this requires numbers)

I tried the follwing to cast the field values to integer numbers:

CREATE VIEW countvalues AS 
SELECT "items"."group", CAST(COUNT("items"."group") AS INTEGER) AS "cnt"  
FROM "items"
GROUP BY "items"."group"

This did not work. If using REAL instead of INTEGER I can see that the values in the table contain a period. However, even then the data type of the "group" and "cnt" column remain empty in the field descripion of the DB Browser .

  • SQLite is very flexible with data types and they may not inherit into views. I would try if "create table as..." would give better result. – user30184 Jan 29 '18 at 21:14
  • I was thinking about that. I guess it will not be dynamic anymore then. However, I want that the values in the "countvalues" (view or table) update if changes are being made in the initial table (items). – Bushroot Jan 29 '18 at 21:31
  • @Bushroot why do you need it to be dynamic? How often does the source data in the view change? – DPSSpatial Jan 30 '18 at 0:10
  • I would still do the test because it could help in analyzing what should be done for improving QGIS. – user30184 Jan 30 '18 at 5:49
  • @DPSSpatial: Very often. The view is build on a table where I constantly update the values manually. – Bushroot Jan 30 '18 at 22:17
2

If you only need QGIS to recognise the column type for styling purposes, maybe cast it in QGIS by entering to_real("cnt") in the column text box.

  • What do you mean exactly with the "column text box"? – Bushroot Feb 5 '18 at 14:00
  • Where you normally select your column to categorise by with Graduated styling in the styling panel - it's a drop down box but also lets you enter text. It won't let you select cnt but should let you type in the above – she_weeds Feb 5 '18 at 23:56
2

I made a test with GDAL by creating one view with a field that contains integer value from the "length" function

create view test_integer as
select geom, length(state_name) as len
from states;

and another where the length is turned into string

create view test_string as
select geom, cast(length(state_name) as text) as len
from states;

Now ogrinfo reports the field types of the views correctly:

Layer name: SELECT
Geometry: Polygon
Feature Count: 49
Extent: (-124.731422, 24.955967) - (-66.969849, 49.371735)
Geometry Column = geom
len: Integer (0.0)
OGRFeature(SELECT):0
  len (Integer) = 8


ogrinfo states.gpkg -sql "select * from test_string"
INFO: Open of `states.gpkg'
      using driver `GPKG' successful.

Layer name: SELECT
Geometry: Polygon
Feature Count: 49
Extent: (-124.731422, 24.955967) - (-66.969849, 49.371735)
Geometry Column = geom
len: String (0.0)
OGRFeature(SELECT):0
  len (String) = 8

I believe that this test proves that the SQLite side works as it should and that it is also possible to recognize the data types from the view correctly because GDAL can do it. The issue is then in QGIS. I would write a question to QGIS users mailing list and depending on the answers, an enhancement ticket for QGIS.

My example is about GeoPackage but I made the same tests also with SpatiaLite and with the same results.

You should perhaps edit your title because SpatiaLite is obviously returning integers but the issue is that QGIS does not know it.

  • It does not see as if the problem was in QGIS. Even if I access the DB outside QGIS the type of the newly created field remains unknown. Hence, something goes wrong while creating the View. Since the workaround proposed by @she_weeds works fine I do not invest more time in investigating the root of the problem for the time being. – Bushroot Feb 21 '18 at 22:23
  • I accessed also the database outside QGIS with GDAL. That seems to prove that it is possible to recognize the data type but it does not guarantee that any other method except QGIS does it right. What tool did you use? – user30184 Feb 22 '18 at 6:33
  • I used sqlitebrowser (sqlitebrowser.org) – Bushroot Feb 22 '18 at 21:27

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.