Create new column with coordinates from centroids using st_transform in PostGIS / PostgreSQL

Question

I have geometry data that I'm trying to transform to lon lat and store in a new column. I have created a new column called 'coordinates' and written the following query:

UPDATE places_centroids
SET coordinates = st_y(st_transform(new_centroid, 4326)) AS lat,
st_x(st_transform(new_centroid, 4326)) AS lon;

But it returns: ERROR: ERROR: syntax error at or near LINE 2: ...coordinates = st_y(st_transform(new_centroid, 4326)) AS lat,

However, it works fine when i writing the query with a select statement:

SELECT st_y(st_transform(new_centroid, 4326)) AS lat,
         st_x(st_transform(new_centroid, 4326)) AS lon
FROM places_centroids;

Can anyone see what's wrong with my query?

Answer 1

You can create two columns, lat and lng for example of type double precision and do this:

UPDATE places_centroids 
SET lat = st_y(st_transform(new_centroid, 4326)),
lng= st_x(st_transform(new_centroid, 4326));

or define coordinates as the native point type:

UPDATE places_centroids
SET coordinates = point(st_y(st_transform(new_centroid, 4326)),
st_x(st_transform(new_centroid, 4326)));

I personally prefer to store in two columns.

Best regards,
Bjarni