Plot points from a Sqlite (non spatial) table as a query on the canvas instead of making a new table

Question

I have a table in an SQLite database with a non spatial table that contains 6 sets of coordinates, representing different features within a record. I would like to generate a series of layers (lets say 6 for argument's sake) and because my dataset is going to grow I don't want to create new tables each time...

I think I'd like to create query that has a geometry column plus the related columns taken from my record. That would (I hope) allow me to requery the table when the dataset is updated...

I thought about:

Select 'Point (' || x1 || ' ' || y1 ||')' as geometry, name, description, uuid from mytable

That gives me the columns, with a WKT column masquerading as my geometry. Where I get stuck is throwing this on to the canvas... I tried 'load as layer' in DB manager, but when I select geometry as my geometry column nothing is loaded to the canvas.

Answer 1

Just as I was about to post the question, I spotted: How to make a Spatialite View (with geometries) for QGIS based on two table

and the crucial item I saw in this post is the use of 'makeline' in the example - which in my case I need to use 'makepoint' and my query becomes:

Select makepoint (x1, y1) as geometry, name, description, uuid from mytable

Now when I add to canvas, with the geometry it works! So I just do the same for my other 5 pairs and I have 6 layers from the one table... No separate static tables. And with uuid within the query I can match those points back to the original table (useful when using an atlas!) How to filter features in other layers of a QGIS Atlas?

So having answered my own question, I thought it might help someone else if I post it rather than just discard...