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);
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;
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.