Geometry column read as Numeric after operation in SpatiaLite (QGIS)

Question

Pretty weird problem with SpatiaLite used within QGIS (2.13) with the DB Manager.

I have 2 layers (result of the same algorithm) and I use this function to merge them in a new table:

CREATE TABLE new_layer AS
SELECT * FROM lay1
UNION ALL
SELECT * FROM lay2;

The function works fine except for the fact that the new Geometry column is recognized as NUM Type and not as MULTILINESTRING (the 2 starting layers have a correct Geometry column type).

When I recover the geometry column, it is correctly recovered (so the layer is transformed into a geometric object) but still the Geometry column is a NUM type:

SELECT RecoverGeometryColumn('new_layer', 'Geometry',  3003, 'MULTILINESTRING', 'XY') 

I think that this misunderstanding of SL can cause future incoherence with spatial operations.

Some idea?

Am I missing something?

Answer 1

SQLite is using a dynamic type system and what it means is best to study from the documentation http://www.sqlite.org/datatype3.html. Here is an example that you can try yourself.

create table test (geometry text);
insert into test (geometry) values (GeomFromText('POINT (1 1)'));
insert into test (geometry) values ('bar');
SELECT geometry,typeof(geometry) from test;
Result:
geometry            typeof
==========================
BLOB sz=60 GEOMETRY blob
bar                 text

Columns named "geometry" was created as a TEXT column but there is no problem with storing a geometry BLOB into this column.

SQLite is not like other databases and it does not have fixed datatypes in the schema. You might imagine that you can read such schema of the table with PRAGMA command, but generally speaking you can't because it is possible to store data that has other datatype into all the columns.

  PRAGMA table_info(test);
  cid name type notnull dflt_value pk
  ====================================
  0 geometry    text    0   NULL    0

It does not matter if geometry column appears as NUM or TEXT in pragma, it is just how SQLite works. It is confusing and it can lead to trouble when data are converted from SQLite into other databases and therefore in the GeoPackage standard it is mandated that data types of columns appear "right" in pragma, even for SQLite they are always right.