Insert into X coordinate separately?

Question

I have a postgis table with a point geometry for lat/long but i want to run a query that inserts a new row with lat, long values separately instead of concatenating them into a geometry.

so is there a way to insert the X and Y values separately ?

Answer 1

select st_x(geom) as x, st_y(geom) as y from the_table;

EDIT: As mentioned in my comment to your question it is not clear what you want to do.

Do you want to insert two new columns in your table that holds x and y values separately? That will look something like:

/*Add the two columns*/
ALTER TABLE the_table ADD COLUMN x float;
ALTER TABLE the_table ADD COLUMN y float;

/*put values in the new columns)*/
UPDATE the_table SET x=ST_X(geom), y=ST_Y(geom);

Or do you want a new table with the x and y in separate columns instead:

CREATE TABLE my_xy_table AS
SELECT ST_X(geom) as x, ST_y(geom) as y FROM the_table;

But if you want to insert new rows in the old table that instead of having a geometry datatype should have two columns of type float, then you will have problems. You can not mix rows with different data types in a table.

Answer 2

ST_Y and ST_X

alter table <myschema>.<mytable> add column x_coords_4326 float4;
update <myschema>.<mytable> as a set x_coords_4326 = st_x(st_transform(a.geom,4326));

alter table <myschema>.<mytable> add column y_coords_4326 float4;
update <myschema>.<mytable> as a set y_coords_4326 = st_y(st_transform(a.geom,4326));