Calculate Geo Distance Using SQL Server

Introduction

In this tip, I will explain how to calculate geo distance between two geo positions using SQL server

Background

Geolocation radius searching is a common feature in most applications nowadays.
You can find out the distance between two geo locations and you can also find out nearby location within a specified radius.

Using the Code

You can calculate geo distance using spatial types - geography datatype in SQL server.
This type represents data in a round-earth coordinate system, You can find out detailed information at Spatial type: Geography

I know more detailed description will irritate you.

Let's assume we have one table with location name and its geo positions in latitude and longitude like this:

Now suppose I want to find out the distance for all showing locations from my current location, then you can calculate it with current latitude and longitude like this.

As of now, my current location is: 23.012034, 72.510754.

DECLARE
@GEO1 GEOGRAPHY,
@LAT VARCHAR(10),
@LONG VARCHAR(10)

SET @LAT='23.012034'
SET @LONG='72.510754'


SET @geo1= geography::Point(@LAT, @LONG, 4326)

SELECT LOC_ID,LOC_NAME,(@geo1.STDistance(geography::Point(ISNULL(LAT,0), _
ISNULL(LONG,0), 4326))) as DISTANCE  FROM LOCATION_MASTER

These distances are calculated in meters. You can calculate it according to your requirement.

I have converted it into Kms like this:

SELECT LOC_ID,LOC_NAME,LEFT(CONVERT(VARCHAR,_
(@geo1.STDistance(geography::Point(ISNULL(LAT,0), _
ISNULL(LONG,0), 4326))/1000)),5)+' Km' as DISTANCE FROM LOCATION_MASTER

You can also calculate location by radius.

Suppose you want to find locations in the nearby radius of 7 kms only.

DECLARE
@GEO1 GEOGRAPHY,
@LAT VARCHAR(10),
@LONG VARCHAR(10)

SET @LAT='23.012034'
SET @LONG='72.510754'

SET @geo1= geography::Point(@LAT, @LONG, 4326)

SELECT LOC_ID,LOC_NAME,LEFT(CONVERT(VARCHAR,_
(@geo1.STDistance(geography::Point(ISNULL(LAT,0), _
ISNULL(LONG,0), 4326)))/1000),5)+' Km' _
as DISTANCE from LOCATION_MASTER
WHERE (@geo1.STDistance(geography::Point(ISNULL(LAT,0), _
ISNULL(LONG,0), 4326)))/1000 < 7

In this way, geo distance calculation becomes very easy by the use of geography datatype.

Points of Interest

You can create a stored procedure in which you have to just pass current latitude, longitude, and radius and it will return records location within a radius.

CREATE PROCEDURE calculateDistance
    @RADIUS INT=0,
    @LAT VARCHAR(10)='',
    @LONG VARCHAR(10)='',
    @GEO1 GEOGRAPHY = NULL,
AS
BEGIN        
    
    SET @geo1= geography::Point(@LAT, @LONG, 4326)        
    
    SELECT TOP 10 LOC_ID,LOC_NAME,LEFT(CONVERT(VARCHAR,_
    (@geo1.STDistance(geography::Point(ISNULL(LAT,0), _
    ISNULL(LONG,0), 4326)))/1000),5)+' Km' as DISTANCE from LOCATION_MASTER
    WHERE (@geo1.STDistance(geography::Point(ISNULL(LAT,0), _
    ISNULL(LONG,0), 4326)))/1000 < @RADIUS
    
END
GO

Note: This distance will be a straight point to point distance, It will not going to be calculated as a road route.

History

• 6^th May, 2017: Initial post