0

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?

0

In your select statement include geometry as column

  • This hasn't worked. I've also tested by trying "select geometry from ignmeta_samples" (Which has all point data correctly filled) and it returns an empty column. – IceDreamer Aug 23 '17 at 9:38

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.