none
SQL Server Distance Function

    問題

  • I need to create a function to calculate the distance from user inputted values of latitude and longitude, utilizing the approach- geography::Point(lat,long,4268). I also need to use the built in SQL 2008 function STDistance() in there. Any thoughts?
    2013年3月18日 下午 10:04

解答

所有回覆

  • Here is what I have so far, but I think this is the old way to do it. SQL 2008 has a new function, I just can't figure out how to incorporate it.

    Create Function [dbo].[Distance] 
    ( 
          @Lat1 Float(18), --User inputs latitude 1 value 
          @Long1 Float(18), --User inputs longitude 1 value 
          @Lat2 Float(18), --User inputs latitude 2 value 
          @Long2 Float(18) --User inputs longitude 2 value 
        
    ) 
    Returns Float(18) 
    AS 
    Begin 
      
          Declare @dLat Float(18); 
          Declare @dLon Float(18); 
          Declare @a Float(18); 
          Declare @c Float(18); 
          Declare @d Float(18); 
        
          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 = 3956.55  * @c; 
          Return @d; 
    End 

    2013年3月19日 上午 02:14
  • DECLARE @p1 geography = geography::Point(@Lat1, @Long1, 4268);
    DECLARE @p2 geography = geography::Point(@Lat2, @Long2, 4268);
    RETURN @p1.STDistance(@p2);


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    2013年3月19日 下午 04:04
    解答者