A basic SQL query to validate the distance via two Latitude / Longitude Points

In this Sprint, we add a new feature called Relocation. Developers complete this feature in C# and I need to validate if the distance between stores is correct when doing relocation. No one told me how to validate the distance. As a SDET, it is important to know how to test in my own way.

After doing some research, the following Excel formula works well if you have 2 Latitude and Longitude points.

ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

However, this is not what I want. What I want to do is:

Enter one Lat/ Long store information and the nearby stores within 16 Kilometers (about 10 Miles) appear in the database. I can use the result set to compare the data in Silverlight Application.

I can’t write a Store Procedure and a User-Defined function in SQL Server because after we push to Production, those can’t be implemented in Production and I still need to validate this scenario for my smoke test. Therefore, writing a basic SQL query is a good way.  Here is my solution:

·         We have the Longitude and Latitude Columns in the table for each store.

·         The Excel Formula is still working in SQL Server 2008 R2.

·         I need to manually enter Lat1 and Long1 as my baseline to validate the distance between stores. That being said, I don’t have to enter Lat2 and Long2 information.

After my several attempts, the following SQL Query is helpful to find the nearby stores after I enter the values of Lat1 and Long1. 

Next time if I want to use another store (Lat/Long) as a baseline to search the nearby stores within 16 Kilometers, it is easy for me to get the list of data.