[GIS] Adding and filling GEOMETRY column in new spatialite

Question

I have three tables. One has no geometry. The second is POINT type, and shows up correctly on the map. The third is, for the moment, a simple checker table, don't worry about it.

I am able to perform a table join using the following query:

select * 
from ignmeta_ages join ignmeta_samples on 
(ignmeta_ages.sampleid=ignmeta_samples.sampleid)
where ignmeta_ages.sampleid in (select sampleid from ignmeta_samples) and 
ignmeta_ages.sampleid not in (select sampleid from 
ignmeta_sample_ages_merge)

This correctly outputs a join of the two. However, the GEOMETRY column is empty of data, and I am completely unable to get it to the point where I can load it as a layer and have the points properly positioned.

What commands should I try for making the data update? X and Y coordinates are found in columns in the second table labelled longitude and latitude.

I have saved a copy of this query to a new table named temp. Running this command makes the GEOMETRY column NULL filled, rather than empty:

update temp set geometry = MakePoint(temp.longitude, temp.latitude, 4326)

I get the feeling this should be the way to go, but it's not working properly.

EDIT:
Some progress (I think). After amending my query to name each column rather than using *, and NOT selecting the old GEOMETRY column, I was able to use

select AddGeometryColumn('temp', 'GEOMETRY', 4326, 'POINT', 'XY')

To add a column of NULL. I then used this:

update temp set GEOMETRY = MakePoint(25, 50, 4326) where ogc_fid=325

And was rewarded with one field that imported properly, with spatial data. My question now turns to 1: How do I replace 25 and 50 with longitude and latitude from the table? 2: How do I have this go down the table and apply it to each row respectively?

Answer 1

In your select statement include geometry as column