Monday, December 8, 2014

SQL Server Calculating Distance using GPS

How far is it from Colorado Springs, CO to the Limon, CO Municipal Airport? If you just blurted out 64.53 miles than you are correct. Why should you know that? I have no clue. I do know however that on several occasions I have had a list of GPS coordinates and have thought to my self, "Self! How do I compute the distance to other points using SQL Server".

Well friends today I finally needed this functionality in some side project I was working on. The goal was to allow a user to input a location, geocode that location to find Latitude and Longitude, and then look up all the other locations in the database within 250 miles of the entered location. This is actual distance, not driving distance.

I used the Google Geocode API to do all the geocoding heavy lifting. Once I had my source GPS coordinates I searched around and found a SQL Script to take in GPS coordinates and return the distance in  meters. Added some American math to get miles and boom, my work here is finished.

I had already completed a set of Python scripts to load NOAA weather data on a daily basis. Therefore the maps hack was just an extension of the already completed website.

I added a link on the Info Window for each pin that allows you to snag the weather data for the NOAA weather station you select. You just click the Get Weather Link

You are taken back to the NOAA weather data page with past 30 days of weather data for the selected NOAA weather station.

I hope to use this functionality in some of my other current and future projects. 

Here is a video demo of the calculating the distance using SQL Server.

1 comment: