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

[Alexandre Neto]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20141210/3d3062b8/attachment.html>