[Qgis-user] Spatialite queries return text instead of numeric values
Alex Mandel
tech_dev at wildintellect.com
Wed Dec 10 11:02:19 PST 2014
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
>
More information about the Qgis-user
mailing list