3

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.

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.