Creating point geometry from varchar (x,y)

Question

I have a large table of locations. The locations are in varchar written as (x,y). I need to create a individual point for all of the locations. I have created a point geometry column in the table but can't figure how to create points for the locations from the format that they are in.

geodata coordinates are in a single column of (x,y) e.g (52.3852758,4.8682022)

I am using this script but getting error.

update "tlblocation" set geom=ST_GeomFromText(geodata);

Any help would be much appreciated

Answer 1

You can use ST_* functions. ie:

drop table if exists geosample;
CREATE TABLE geosample(gid serial PRIMARY KEY,  geog geography(POINT) null, pointAsVarchar varchar(100));

insert into geoSample (pointAsVarchar) values
('(-74.06414465351811,40.70677298472955)'),
('(-122.33383026916862,47.62266259856472)'),
('(2.3501235468097144,48.86267330918897)');


update geoSample set geog = st_pointfromtext('POINT'||replace(pointAsVarchar,',',' ')) where 1=1;

select *, st_astext(geog) from geosample;