15,921,062 members
Articles / Database Development / SQL Server
Tip/Trick

# Calculate Geo Distance Using SQL Server

Rate me:
8 May 2017CPOL1 min read 75.8K   22   22
How to calculate distance between two geolocations or find out distance within a specific radius by SQL Server

## Introduction

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

## Background

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.

SQL
```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:

SQL
```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.

SQL
```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.

SQL
```CREATE PROCEDURE calculateDistance
@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), _

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

• 6th May, 2017: Initial post

Written By
India

## Nirav Prabtani

### I love to code....!!!

#### My recent past includes my work with the education domain as a technical business requirement analyst, database architect & designer and analyst programmer; just love my involvement with the world of knowledge, learning and education and I think I know quite well what I want to do in life & in my career. What do I like? Well, ideation, brainstorming, coming up with newer and more creative ways of doing things; each time with an enhanced efficiency. An item in my day's agenda always has a task to look at what I did yesterday & focus on how I can do it better today

 Contact Me Nirav Prabtani Mobile : +91 738 308 2188 Email : niravjprabtani@gmail.com My Blog: Nirav Prabtani

 First Prev Next
 how to Find the Lat and lon is inside the geofence ? naveen 56562-Jun-21 23:24 naveen 5656 2-Jun-21 23:24
 Re: how to Find the Lat and lon is inside the geofence ? Nirav Prabtani20-Oct-21 3:22 Nirav Prabtani 20-Oct-21 3:22
 Invalid column name 'LAT'. and ' Long' Member 149594208-Oct-20 9:58 Member 14959420 8-Oct-20 9:58
 Re: Invalid column name 'LAT'. and ' Long' Nirav Prabtani20-Oct-21 3:21 Nirav Prabtani 20-Oct-21 3:21
 What is the "_" in the Select Syntax? Member 1483172013-May-20 20:35 Member 14831720 13-May-20 20:35
 Why is there an underscore "_" between the two ISNULL in the SELECT statement? This caused an error. SELECT LOC_ID,LOC_NAME,(@geo1.STDistance(geography::Point(ISNULL(LAT,0), _ ISNULL(LONG,0), 4326))) as DISTANCE FROM LOCATION_MASTER
 Re: What is the "_" in the Select Syntax? Nirav Prabtani20-Oct-21 3:21 Nirav Prabtani 20-Oct-21 3:21
 Great job ! SodyMike769-Feb-20 21:41 SodyMike76 9-Feb-20 21:41
 Re: Great job ! Nirav Prabtani13-Feb-20 19:18 Nirav Prabtani 13-Feb-20 19:18
 Good Job hamza soyturk26-Mar-19 9:08 hamza soyturk 26-Mar-19 9:08
 Not explain code properly Tridip Bhattacharjee7-May-17 22:27 Tridip Bhattacharjee 7-May-17 22:27
 Re: Not explain code properly Nirav Prabtani7-May-17 23:43 Nirav Prabtani 7-May-17 23:43
 Re: Not explain code properly Tridip Bhattacharjee9-May-17 2:10 Tridip Bhattacharjee 9-May-17 2:10
 Re: Not explain code properly Nirav Prabtani9-May-17 3:04 Nirav Prabtani 9-May-17 3:04
 Re: Not explain code properly Tridip Bhattacharjee9-May-17 22:25 Tridip Bhattacharjee 9-May-17 22:25
 Re: Not explain code properly Chris Ross 29-May-17 23:35 Chris Ross 2 9-May-17 23:35
 Re: Not explain code properly Nirav Prabtani10-May-17 0:20 Nirav Prabtani 10-May-17 0:20
 Re: Not explain code properly Nirav Prabtani9-May-17 23:38 Nirav Prabtani 9-May-17 23:38
 Re: Not explain code properly Tridip Bhattacharjee9-May-17 23:41 Tridip Bhattacharjee 9-May-17 23:41
 Re: Not explain code properly Nirav Prabtani10-May-17 0:13 Nirav Prabtani 10-May-17 0:13
 Re: Not explain code properly Tridip Bhattacharjee10-May-17 0:16 Tridip Bhattacharjee 10-May-17 0:16
 Re: Not explain code properly Richard Deeming11-May-17 1:05 Richard Deeming 11-May-17 1:05
 Re: Not explain code properly Nirav Prabtani11-May-17 1:46 Nirav Prabtani 11-May-17 1:46
 Last Visit: 31-Dec-99 18:00     Last Update: 21-Jun-24 7:52 Refresh 1