[Qgis-user] Spatialite queries return text instead of numeric values

[Alex Mandel]

What happens if you CREATE VIEW with those tests?
Does QGIS read the VIEW differently?

Thanks,
Alex

On 12/10/2014 10:57 AM, Alexandre Neto wrote:
> Hello all,
> 
> To make some tests I have the folllowing table:
> 
> General info
> 
> 
>   Relation type:
> 
> Table
> 
> Rows:
> 
> 3
> 
> SpatiaLite
> 
> 
>   Column:
> 
> geometry
> 
> Geometry:
> 
> MULTIPOLYGON
> 
> Dimension:
> 
> XY
> 
> Spatial ref:
> 
> WGS 84 (4326)
> 
> Extent:
> 
> (unknown) (find out <action:extent/get>)
> 
> Fields
> 
> 
>   #
> 
> Name
> 
> Type
> 
> Null
> 
> Default
> 
> 0
> 
> pkuid
> 
> integer
> 
> Y
> 
> 
> 
> 1
> 
> coluna1
> 
> text
> 
> Y
> 
> 
> 
> 2
> 
> coluna2
> 
> real
> 
> Y
> 
> 
> 
> 3
> 
> coluna3
> 
> integer
> 
> Y
> 
> 
> 
> 4
> 
> geometry
> 
> MULTIPOLYGON
> 
> Y
> 
> 
> 
> 
> If I do this:
> 
> SELECT
>    *,
>     coluna2/1.0 AS test_field
> FROM
>     testes
> 
> when I open it with DBmanager the test_field return a QSTRING with type
> name TEXT
> 
> http://imgur.com/GHeMyiW
> 
> Using cast:
> 
>  SELECT
>    *,
>     cast((coluna2/1.0) as real) AS test_field
> FROM
>     testes
> 
> I get the same.
> 
> Thanks for the help.
> 
> Alexandre Neto
> 
> 
> On Wed, Dec 10, 2014 at 6:16 PM, Alex Mandel <tech_dev at wildintellect.com>
> wrote:
> 
>> On 12/10/2014 09:55 AM, Goyo wrote:
>>> 2014-12-10 1:50 GMT+01:00 Alexandre Neto <senhor.neto at gmail.com>:
>>>> Hello,
>>>>
>>>> When querying a spatialite database in the database manager, if you
>> create
>>>> some calculations (say add two columns values) the resulting column
>> returns
>>>> as TEXT instead of the expected numeric field.
>>>>
>>>> Is this intended or a bug?
>>>>
>>>> Trying to cast it as real, double or float did not work either.
>>>
>>> Calculated columns in SQLite do not have a data type, the actual
>>> values in the column do. If you execute this query:
>>>
>>> SELECT "col1" + "col2" AS "thevalue", typeof("col1" + "col2") as
>>> "thetype" FROM "table"
>>>
>>> You should have "thetype"='real' in every row (provided "col1" and
>>> "col2" always contain real values) but the column "thevalue" does not
>>> have a data type assigned.
>>>
>>> I guess QGIS needs to assign a data type to each column and chooses
>>> the more general type when the column does not have one without
>>> looking at the actual values (which could be expensive and also
>>> misleading if the data change).
>>>
>>> Goyo
>>> _______________________________________________
>>> Qgis-user mailing list
>>> Qgis-user at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>>
>>
>> There are two ways in SQLITE to get the numeric type you want, you can
>> do a CAST() or you can force it with a multiplication (*1 or *1.0). It's
>> really critical if you do a division to *1.0 somewhere in the statement
>> otherwise you'll only get an integer.
>>
>> Note this is not a QGIS specific thing, it applies to general use of
>> SQLITE.
>>
>> You said you tried a CAST, can you paste what you tried?
>>
>> Thanks,
>> Alex
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
> 
> 
> 
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
>