Updating Geography column by passing in lat and long for that row.

Question

Updating Geography column by passing in lat and long for that row.

.Netter

SSCrazy

Points: 2476
More actions
April 28, 2015 at 2:35 am

#300203

I have the following in my table
As you can see the Geo field is null (which is of type Geography), what I'm trying to do is populate that with an update statement by comparing the latitude and longitude for each row so I can then work out the distance, I've been following this tutorial to workout the distance which is giving me the desired results
http://weblog.west-wind.com/posts/2012/Jun/21/Basic-Spatial-Data-with-SQL-Server-and-Entity-Framework-50
The issue I have is passing in the points i.e latitude and longitude and then updating the Geo field for the row, I currently have 11938 rows in my DB so I need to run this against all of them. I thought I had it I was thinking of update then select from the source but then I realized I need to pass the lat and long in for the points and that's where I got confused 🙁

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

x SSC-Insane Points: 23660 More actions · Answer 1

I apologise for not understanding the question if thats the case, but if you just want the geography value for each latitude and longitude, you could try the "point" function and see if that does what you want. The '4326' value in the code is the "spatial reference ID (SRID)", and you might want to read up on what that is and whether what you are doing works with 4326, but you can also just read up on the whole geography topic for sql server.

CREATE TABLE #TEST

(

GEO GEOGRAPHY,

LAT FLOAT,

LONG FLOAT

)

INSERT INTO #TEST

SELECT NULL, -35.237485, 149.067248

UNION

SELECT NULL, -35.282000, 149.128684

UPDATE #TEST SET GEO = geography::Point(LAT, LONG, 4326)

Again, apologies if I'm not getting your question, it happens!

.Netter SSCrazy Points: 2476 More actions · Answer 2

Hey patrickmcginnis59 10839

Thanks for your suggestion, I can do it the way you suggested but I have 11913 rows in my table so writing out insert statements for each one will be come unpleasant.

Allow me to try and simplify my question because I've just read it back to my self and I two got a bit confused.

As you can see in the picture I have a Geo,Latitude and Longitude column.

I need to write an update statement for that table whereby it selects the latitude and longitude for each row and updates the Geo column by using the geography:point passing in the latitude and longitude for that row.

This is where I'm getting confused..

x SSC-Insane Points: 23660 More actions · Answer 3

I actually meant that I would use the update statement, the other inserts were just setting up a test to see if the update works. From your comments, I get the impression that you need to understand whats happening, this statement will update all of your rows, setting the column "GEO" to the value calculated by the function geography::Point.

Make sure you understand your needs and don't just run stuff without testing and understanding.

UPDATE YOUR_TABLE_NAME SET GEO = geography::Point(LATITUDE, LONGITUDE, 4326)

You obviously need to test that against your situation as well as really understand the statement especially in the context of what your situation is, don't run an update from the web without understanding and testing for your own situation.

If your updating from another table thats a different situation!

.Netter SSCrazy Points: 2476 More actions · Answer 4

what I'm basically doing is populating the geo column based on the latitude and longitude which belongs to each row, that way I'd have the lat, long and geo data.

The statement you provided seemed to of done the trick I created a temp table and populated it with some data from the permanent table and then your snippet. The data looks good thanks