[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2010-04-03
5
Medium Priority
?
932 Views
Last Modified: 2012-05-09
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
0
Comment
Question by:MazingTree
5 Comments
 

Author Comment

by:MazingTree
ID: 29589629
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
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 332 total points
ID: 29652023
try

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
 
0
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 332 total points
ID: 29652101
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

0
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 336 total points
ID: 29653669
Degrees, Minutes, Seconds and Decimal Degrees Latitude/Longitude Conversions
http://www.fcc.gov/mb/audio/bickel/DDDMMSS-decimal.html

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
ELSE
CAST(SUBSTRING(latitude,1,1) AS int) + (CAST(SUBSTRING(latitude,4,2) + '.' + SUBSTRING(latitude,7,3) AS decimal(5,3)))/60.0
END
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.

0
 

Author Closing Comment

by:MazingTree
ID: 31710601
Thanks for the help everyone, all very useful information
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question