load geography table from table of lat/long using ST_GeographyFromText
I have a Vertica table with millions of positions (positiondate timestamp, latitude numeric, longitude numeric) and I want to load these into another Vertica table with a geography type column.
Most of the examples I have found use an insert with values as below (this works fine):
insert into table(geog) values(ST_GeographyFromText('POINT(-85 15)');
I want to insert by selecting from the positions table similar to below (this does not work: could not find function defintion):
insert into positions(geog) select ST_GeographyFromText('POINT('||cast(longitude as varchar(10)) ||' '|| cast(latitude as varchar(10))||')') from positions;
I am stumped. Any ideas or tips? I am used to casting as geography type in SQL Server, is there something similar in Vertica?
Thanks in advance for any ideas!