By: Daniel Calbimonte | Last Updated: 2012-08-06 | Comments (6) | Spatial Data Storage
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.
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
- 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.
- In order to find he coordinates for each city, I am going to use Google Earth
- Now let's move to Cochabamba, Bolivia. I am going to zoom in until I can see the airport:
- 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:
- 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:
- I am going to Zoom in until I can see the 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:
- 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
- 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
- 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
- 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:
Last Updated: 2012-08-06
About the author
View all my tips