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

7 Comments

  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.

    http://www.c-sharpcorner.com/UploadFile/afenster/how-to-find-a-doctor-near-your-home-in-sql-server/

  5. Awesome website you have here but I was curious if you knew
    of any message boards that cover the same topics discussed
    here? I’d really like to be a part of community where I can get opinions from other knowledgeable people that share the same interest. If you have any suggestions, please let me know. Appreciate it!

  6. That is really helpful but it is giving the distance in meters in my case, I am storing the coordinates in two columns:

    Latitude Longtitude
    -33.836709 151.209684

  7. Thanks! Calculations were spot on!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.