7

Is it different to update geography column in sql server than a regular field( varchar....). Can you please provide a sample statement to do this. thanks.

dotnetrocks
  • 2,589
  • 12
  • 36
  • 55

2 Answers2

15

I am not sure if this is the answer you are looking for - but as I would say, the main difference is that when updating a "regular field", you typically provide directly the new value - for example:

UPDATE mytable SET name = 'John' WHERE id = 1

When updating a geography column, you probably cannot provide the value directly (since it is a very long hexadecimal number, which encodes the geoghraphy information) but you will want to compute it from some other values (which can, but do not have to be columns of the same table), e.g.:

UPDATE mytable SET gps=geography::STPointFromText('POINT(' + lng + ' ' + lat + ')', 4326)

where lng and lat are varchar values specifying the GPS coordinates in a "human-readable" format (like lat = '48.955790', lng = '20.524500') - in this case they are also columns of mytable.

Helena
  • 623
  • 1
  • 6
  • 12
  • Thanks for this, I had no idea how these geography data types worked. Apparently the Import/Export Wizard in Sql Server 2008 R2 does not understand either - it would not let me import a table containing this column into another database. – Chris Smith Feb 01 '13 at 02:28
  • I came back to this answer years later and second guessed the LONG then LAT parameter order to the POINT() call. It is the correct order and its interesting that MS uses LONG/lat as opposed to the way most of us talk which is in terms of lat/long. More info here http://stackoverflow.com/q/27297113/194872 – Chris Smith May 12 '15 at 18:13
  • Great answer, I just had to make one small tweak to avoid an issue converting types: `geography::STPointFromText(CONCAT('POINT(', Lng, ' ', Lat, ')'), 4326)` – Rory McCrossan Oct 01 '21 at 10:21
5

If you have Latitude and Longitude as decimals, you can update a geography column as shown below: 

DECLARE @latitude DECIMAL(15,6)
    ,@longitude DECIMAL(15,6);

SET @latitude = 29.938580;
SET @longitude = -81.337384;

UPDATE Properties
SET Geog = GEOGRAPHY::Point(@latitude, @longitude, 4326)
WHERE PropertyID = 858;
Paul Van Gundy
  • 129
  • 3
  • 4