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?
