Nirav Prabtani">
Click here to Skip to main content
15,073,344 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 6 May 2017

Stats

58.1K views
22 bookmarked

Calculate Geo Distance Using SQL Server

Rate me:
Please Sign up or sign in to vote.
4.96/5 (13 votes)
8 May 2017CPOL1 min read
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

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:

Location tag

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

location

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

location

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

LOCATIONS

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
    @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

  • 6th May, 2017: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nirav Prabtani
Team Leader eInfochips
India India



Nirav Prabtani




I am a team lead, Database Architect and Designer /Technical Architect/Analyst,
Programmer in Microsoft .NET Technologies & Microsoft SQL Server with more than
4.5 years of hands on experience.



I love to code....!!! Smile | :)



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



Comments and Discussions

 
Questionhow to Find the Lat and lon is inside the geofence ? Pin
naveen 56562-Jun-21 23:24
Membernaveen 56562-Jun-21 23:24 
AnswerRe: how to Find the Lat and lon is inside the geofence ? Pin
Nirav Prabtani20-Oct-21 3:22
professionalNirav Prabtani20-Oct-21 3:22 
QuestionInvalid column name 'LAT'. and ' Long' Pin
Member 149594208-Oct-20 9:58
MemberMember 149594208-Oct-20 9:58 
AnswerRe: Invalid column name 'LAT'. and ' Long' Pin
Nirav Prabtani20-Oct-21 3:21
professionalNirav Prabtani20-Oct-21 3:21 
QuestionWhat is the "_" in the Select Syntax? Pin
Member 1483172013-May-20 20:35
MemberMember 1483172013-May-20 20:35 
AnswerRe: What is the "_" in the Select Syntax? Pin
Nirav Prabtani20-Oct-21 3:21
professionalNirav Prabtani20-Oct-21 3:21 
QuestionGreat job ! Pin
SodyMike769-Feb-20 21:41
MemberSodyMike769-Feb-20 21:41 
AnswerRe: Great job ! Pin
Nirav Prabtani13-Feb-20 19:18
professionalNirav Prabtani13-Feb-20 19:18 
Great to know that My tip is useful to resolve an issue of somebody

Welcome !!
Nirav Prabtani

QuestionGood Job Pin
hamza soyturk26-Mar-19 9:08
Memberhamza soyturk26-Mar-19 9:08 
QuestionNot explain code properly Pin
Tridip Bhattacharjee7-May-17 22:27
professionalTridip Bhattacharjee7-May-17 22:27 
AnswerRe: Not explain code properly Pin
Nirav Prabtani7-May-17 23:43
professionalNirav Prabtani7-May-17 23:43 
GeneralRe: Not explain code properly Pin
Tridip Bhattacharjee9-May-17 2:10
professionalTridip Bhattacharjee9-May-17 2:10 
GeneralRe: Not explain code properly Pin
Nirav Prabtani9-May-17 3:04
professionalNirav Prabtani9-May-17 3:04 
QuestionRe: Not explain code properly Pin
Tridip Bhattacharjee9-May-17 22:25
professionalTridip Bhattacharjee9-May-17 22:25 
AnswerRe: Not explain code properly Pin
Chris Ross 29-May-17 23:35
MemberChris Ross 29-May-17 23:35 
GeneralRe: Not explain code properly Pin
Nirav Prabtani10-May-17 0:20
professionalNirav Prabtani10-May-17 0:20 
AnswerRe: Not explain code properly Pin
Nirav Prabtani9-May-17 23:38
professionalNirav Prabtani9-May-17 23:38 
GeneralRe: Not explain code properly Pin
Tridip Bhattacharjee9-May-17 23:41
professionalTridip Bhattacharjee9-May-17 23:41 
AnswerRe: Not explain code properly Pin
Nirav Prabtani10-May-17 0:13
professionalNirav Prabtani10-May-17 0:13 
GeneralRe: Not explain code properly Pin
Tridip Bhattacharjee10-May-17 0:16
professionalTridip Bhattacharjee10-May-17 0:16 
GeneralRe: Not explain code properly Pin
Richard Deeming11-May-17 1:05
mveRichard Deeming11-May-17 1:05 
GeneralRe: Not explain code properly Pin
Nirav Prabtani11-May-17 1:46
professionalNirav Prabtani11-May-17 1:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.