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.