0

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Zafear
  • 13
  • 4

2 Answers2

1

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

https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/point-geography-data-type?view=sql-server-ver15

Kalyan
  • 1,200
  • 1
  • 11
  • 23
0

Import you Excel spreadsheet as a working table, then, make a INSERT INTO SELECT that transform to geometry you decimal

Take a look here could help you :

Create Geometry/Geography Field from Latitude & Longitude fields (SQL Server)

Blag
  • 5,818
  • 2
  • 22
  • 45
  • Hi thanks for the comment, correct me if I'm wrong, basically what I should do is just import the excel to the SSMS, making the lat and longitude to decimal. From there, I should combine insert into select with the query that Juan Carlos Oropeza user has posted? – Zafear May 04 '20 at 00:06
  • @Zafear yes, that was that, but I see Kalyan already give you the SQL (with update instead on insert into select, but that's the same). – Blag May 04 '20 at 08:09