QGIS: SQLite DB with point data in lat, long columns (no geometry columns)

Question

I have a SQLite database that I cannot modify (it is a local copy of a database sync'd from elsewhere).

I would like to view vector (point) data from a table in this database in QGIS. The table contains point data with the location (in EPSG:2193) in columns named 'lat' and 'long'.

If I export the table to a CSV, I can load it in QGIS as a point vector layer and display points based on the lat, long columns.

If I open the .sqlite file as a vector layer in QGIS (selecting the correct table), then QGIS will only open it as a table, not a vector layer.

I see solutions online to update the table to include a WKT_GEOMETRY field, or create virtual table that QGIS can read. But these will be lost each time the local database file is updated from the remote server.

Is there a solution to directly open the SQLite database file as a vector layer using the lat, long columns as QGIS allows for 'delimited text' files?

Answer 1

You can use a virtual layer. This will be updated when the local database file is updated. Though the update isn't visible until the map canvas is refreshed/redrawn (pan, zoom, reopen QGIS etc...)

Open the Data Source Manager (or you can also use the SQL window of DB Manager Plugin) and use a SQL query something like:

SELECT fid, MakePoint(lon, lat, 4326) as geometry from points