QGIS DBmanager: error creating the SQL layer

Question

i've converted an OSM file from Geofabrik to an SQLite database and loaded it into the QGIS DBManager (QGIS 3.12). I've executed the following SQL query and got a nice table as result.

SELECT * 
FROM points 
WHERE place = "city" 

Yet when I try to load the layer into the canvas using the Load button (and with the settings seen in the screenshot), I receive the following vague error:

There was an error creating the SQL layer, please check the logs for further information.

There's nothing to be seen in the logs.

Does anyone know how to fix this?

Answer 1

Some OSM column name are reserved keywords. If in use, the point of failure can happen anytime (between the table creation and the query). An example of such name is natural

If you are using any of these names, make sure the column name is double-quoted.

Alternatively, you can try to explicitly name (and double quote) the column names instead of doing select *

select gid, geometry, "natural" from ...

Answer 2

I fixed the problem with the help of a colleague in the meantime. It turned out I had converted my osm.pbf file to an SQLite without SpatiaLite, so QGIS had no geometry to look at. When I converted the file to geopackage and spatialite, I was able to load the layer into the canvas from the DBmanager (with the same query)