Update Geography column in table

Question

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.

ughai · Accepted Answer · 2015-04-29T08:39:25.220

4

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)

edited Apr 29 '15 at 08:39

answered Apr 29 '15 at 08:33

ughai

9,830
3
29
47

I didn't realize I could do it the way you provided first, but how do I know its pulled the right latitude and longitude for each row and not selected the incorrect ones? sorry my SQL knowledge is someone limited so I'm sorry if the question I just asked is stupid. – Code Ratchet Apr 29 '15 at 08:41
Because that's the whole point of SQL. It's a row-based operation and recursively falls over each row. – John Bell Apr 29 '15 at 08:42
@ScottAtkinson - Each row will use latitude / longitude values stored in the row. – ughai Apr 29 '15 at 08:43

score 0 · Answer 2 · answered Apr 29 '15 at 08:39

0

ughai's answer will work, but you will find performance issues. You'd do better using STPointFromText:

UPDATE Location.Cities
SET Geo = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

As a side note the reason why you received the error is because the function expects a varchar parameter as it's first argument.

answered Apr 29 '15 at 08:39

John Bell

2,350
1
14
23

2

I didn't knew `STPointFromText` was faster than `Point`. Do you have any reference sources detailing the performance studies on `geography` – ughai Apr 29 '15 at 08:46

score 0 · Answer 3 · answered Apr 29 '15 at 14:56

If it were me, I'd do something like: declare @batchsize int = 1000; while(1=1) begin

    Update top(@batchsize) Location.Cities
    set Geo = geography::Point(Latitude, Longitude), 4326)
    where Geo is null;

    if (@@rowcount < @batchsize)
       break;

end

A couple of notes

The Point static method is an MS extension. That is, it's not a standard OGC method. But that's fine, it'll give you the same result. I find it to be much more readable because you don't need to create a WKT that represents the point.
I'm batching the updates. It shouldn't matter too much since your table is approx 10k rows, but it would matter if it was larger. This idiom is a good one to know.

Update Geography column in table

3 Answers3

Linked