Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Calculate the Geographical Distance Between Two Cities in SQL Server


By:   |   Last Updated: 2012-08-06   |   Comments (6)   |   Related Tips: More > 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 1724'45.00 S and the Longitude is 6610'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 5130'23.29'' N and the Longitude is 0001'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 [email protected])
       SET @dest = (select coordinates from [dbo].[CitiesWorld] where [email protected])
    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


Last Updated: 2012-08-06


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 

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

Thanks a lot Daniel!! Big hug from Argentina.


Thursday, February 07, 2013 - 6:38:32 PM - Brian Back To Top

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 07, 2013 - 3:34:08 PM - Brian Back To Top

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 07, 2012 - 5:44:45 PM - Daniel Calbimonte Back To Top

Thank you.

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


Tuesday, August 07, 2012 - 3:43:52 PM - TimothyAWiseman Back To Top

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.

 

 

 


Learn more about SQL Server tools