Forums (Obsolete)

Kentico Legend

Brenden Kehren - 8/10/2012 11:01:26 AM

Distance between pairs of Latitude and Longitude

I have a search page that allows a user to bring up a list of locations based on zip code, city and state. How can I get the distance between a particular location and display it within the Google Map webpart?

For instance if someone enters the city of Minneapolis, MN and there is a location in St. Paul, MN, I'd like my list to display the results with the distance between the search location (Minneapolis) and the actual location (St. Paul). As well as have a Google map with all the points on it and have the distance between point A and B as well.

Been struggling with this one for a while, especially the distance part so any help would be great.

Certified Developer 8

Jiveabillion - 8/13/2012 2:21:17 PM

RE:Distance between pairs of Latitude and Longitude

I've done this before with a zipcode database that included latitude and longitude. I used a database function to calculate the distance using math that I don't understand. It's complicated because of the curvature of the earth. Here is an example:


Create Function [dbo].[fnGetDistance]
(
      @Lat1 Float(18), 
      @Long1 Float(18),
      @Lat2 Float(18),
      @Long2 Float(18),
      @ReturnType VarChar(10)
)

Returns Float(18)

AS

Begin

      Declare @R Float(8);
      Declare @dLat Float(18);
      Declare @dLon Float(18);
      Declare @a Float(18);
      Declare @c Float(18);
      Declare @d Float(18);

      Set @R = 
            Case @ReturnType 
            When 'Miles' Then 3956.55 
            When 'Kilometers' Then 6367.45
            When 'Feet' Then 20890584
            When 'Meters' Then 6367450
            Else 20890584 -- Default feet (Garmin rel elev)
            End

      Set @dLat = Radians(@lat2 - @lat1);

      Set @dLon = Radians(@long2 - @long1);

      Set @a = Sin(@dLat / 2) 
                 * Sin(@dLat / 2) 
                 + Cos(Radians(@lat1))
                 * Cos(Radians(@lat2)) 
                 * Sin(@dLon / 2) 
                 * Sin(@dLon / 2);
      Set @c = 2 * Asin(Min(Sqrt(@a)));

      Set @d = @R * @c;
      Return @d;

End

Certified Developer 8

Jiveabillion - 8/13/2012 2:24:12 PM

RE:Distance between pairs of Latitude and Longitude

I remember also using that function to get a radius search. I can't remember exactly how though. I do remember that I basically calculated the starting point and then ran a query that calculated the distance from that point for all of the rows of data and filtered them where the distance was within some parameter.

First thing you need is a zipcode database if you don't have one yet.

Kentico Legend

Brenden Kehren - 12/21/2012 7:56:23 AM

RE:Distance between pairs of Latitude and Longitude

Been doing some research on this and found that in MS SQL 2008 and higher you can use Spatial Data. Here is an example of a query that takes a lat and long as parameters and gets all the locations from a custom table and orders them by closest to furthest away.

SELECT *, GEOGRAPHY::Point(Latitude, Longitude, 4326).STDistance('POINT(' + CAST(@CenterLongitude AS NVARCHAR(100)) + ' ' + CAST(@CenterLatitude AS NVARCHAR(100)) + ')')/1609.344 AS ProxDistance
FROM Custom_Location
ORDER BY ProxDistance

The ProxDistance field is converted to miles (<value>/1609.344) so if you don't want miles, remove that.