# Calculating Distances Between Latitude and Longitude – T-SQL – Haversine [Answered]RSS

## 10 replies

Last post Sep 29, 2009 11:11 AM by seventeen

Member

9 Points

28 Posts

### Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 26, 2009 01:43 PM|seventeen|LINK

Hey im trying to use the Haversine formula in my sql and i found this :

http://weblogs.asp.net/jimjackson/archive/2009/02/13/calculating-distances-between-latitude-and-longitude-t-sql-haversine.aspx

which i thought was perfect, but i used these values :

lat2 - 52.9558348

lat1- 52.9253057

long2 - -1.1535073

long1 - -1.1072727

which is 3.7 miles apart, now heres my t-sql it returns 0.

SELECT CONVERT(Float(8), 39600 * (2 * ASIN(MIN(SQRT(SIN(RADIANS(@lat2 - @lat1) / 2) * SIN(RADIANS(@lat2 - @lat1) / 2) + COS(RADIANS(@lat1))
* COS(RADIANS(@lat2)) * SIN(RADIANS(@long2 - @long1) / 2) * SIN(RADIANS(@long2 - @long1) / 2)))))) AS Expr1

can anyone tell me why this returns 0, thanks Simon

• ### limno

All-Star

119327 Points

8999 Posts

Moderator

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 26, 2009 03:04 PM|limno|LINK

```declare @Lat1 Float(8),
@Long1 Float(8),
@Lat2 Float(8),
@Long2 Float(8)

set @lat2 = 52.9558348
set @lat1= 52.9253057
set @long2 = -1.1535073
set @long1 = -1.1072727

--which is 3.7 miles apart, now heres my t-sql it returns 0.
select [dbo].[fnGetDistance](@lat1,@long1,@lat2,@long2,'Miles')
--2.8542

SELECT CONVERT(Float(8), 3956.55  * (2 * ASIN(MIN(SQRT(SIN(RADIANS(@lat2 - @lat1) / 2) * SIN(RADIANS(@lat2 - @lat1) / 2) + COS(RADIANS(@lat1)) * COS(RADIANS(@lat2)) * SIN(RADIANS(@long2 - @long1) / 2) * SIN(RADIANS(@long2 - @long1) / 2)))))) AS Expr1
--2.854201```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Member

9 Points

28 Posts

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 26, 2009 10:29 PM|seventeen|LINK

is that SQL? sorry, this throws me some errors. Do i just copy and paste it in? Thanks Si

Star

12324 Points

2771 Posts

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 26, 2009 10:37 PM|mendhak|LINK

Did you click 'view plain' before copy-pasting?  You should be able to copy that into a new query window to test the results.  Works alright for me.

My Flickr

All-Star

22069 Points

7804 Posts

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 27, 2009 04:06 AM|Naom|LINK

Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

Visit my blog

Microsoft Community Contributor 2011-12

Member

9 Points

28 Posts

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 27, 2009 10:59 AM|seventeen|LINK

Ahh, sorry fee like an idiot thanks a million to all of you ! :)

Si

Member

9 Points

28 Posts

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 27, 2009 05:55 PM|seventeen|LINK

hey, sorry, i replaced my numbers with variables, and now it again returns 0, why does it work with set values, and not if i enter variales? thanks Si

SELECT CONVERT(Float(16), 3956.55 * (2 * ASIN(MIN(SQRT(SIN(RADIANS(CONVERT(float(8), @lat2) - CONVERT(float(8), @lat1)) / 2)
* SIN(RADIANS(CONVERT(float(8), @lat2) - CONVERT(float(8), @lat1)) / 2) + COS(RADIANS(CONVERT(float(8), @lat1))) * COS(RADIANS(CONVERT(float(8),
@lat2))) * SIN(RADIANS(CONVERT(float(8), @long2) - CONVERT(float(8), @long1)) / 2) * SIN(RADIANS(CONVERT(float(8), @long2) - CONVERT(float(8),
@long1)) / 2)))))) AS Expr1

Star

12324 Points

2771 Posts

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 27, 2009 06:30 PM|mendhak|LINK

You are passing in decimals/floats from your code, right, and not ints?  Check to make sure that the calling code isn't passing ints or that you're somehow losing your decimal places somewhere.  Are @lat1, etc declared as FLOATs too?

My Flickr

Member

9 Points

28 Posts

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 27, 2009 09:54 PM|seventeen|LINK

They are actually query strings. So i have to convert them to floats so i used CONVERT(float(8), @lat1) etc. should i be using a different converting them to decimals? is that possible? Si

Star

12324 Points

2771 Posts

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 28, 2009 07:48 AM|mendhak|LINK

No, that's fine.  I tested out the SQL in your latest post.  I also tried it by calling the query via values populated by a query string.  Can you show us how you're calling the code - starting from reading the querystring value all the way until your SqlCommand.

My Flickr

Member

9 Points

28 Posts

### Re: Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

Sep 29, 2009 11:11 AM|seventeen|LINK

Sorry, had the longitude going into the lat variable, and vice versa, feel stupid, but its sorted  thanks for you help everyone Si