Calculate the Geographical Distance Between Two Cities in SQL Server

By: Daniel Calbimonte   |   Updated: 2012-08-06   |   Comments (6)   |   Related: > Spatial Data Storage

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

Requirements for calculating the distance between two cities

In this tip I am using SQL Server 2012, but this sample code should work with SQL Server 2008 as well. 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 calculations in SQL Server

1. 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.

   
   
2. In order to find he coordinates for each city, I am going to use Google Earth
   
   
   
3. Now let's move to Cochabamba, Bolivia. I am going to zoom in until I can see the airport:
   
   
   
4. 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.
   
5. 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.
   
6. In the site write the latitude and longitude and press the Calc button:
   
   
   
7. 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
   

   
   
8. Now let's move to London:
   
   
   
9. I am going to Zoom in until I can see the airport:
   
   
   
10. The latitude is 51°30'23.29'' N and the Longitude is 00°01'39.13 E''.
    
11. If we repeat the step 5 and 6 with the London coordinates we will have the following results:
    
    
    
12. 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
    

    
    
13. 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
    

    
    
14. 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'
    

    
    
15. The distance between these two cities in miles is: 6186.99
    
16. In order to find the distance in kilometers, use this sentence:

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

    
    
17. The distance in Km is: 9957.01

Next Steps

• There are some pretty nice SQL Server functions to find the distance and areas using geography points. There are new applications created to find areas, perimeters and the distance between two points. If you want you can find distance between your home and your office and more.
• Review the following resources for more information:
  • STDistance function
  • STArea function
  • How to convert the coordinates
  • Using the Google Earth
  • Check out all of the tips on working with spatial data

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips