CODEWITHSUNDEEP
sqlfloating-pointsql-server-2008-r2latitude-longitude
Risa Marie
Risa Marie

Reputation: 27

SQL Calculating distance using Long/Latitude & Converting Int. to Decimal

Original Post: Changing integer to floating point and adding decimal point

So here's the back story: I'm working with a database that receives GPS coordinates from vehicles and I need to figure out the mileage of those vehicles based on the Longitude and Latitude values. However, in the database both columns Longitude/Latitude are "Int/Not null" and have no decimal places. So they look like this:

Latitude Longitude 
36158500 115949833
36340000 115914667
36153488 115944875

and I need it to look like this: 

Latitude Longitude 
36.158500 115.949833  
36.340000 115.914667 
36.153488 115.944875

With Tim Lehner's help, we figured out how to get the decimal point, but I can't use the formula I already created to figure out the mileage. I have to be able to search for the long/lat values based on the radio_name so that I can look at each vehicle's mileage individually, which is why I created a temp table called CTE and CTE2. However, the code will not work if I call the temp table because it says it's an invalid object. Now the code works fine if I plug in "AVL" (the original table's name) versus using CTE2 on my last statement below. However, the mileage isn't calculated using the new lat/long values.

So just as a recap: the columns are Latitude, Longitude. We need to convert these columns to place a decimal point after 2 characters in latitude, and after 3 characters in longitude. Then we need a column named Miles which calculates the mileage between the latitude and longitude values given.

Any tips, help, pointers, etc. are all greatly appreciated! Thank you! 

USE [system]
GO
With CTE as 
(SELECT * FROM AVL 
WHERE (DATE_TIME between '01/30/2013 00:00:00' AND 
'01/30/2013 23:59:59') AND radio_name = 'MAS7'),
CTE2 as 
(select *,row_number() over(partition by Vehicle_ID order by Date_Time) as RN
FROM CTE)

SELECT *, sqrt((69.1*(previous.Latitude - next.Latitude))*
     (69.1*(previous.Latitude-next.Latitude)) +
     (69.1*(previous.Longitude-next.Longitude)) *
     cos(next.Latitude/57.3) * (69.1*(previous.longitude-next.Longitude)) *
     cos(next.Latitude/57.3)) as Miles
From CTE2 as Previous
Join CTE2 as Next
On previous.Vehicle_ID = Next.Vehicle_ID
AND Previous.RN = 
Next.RN - 1
select cast(Latitude / 1000000.0 as decimal(10, 6)) as Latitude
  , cast(Longitude / 1000000.0 as decimal(10, 6)) as Longitude
from cte2

****EDIT****
Desired outcome would look something similar to this: 

Latitude Longitude  Mileage Radio_Name
36.158500 115.949833 3.444  MAS7
36.340000 115.914667 3.443  MAS7
36.153488 115.944875 4.544  MAS7

(That's not the correct mileage but it's just an example of what I'm looking for as the outcome.)

Upvotes: 1

Views: 895

Answers (1)

Tim Lehner
Tim Lehner

Reputation: 15251

Please note the difference between tamp tables, table variables and CTEs.

You are currently using Common Table Expressions, or CTEs, which are more akin to derived tables, as they can be used for only one select (or insert, update, etc.) statement that directly follows them (though you can declare more than one CTE for a statement, as you have done).

As such, by the time you get to your second select statement, your CTEs have gone out of scope, and you can no longer refer to them (or you have to declare them again). You may want to use a table variable or a temp table in this case.

You might easily create a temp table by converting your CTE definitions like the following:

-- Make a temp table
SELECT *
INTO #TempTableName -- Create a new temp table
FROM AVL 
WHERE DATE_TIME between '01/30/2013 00:00:00' AND '01/30/2013 23:59:59'
    AND radio_name = 'MAS7'

-- Check out our new temp table
SELECT * FROM #TempTableName

-- Clean up our temp table, though this will automatically happen once the connection is dropped
DROP TABLE #TempTableName

Also, when working with temp tables, I'll often check for and drop them at the beginning and end of my procs with something like the following:

if object_id('tempdb..#TempTableName') is not null begin
    drop table #TempTableName
end

With this stuff in your code, please let us know if you have difficulty performing the actual distance calculations.

Upvotes: 2

Related Questions