# Calculate Distance Between Locations in SQL Server 2008 using Geography

Ever had a table in SQL Server with hundreds of locations with their longitude, latitude and wanted to retrieve only top 10 which are nearest to you or those that are within couple of Kilometers away? To do so in SQL Server 2005 you needed to convert long,lat into Radians and then make a large amount of calculations to do so.

Old way:

```CREATE FUNCTION [dbo].[LatLonRadiusDistance]
(
@lat1Degrees decimal(15,12),
@lon1Degrees decimal(15,12),
@lat2Degrees decimal(15,12),
@lon2Degrees decimal(15,12)
)
RETURNS decimal(9,4)
AS
BEGIN

DECLARE @kilometerConversionToMilesFactor as decimal(7,6)
SELECT @kilometerConversionToMilesFactor = .621371

SELECT @lat1Radians = (@lat1Degrees / 180) * PI()
SELECT @lon1Radians = (@lon1Degrees / 180) * PI()
SELECT @lat2Radians = (@lat2Degrees / 180) * PI()
SELECT @lon2Radians = (@lon2Degrees / 180) * PI()

-- formula for distance from [lat1,lon1] to [lat2,lon2]

END```

Now on SQL Server 2008 which is location aware this is much more efficient and easy to do using geography. Lets say that you have a table with locations called tblLocations which has stored in two columns the longitude(location_longitude), latitude(location_latitude) and you want to calculate the distance of those from a point.

All you have to do is:

```CREATE PROCEDURE [dbo].[spGetNearLocations]
@latitude decimal(18,14),
@longtitude decimal(18,14)
AS
BEGIN
SET NOCOUNT ON;
-- @p1 is the point you want to calculate the distance from which is passed as parameters
declare @p1 geography = geography::Point(@latitude,@longtitude, 4326);

SELECT *
,@p1.STDistance(geography::Point([location_latitude], [location_longitude], 4326)) as [DistanceInKilometers]
FROM [tblLocations]
END```

Of course since you can have a column already defined in SQL Server 2008 with the geography of the location(location_geography) you can more efficiently have:

```CREATE PROCEDURE [dbo].[spGetNearLocations]
@latitude decimal(18,14),
@longtitude decimal(18,14)
AS
BEGIN
SET NOCOUNT ON;
-- @p1 is the point you want to calculate the distance from which is passed as parameters
declare @p1 geography = geography::Point(@latitude,@longtitude, 4326);

SELECT *
,@p1.STDistance([location_geography]) as [DistanceInKilometers]
FROM [tblLocations]
END``` 1. Nice code, although for SRID 4326 I’m pretty sure the distance unit returned is in meters, not kilomenters.

2. hi,
I have already 500000 places in my database and I used the SIN,COS method above to get nearest places around a given point. It´s very slow because SQLServer makes a full table scan over 500000 rows.
Do you think SQLServer.geography performs better. I don´t (full table scan is still used).

3. You used the Geography method in a procedure. How can this be done as a function?

4. That was very helpful. I just wrote an article on a similar subject, and I mentioned your article in mine. I hope you like it.