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 includesturns 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?
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, but I want to use QGIS and SQLite.