How to Convert Degrees, Minutes & Seconds to Google Earth format in SQL Server 2008 Database

I have Longitude and Latitude data imported into SQL Server 2008. The issue I am facing is converting this into a format which can be used with the Google Earth Plugin in C#. The expected data type is Double for example longitude(4.039), latitude(3.490), however the rows in the database are in the format of degrees, minutes and seconds for example 50:22:109. I need an sql query which will convert all the rows to the correct Double format. I am unsure of the maths or sql query that would give the desired output.

This is an example row from my table
bssid                     latitude           longitude
002222444e33     50:22:109       4:08:166
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MazingTreeAuthor Commented:
I have discovered that the figures in the database are actually only degrees and minutes therefore the above example is 50degrees and 22.109minutes. so I just need a query which will go through the rows and convert values using the maths below
degrees + (seconds/60) which would be 50 + (22.109/60) for the above example

Select z.*
      ,latdeg+latmin/60 as googlat
      ,longdeg+longmin/60 as googlong
  from (
select y.*
      ,convert(float,left(latitude,latpos-1)) as latdeg
      ,convert(float,replace(right(latitude,datalength(latitude)-latpos),':','.')) as latmin
      ,convert(float,left(longitude,longpos-1)) as longdeg
      ,convert(float,replace(right(longitude,datalength(longitude)-longpos),':','.')) as longmin
  from (
select x.*
      ,charindex(':',latitude,1) as Latpos
      ,charindex(':',longitude,1) as Longpos
  from yourtable as x
        ) as y
        ) as z
You may try following formula:
select cast(LEFT(longitude, CHARINDEX(':', longitude)-1) AS numeric(10,5))+
       cast(replace(substring(longitude, CHARINDEX(':', longitude)+1, len(longitude)), ':', '.') AS numeric(10,5))/60 AS LongitudeNumeric, 
       cast(LEFT(latitude, CHARINDEX(':', latitude)-1) AS numeric(10,5))+
       cast(replace(substring(latitude, CHARINDEX(':', latitude)+1, len(latitude)), ':', '.') AS numeric(10,5))/60 AS LatitudeNumeric
  from YourTable

Open in new window

Degrees, Minutes, Seconds and Decimal Degrees Latitude/Longitude Conversions

Try these queries:

SELECT CONVERT(int,SUBSTRING(latitude,1,2)) + (CONVERT(decimal(5,3),(SUBSTRING(latitude,4,2) + '.' +
SUBSTRING(latitude,7,3))))/60.0 FROM YourTable WHERE bssid = '002222444e33';

You can also write this as:

SELECT CAST(SUBSTRING(latitude,1,2) AS int) + (CAST(SUBSTRING(latitude,4,2) + '.' + SUBSTRING(latitude,7,3) AS decimal(5,3)))/60.0 FROM YourTable WHERE bssid = '002222444e33';

The degrees should be 2 digit one, minutes 2 digit one and seconds 3 digit one.

If it is like the data that you have given:

SELECT CASE latitude WHEN LENGTH(SUBSTRING(latitude,1,2)) = 2 THEN CAST(SUBSTRING(latitude,1,2) AS int) + (CAST(SUBSTRING(latitude,4,2) + '.' + SUBSTRING(latitude,7,3) AS decimal(5,3)))/60.0
CAST(SUBSTRING(latitude,1,1) AS int) + (CAST(SUBSTRING(latitude,4,2) + '.' + SUBSTRING(latitude,7,3) AS decimal(5,3)))/60.0
FROM YourTable WHERE bssid = '002222444e33';

You can write similarly for CONVERT. Also add one more i.e. longitude based expression similarly to SELECT list. I have not given it myself since it will look too complex.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MazingTreeAuthor Commented:
Thanks for the help everyone, all very useful information
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.