Calculate the Geographical Distance Between Two Cities in SQL Server

By:   |   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
    Googe Earth

  3. Now let's move to Cochabamba, Bolivia. I am going to zoom in until I can see the airport:
    Googe Earth of Cochabamba, Bolivia

  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:
    Convert the latitude and longitude to a SQL Server format

  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:
    Image of Europe

  9. I am going to Zoom in until I can see the airport:
    Zoom into London 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:
    London latitude and longitude coordinates

  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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte 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


Comments For This Article




Tuesday, October 24, 2017 - 6:36:28 AM - Jan Lenders Back To Top (68723)

 

Thank you Daniel for this example. Do you happen to have an explaination for the diffrence between the query result (9957,01 km) and the distance calculation of Google maps, which is 9975.27 km.

I am experiencing such different numbers with every example, even over small distances.

Monday, November 23, 2015 - 10:13:17 AM - Damian Back To Top (39124)

Thanks a lot Daniel!! Big hug from Argentina.

Thursday, February 7, 2013 - 6:38:32 PM - Brian Back To Top (21996)

This attempt didn't work, but shows what I wanted to do. To basically calc the distance for many different points and get a record for each. I assume that my variable @Patzip can only hold one value...I need some kind of array or something?

___________________________________________

DECLARE @98001 GEOGRAPHY;
DECLARE @Patzip GEOGRAPHY;

SELECT @98001 = Coordinates  FROM         ZipCodeLKUP INNER JOIN
                      Facility ON ZipCodeLKUP.ZipCode = Facility.ZipCode
                      Where Facility.ZipCode=98001

SELECT @Patzip = Coordinates  FROM         ZipCodeLKUP INNER JOIN
                      HIDIMV_year ON ZipCodeLKUP.ZipCode = HIDIMV_year .Patzip
                      where PATZIP in ('98001', '98466','97202')
                     
SELECT @98001.STDistance(@Patzip)/1000 AS [Distance in KM]

____________________________________________

Again, was hoping that I'd get back a distance from 98001 to the other 3 zip codes. Frankly, I want it to run against every zip code in my table, not just three, but I'd settle for having to specify. Ideas? Thanks!!

Thursday, February 7, 2013 - 3:34:08 PM - Brian Back To Top (21993)

I am definitely a Spatial newbie, but my question is about how you can calculate distance for the results of query (many records, not just typing in values like your example: "EXECUTE Distance 'Cochabamba','London','miles'"

I successfully adapted to find between stated zip codes, but now want to use this procedure to find the distance from a person's zip code to the zip code of the hospital they were admitted to when I am querying to find patients by diagnosis.

In other words, I already have a query that finds the patients and what hospital they went to and I have the zipcode for each AND I've written the [Coordinates] to my ZipcodeLKUP table, so how do  I alos bring in the distance between the HospZip and the PatZip?

DECLARE @GetPatientCoords

SET @GetPatientCoords = [Discharges].[Patzip]

DECLARE @GetHospCoords

SET @GetHospCoords = [Facility].[Zipcode]


EXECUTE DistanceBetweenZips @GetPatientCoords,@GetHospCoords,'miles'

Thanks!!!

 

 

Tuesday, August 7, 2012 - 5:44:45 PM - Daniel Calbimonte Back To Top (18947)

Thank you.

Yes, it is targetted at people new to spatial data types

Tuesday, August 7, 2012 - 3:43:52 PM - TimothyAWiseman Back To Top (18946)

Thank you for providing the excellent example.

If this is targetted at people new to spatial data types in SQL server it might be worth pointing out that the second paramater in the geography::STGeomFromText, the 4326 in:

VALUES('London', geography::STGeomFromText('POINT(0.0275361 51.5064694)', 4326));

Is providing the spatial reference identifier (SRID) and that the 4326 refers to the WGS84 standard, which is one of the more commonly used coordinate systems.

 

 

 














get free sql tips
agree to terms