Re: [Qgis-user] Spatialite queries return text instead of numeric values
2014-12-10 1:50 GMT+01:00 Alexandre Neto senhor.n...@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Spatialite queries return text instead of numeric values
On 12/10/2014 09:55 AM, Goyo wrote: 2014-12-10 1:50 GMT+01:00 Alexandre Neto senhor.n...@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@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Spatialite queries return text instead of numeric values
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_...@wildintellect.com wrote: On 12/10/2014 09:55 AM, Goyo wrote: 2014-12-10 1:50 GMT+01:00 Alexandre Neto senhor.n...@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@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Spatialite queries return text instead of numeric values
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_...@wildintellect.com wrote: On 12/10/2014 09:55 AM, Goyo wrote: 2014-12-10 1:50 GMT+01:00 Alexandre Neto senhor.n...@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@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Spatialite queries return text instead of numeric values
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. Thanks, Alexandre Neto ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
