Calculating GeoDistance between two points

In this article, we are going to see how to calculate Geo-distance between two points using SQL server.

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

The default syntax for to convert Latitude and Longitude into Geography points is:

DECLARE

GeographyPoint1 GEOGRAPHY = Null

SET

GeographyPoint1 = geography::Point(Latitude, Longitude, 4326);

Syntax to calculate distance between two geographical points:

GeographyPoint1.STDistance(GeographyPoint2)

STDistance returns distance in metres. To get the distance in km we need to divide it by 1000

GeographyPoint1.STDistance(GeographyPoint2)/1000 –- For Kms

Let’s calculate the Geo-distance between two points Suburb (Latitude and Longitude) with a Public transport stop (Latitude and Longitude )

For this I am going to create a stored procedure named Example.

Let’s assume we have two tables Suburb and PublicTransport like this:

Our goal is to get all the Stop details within 500 mts for given suburb and display the distance between them

To calculate the Geo-distance we need two geographical points. i.e Suburb geographical point and the transport geographical point. Suburb geographical points latitude and longitude is in Suburb table and PublicTransport geographical points latitude and longitude are in PublicTransport table.

Say for example we are getting stop details for Altona, Vic we can get the latitude and longitude from Suburb table as ‘-37.8693’ and ‘ 144.8303′

We can directly convert latitude and longitude into geographical points without declaring as given below.

@Suburbgeo.STDistance(geography::Point(StopLatitude,StopLongitude,0), 4326))/1000)

To calculate the distance the procedure will be:

In real life scenario we can’t be sure whether we will have latitude and longitude values at some cases there can be null values. In that case we can avoid complexity by using ISNull function to assign 0 in place of null values.

For example:

geography::Point(ISNULL(StopLatitude,0), ISNULL(StopLongitude,0), 4326)

In the above syntax if there is any null value in the Latitude and Longitude column it will convert null value to zero.

The output for above stored procedure will be:

Our goal is to get the stop details within half km (500 metres radius). To achieve that we need to use a where clause condition in the select statement that the distance should be lessthan 0.5 like this

WHERE(@Suburbgeo.STDistance(geography::Point(ISNULL(StopLatitude,0), ISNULL(StopLongitude,0), 4326))/1000 <@0.5)

What if we need to pass the suburb name dynamically for the stored procedure. In that case we have only the suburb name, we don’t have the latitude and longitude value. To get the latitude and longitude dynamically we need to set the latitude and longitude value by fetching the data from suburb table. Let see how to do it.

DECLARE

@SuburbLatitude Float(15)= null,    — suburb latitude

@SuburbLongitude Float(15) = null   — suburb longitude

— Getting latitude and longitude value from Suburb Table

SET @SuburbLatitude = (SELECT Latitude FROM Suburb WHERE Suburb = @SuburbName )

SET @SuburbLongitude = (SELECT Latitude FROM Suburb WHERE Suburb = @SuburbName )

This stored procedure will return the desired output we want

Here while executing the query we need to pass suburb name as a parameter here.

EXEC stopdetails ‘Altona’

Let’s try again with another suburb name

Exec stopdetails ‘ Auldana’

This stored procedure will return the desired output we want

I hope this article makes a clear understanding of calculating geo-distance.