How to get the geometry value from coordinates

Question

I have a table with empty geometry column. I have two columns in the same table which has xcoordinate and ycoordinate values. I want to get the geometry value formed by these two coordinates. The geometry is POINT so I am using st_geomfromtext('POINT(x y)') function.

I tried below syntax to update the geometry column the_geom in table 't1':

 update t1 set the_geom=st_geomfromtext('POINT('||t1.xcoordinate||t1.ycoordinate||' )');

However this gives an error that :

"POINT(3458196941 )" <-- parse error at position 17 within geometry

I am not using '||' string concatenation correctly here. Can any one correct me ?

Answer 1

I figured it out, It should be like this:

update t1 set the_geom=st_geomfromtext('POINT('||t1.xcoordinate||' '||t1.ycoordinate||' )');

It worked fine!!

Answer 2

Rather than formatting text for WKT, you should directly use the numeric coordinate data since it is faster and lossless:

update t1 set the_geom = ST_MakePoint(t1.xcoordinate, t1.ycoordinate);

And if you also need to assign an SRID:

update t1 set the_geom = ST_SetSRID(ST_MakePoint(t1.xcoordinate, t1.ycoordinate), 4326);