Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

Thanks

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

1 Answer

up vote 2 down vote accepted

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;
share|improve this answer
    
What is the equivalent for GEOGRAPHY type ? Thanks –  user1651994 Jun 11 '13 at 11:13
    
@user1651994: See the Update –  Devdatta Tengshe Jun 11 '13 at 11:14
    
I'll try it and give a feedback asap –  user1651994 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'; –  user1651994 Jun 11 '13 at 12:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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