Search code examples
sql-serversql-server-2008sqlgeography

Update Geography column in table

I have the following table

enter image description here

As you can the Geo column (data type Geography) is null I currently have 11913 rows in this table what I'm trying to do is update the Geo column by using the following statement and populate the Geo column with the data that is provided by Geography::STGeomFromText

DECLARE @Temp TABLE
(
Id bigint,
Latitude decimal(9,6),
Longitude decimal(9,6)
)

Insert Into @Temp (Id, Latitude, Longitude)
    Select id, Latitude, Longitude from Location.Cities 
where Active = 1

Update Location.Cities
set Geo = geography::STGeomFromText (POINT(Select Latitude, Longitude from  @Temp), 4326)
where Id = -- massively confused.....

Two issues I have come against where I say Select Latitude, Longitude from @Temp it says POINT is not a recognized built-in function name and the other is how can I make sure I update the right record/row where I've selected the latitude and longitude from.

The reason I need to do this is because on our application we are allowing the end user to search by radius.

Any help would be great.

Solution

  • You don't need a temp table @Temp. You can use geography::Point directly on your table Location.Cities.

    Something like this.

    Update Location.Cities
set Geo = geography::Point(Latitude, Longitude , 4326)

    If you want to use geography::STGeomFromText, you can use it like this.

        Update Location.Cities
        set Geo = geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(30),Longitude ) + ' ' + CONVERT(VARCHAR(30),Latitude) + )',4326)