I currently have a task where I need to compare distances of latitude and longitude for specific locations and I have an Excel spreadsheet that has lots of data on latitude and longitude and need to convert those data type into geography.
The problem is, before finding out the distances, I need to convert the columns to geography. I tried using import wizard to SSMS but unfortunately, I do not have the option to change the data type to geography. Putting the data type to decimal and trying to change manually will give of an error:
Explicit conversion from data type decimal to Test.sys.geography is not allowed.
I thought of creating a table manually but the problem is the number of rows that the latitude and longitude have is HUGE.
Please assist me on the ways I could do to convert the column to geography.
You could create a new column named GeoLocation which will convert the lat long columns to geography. Perhaps you can create this column as computed column though.
ALTER TABLE [dbo].[Your Table Name]
ADD [GeoLocation] GEOGRAPHY
Then update this column using the following SQL query
UPDATE [dbo].[Your Table Name]
SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
More details can be found at this link http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx