Is it possible to update only latitude or longitude in postgis?

Question

I have a postgis database that contains Points created based on a couple (longitude, latitude), and I need to update only one of those two for a given point.

I found that it's possible to do so but to modify both of the coordinates using :

UPDATE poi SET place = ST_GeographyFromText('SRID=4326; POINT(' || "+lg+" ||' '|| "+lat+" || ')') where mykey='"+key+"';

Any help would be very appreciated.

Answer 1

Your input geometry needs to be built from the existing geometry.

If I want to just change the Y coordinate (i.e. Latitude) to 66.23, I can use the following command (when data is of type geometry):

update mytable 
       set the_geom=ST_SetSRID(ST_MakePoint(ST_X(the_geom), 66.23), 4326)
                      where gid=9;

Since your data is in Geography type, you can use a SQL command like this:

update mytable 
       set place =ST_GeographyFromText('POINT('||ST_X(place::geometry)||' 66.23)') 
              where where gid=9;