1

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 ?

3
  • 2
    As separate columns aka attribute data? Or do you want to update the geometry coordinates separately?
    – mkennedy
    Jun 14, 2013 at 20:57
  • I want to update the coordinates of the geometry separately while doing an INSERT command.
    – franklynd
    Jun 14, 2013 at 21:11
  • I am still confused about what you really want to do. You use the word update and insert together. That doesn't make sense. If you insert you add a new row (with the same data types as the other rows in the table). If you update you change the value in an existing row. Jun 16, 2013 at 18:02

2 Answers 2

2

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.

1
  • I'm inserting into the database...
    – franklynd
    Jun 14, 2013 at 21:12
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.