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 @earthSphereRadiusKilometers as decimal(10,6)
	DECLARE @kilometerConversionToMilesFactor as decimal(7,6)
	SELECT @earthSphereRadiusKilometers = 6366.707019
	SELECT @kilometerConversionToMilesFactor = .621371

	-- convert degrees to radians
	DECLARE @lat1Radians decimal(15,12)
	DECLARE @lon1Radians decimal(15,12)
	DECLARE @lat2Radians decimal(15,12)
	DECLARE @lon2Radians decimal(15,12)
	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]
	RETURN ROUND(2 * ASIN(SQRT(POWER(SIN((@lat1Radians - @lat2Radians) / 2) ,2)
        + COS(@lat1Radians) * COS(@lat2Radians) * POWER(SIN((@lon1Radians - @lon2Radians) / 2), 2)))
        * (@earthSphereRadiusKilometers * @kilometerConversionToMilesFactor), 4)

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