Is there a good way to use Spatialite in QGIS?

Question

I am somewhat disheartened by my experience with Spatialite databases in QGIS. In theory, it could be what personal geodatabases are for ArcGIS: a consolidated, portable format that keeps projects clean. In reality, I run into problems though, that make it very cumbersome to use Spatialite in QGIS:

• Data types do not get assigned properly, most fields get TEXT, even if the CREATE statement stated otherwise. This makes form creation impossible since it depends on the data type of a field.
• The GUI accepts Spatialite, however, the output format is almost always a Shapefile, truncating attribute names in the process… Boolean operations between Shapefiles and Spatialite do not work at all…
• The functionality to work with other formats is well hidden in the UI. in the Toolbox there are the socalled "QGIS-Geo-Algorithms" that basically do all the Vector functions with other formats as output. However, it is not possible to specify a spatialite table as the target, it just works with spatialite databases.

Is there a good way to use Spatialite in QGIS?

Answer 1

I increasingly use Spatialite with QGIS effectively as a good, portable store for projects - and would say that the quick answer is 'yes, it's usable'.

• Store and access spatial and non-spatial tables in one place;
• Create views with linked data easily;
• Store style data together with tables / layers.

However - I am also occasionally frustrated by some of the limitations - QGIS doesn't always (rarely?) let you specify a Spatialite layer as an input for operations as you mention. However, the Processing toolbox gives you access to all and more of the operations and usually lets you use Spatialite canvas layers - and if you invest some time in learning how to do spatial operations directly with SQL statements (DB manager) it will be seriously rewarding.

Regarding data types - Spatialite (SQLite) uses dynamic typing and will change data types on the fly, but you can set up constraints in the table definition to enforce strong typing to an extent.

CREATE TABLE test (
    pk    INTEGER PRIMARY KEY,
    geom  POINT,
    myint INTEGER,
    ckint INTEGER CHECK (TYPEOF(ckint) == 'integer') 
);

Edit 2016-10-05 testing this setup:

This works:

sqlite> insert into test (myint, ckint) VALUES (1, 1);

Trying to add text values doesn't work:

sqlite> insert into test (myint, ckint) VALUES ('2', '2');
Error: CHECK constraint failed: test

Trying to insert text into the first (non-checked) integer field still works, and note that the attempt above with '2' didn't get added:

sqlite> insert into test (myint, ckint) VALUES ('two', 2);
sqlite> select * from test;
1||1|1
2||two|2