Creating SpatiaLite view (for use in QGIS) from non-spatial SQLite database?

Question

I have a plain SQLite database that contains X, Y coordinates in a table. Data are edited via an MS Access front-end using the ODBC driver and so I want to avoid storing them in a geometry blob that can only be edited using Spatialite.

I know that QGIS can plot tables of .csv files by specifying an X and Y column and that Arc can do a similar thing with plain SQLite tables.

How can I create a view that turns my X, Y columns into Spatialite geometry objects so that the resulting data can be viewed on a map in QGIS?

Example table structure

-- Location table (non-spatial)
CREATE TABLE location (
 id INTEGER PRIMARY KEY,
 name TEXT,
 x INT,
 y INT,
 epsg_code INT
);
INSERT INTO location VALUES
 (1, 'North', -3, 55, 4326),
 (2, 'South', 0, 52, 4326);
-- Sample data to be plotted on map
CREATE TABLE sample (
 sample_id INTEGER PRIMARY KEY,
 description TEXT,
 location INT
);
INSERT INTO sample VALUES
 (1, 'big', 1),
 (2, 'bigger', 2),
 (3, 'biggest', 2);

Example view

I would like to create a view that will let me plot the following attributes on a map:

SELECT 
 s.sample_id,
 s.description,
 loc.name
FROM
 sample AS s LEFT JOIN location as loc
 ON s.location = loc.id;

Similar questions

I am posting this question to answer it myself. I found parts of the answer in the following questions, but wanted it to be documented in a single place.

• How to make a Spatialite View (with geometries) for QGIS based on two table
• Can QGIS Read Spatialite Views?
• Spatialite view appears 'filtered' in attribute table, won't load in canvas
• Creating SpatiaLite View in QGIS?

Answer 1

The following commands can be used to add a Spatialite view to non-spatial tables in a SQLite database.

1. Add Spatialite capabilities to the database.

   SELECT load_extension('mod_spatialite');
   SELECT initspatialmetadata(1);
   

It is necessary to have Spatialite installed on the system where these commands are run (sudo apt install libsqlite3-mod-spatialite spatialite-bin on Ubuntu). The extension is already loaded if you use Spatialite-GUI or QGIS DB Manager.

2. Create reference geometry to use with view

   CREATE TABLE _point_4326 (
    id integer primary key
    );
   SELECT AddGeometryColumn('_point_4326', 'geom', 4326, 'POINT', 2);
   

Spatialite adds geometry columns to tables after they have been created. It is not possible to do this with a view. So we create a dummy table with the same geometry type as our view will use. This clever trick came from this answer.

3. Create the view

   CREATE VIEW sample_locations AS
    SELECT 
    1e6 * loc.ROWID + s.sample_id as fid,
    s.sample_id,
    s.description,
    loc.name,
    MakePoint(
    CAST(loc.x AS REAL),
    CAST(loc.y AS REAL),
    CAST(loc.epsg_code AS INT)) as geom 
    FROM
    sample AS s LEFT JOIN location as loc
    ON s.location = loc.id;
   

The MakePoint() function is used to create the geometry object from the X and Y coordinates. For the view to work in QGIS, it is necessary for it to have a unique ID column. In cases where the view doesn't have one, it is possible to create one using primary key or ROWID values from joined tables, combining them if necessary. The MakePoint function fails silently if it receives arguments of the wrong type, so we CAST them to make sure that they are correct.

4. Register the view geometry

   INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
   VALUES ('sample_locations', 'geom', 'fid', '_point_4326', 'geom', 1);
   

This final step tells Spatialite that our view contains a geometry column. It uses the unique ID and the geometry of our dummy table. Setting the read_only flag to 1 ensures that the data cannot be edited.

It is now possible to edit the sample and location tables on machines where Spatialite is not installed/accessible and to plot the data in QGIS via the Spatialite view.

>>> SELECT * FROM sample_locations;
1000001.000000 1 big North BLOB sz=60 GEOMETRY
2000002.000000 2 bigger South BLOB sz=60 GEOMETRY
2000003.000000 3 biggest South BLOB sz=60 GEOMETRY