Step by step Finding Distance between two cities by using SQL Server

     I wrote about finding distance between two location in my older post. I have been getting question about how to make the search by using data in SQL Server. In this post, I will try to answer all these questions. You do not need to have latest version of SQL Server to do any of these examples. SQL Server has been supporting Geospatial data since 2008.

     First, we need some data. Not just some data, some free spatial data. I want to show you how to find distance between cities in this post, so I need at least names of the cities and their latitude and longitude. I downloaded this data from SimpleMaps website in CSV Format. This dataset has the following attributes

• City Name,
• State Name,
• County Name,
• Population,
• Time Zone,
• Coordination
• Zip Codes

 This is more than what I need, also it is free, so I am happy. This is a good starting point. Next, I use Management Studio's Import Flat File option. You can use my older post about this feature if you will need help to use it. After this step, we have all this data in SQL Server, I named my table Cities_US. We need to create a new column with geography data type and populate this column by using the coordination of each city.

ALTER Cities_US add geoloc geography
UPDATE Cities_US SET geoloc = geography::Point(lat,long,4326)

      We have a new column named geoloc, we generated spatial data by using the latitude and longitude of each city, Now, we can use geospatial functions of SQL Server and do some magic. 

Here is my table's schema after adding the geospatial column. 

Let's say, your user is on your web application and tries to find a ticket. User gives the current city and destination city to start the process. Web app will pass this information to the SQL Server and we will have enough information to run a query and find the distance between these two cities. For example, following query finds the distance between Boston and Los Angeles. 

SELECT currentcity.CityName, currentcity.StateCode, dest.CityName, dest.StateCode, 
currentcity.Geoloc.STDistance(dest.Geoloc) / 1609.344 as miles 
FROM Cities_US as currentcity
JOIN Cities_US as dest ON dest.CityName = 'Los Angeles' and dest.StateCode='CA'
WHERE  currentcity.CityName = 'Boston' and currentcity.StateCode='MA'