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.

  • Is your data in Geometry type or Geography? Jun 11 '13 at 10:59
  • The object I want to update is "place" and I created it this way : place GEOGRAPHY(POINT,4326)
    – Anas
    Jun 11 '13 at 11:10
  • I have added the code for geography type Jun 11 '13 at 11:14

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;
  • What is the equivalent for GEOGRAPHY type ? Thanks
    – Anas
    Jun 11 '13 at 11:13
  • @user1651994: See the Update Jun 11 '13 at 11:14
  • I'll try it and give a feedback asap
    – Anas
    Jun 11 '13 at 11:30
  • It is working fine ! Thank you :) For the ones who may be interested, I used the following query to check if everything is ok : SELECT st_X(Geometry(place)), st_Y(Geometry(place)) FROM poi where myid= 'id';
    – Anas
    Jun 11 '13 at 12:00

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.