Calculate the Geographical Distance Between Two Cities in SQL Server

Problem

Some SQL Server DBAs complain that it is hard to start working with the Spatial Data Types without using a real world example. In today’s tip I am going to show how to find the distance between different cities using the SQL Server geography data type and Google Earth.

Solution

In this tip I am using SQL Server 2012, but this sample code should work with SQL Server 2008 or later. Earlier versions of SQL Server did not include the geography data type. Another tool that I’ve installed for this tip is Google Earth, but you can use other tools like findlatitudelongitud.com, itouchmap.com, etc.

Getting started with the geography calculations in SQL Server

Let’s first create a table to store the cities and coordinates:

CREATE TABLE [dbo].[CitiesWorld](
 [Id] [smallint] IDENTITY(1,1) NOT NULL,
 [City] [nchar](40) NULL,
 [Coordinates] [geography] NULL,
 CONSTRAINT [PK_CitiesWorld] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The only special thing in this example is that we are using a geography data type in the column named Coordinates. In this example we are going to work with 2 cities: Cochabamba (where I live) and London.

Using Google Earth Data

In order to find he coordinates for each city, I am going to use Google Earth

Googe Earth

Now let’s move to Cochabamba, Bolivia. I am going to zoom in until I can see the airport:

Googe Earth of Cochabamba, Bolivia

Verify the latitude and longitude. As you can see in the picture, the Latitude of Cochabamba is 17°24’45.00 S and the Longitude is 66°10’16.06” W.

We need to convert this coordinates to a SQL Server format. There are many ways to do this. In this example I am going to use the earthpoint.us site.

In the site write the latitude and longitude and press the Calc button:

Convert the latitude and longitude to a SQL Server format

Insert Latitude and Longitude Data

Let’s insert the “Degrees Lat Long” information in our CitiesWorld table:

INSERT INTO [dbo].[CitiesWorld] ([City],[Coordinates])
VALUES ('Cochabamba', geography::STGeomFromText('POINT(-66.1711278 -17.4125)', 4326));
GO

Now let’s move to London:

Image of Europe

I am going to Zoom in until I can see the airport:

Zoom into London airport

The latitude is 51°30’23.29” N and the Longitude is 00°01’39.13 E”.

If we repeat the step 5 and 6 with the London coordinates we will have the following results:

London latitude and longitude coordinates

Now we are going to insert this information in the table:

INSERT INTO [dbo].[CitiesWorld]([City],[Coordinates])
VALUES('London', geography::STGeomFromText('POINT(0.0275361 51.5064694)', 4326));
GO

Create Stored Procedure to Calculate Distance

Now, let’s find the distance by plane between London and Cochabamba. To do this I created the following stored procedure:

CREATE PROCEDURE Distance
@cityor varchar(50),@citydest varchar(50),@unit varchar(5)
as
   declare @or geography, @dest  geography
   SET @or = (select coordinates from [dbo].[CitiesWorld] where city=@cityor)
   SET @dest = (select coordinates from [dbo].[CitiesWorld] where city=@citydest)
IF @unit='miles'
 SELECT @or.STDistance(@dest)/1609.344
ELSE
 --Else show the distance in km
 SELECT @or.STDistance(@dest)/1000

The following procedure finds the distance between two points stored in the CitiesWorld table. This stored procedure is using the STDistance function. You can find the distance in Kilometers or Miles. To find the distance in miles use the following code:

EXECUTE Distance 'Cochabamba','London','miles'

The distance between these two cities in miles is: 6186.99

In order to find the distance in kilometers, use this sentence:

EXECUTE Distance 'Cochabamba','London','km'

The distance in Km is: 9957.01

Next Steps

Related Posts

Leave a Reply

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