7

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.

7

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.

  1. 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.

  1. 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.

  1. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.