1

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 ?

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!!

  • 3
    You can try ST_Make point too, more elegant than string concatenation, IMHO. – John Powell Dec 7 '14 at 7:59
1

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);

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.