# SQL Server Distance Function

### Question

• I need to create a function to calculate the distance from user inputted values of latitude and longitude, utilizing the approach- geography::Point(lat,long,4268). I also need to use the built in SQL 2008 function STDistance() in there. Any thoughts?
Monday, March 18, 2013 10:04 PM

### All replies

• Here is what I have so far, but I think this is the old way to do it. SQL 2008 has a new function, I just can't figure out how to incorporate it.

```Create Function [dbo].[Distance]
(
@Lat1 Float(18), --User inputs latitude 1 value
@Long1 Float(18), --User inputs longitude 1 value
@Lat2 Float(18), --User inputs latitude 2 value
@Long2 Float(18) --User inputs longitude 2 value

)
Returns Float(18)
AS
Begin

Declare @dLat Float(18);
Declare @dLon Float(18);
Declare @a Float(18);
Declare @c Float(18);
Declare @d Float(18);

Set @dLat = Radians(@lat2 - @lat1);
Set @dLon = Radians(@long2 - @long1);
Set @a = Sin(@dLat / 2)
* Sin(@dLat / 2)
* Sin(@dLon / 2)
* Sin(@dLon / 2);
Set @c = 2 * Asin(Min(Sqrt(@a)));
Set @d = 3956.55  * @c;
Return @d;
End ```

Tuesday, March 19, 2013 2:14 AM
• ```DECLARE @p1 geography = geography::Point(@Lat1, @Long1, 4268);
DECLARE @p2 geography = geography::Point(@Lat2, @Long2, 4268);
RETURN @p1.STDistance(@p2);```

twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

Tuesday, March 19, 2013 4:04 PM