A couple months ago I had to calculate distance between locations using latitude and longitude... at that time I found a T-SQL sample that did the job but I forgot the site... I was impressed and I translated the code to C# and put in .NET Code library class and then, to make thigs complicated ( and usable in MS SQL 2005 Analisys Services- SSAS ) I've put in SSAS and call it from MDX query..
So far so good... but now I had some doubts about data accuracy and I searched the web again :). I havent; found same piece of code but I found better - and even article that describes the whole thing :)
In order not to forget again ( and to benefit you ) and I osting the code here. I recommend reading the article - Using SQL Server Yukon's .NET CLR Features Practically by Kent Tegels MCDBA, MCSE+I, MCP+SB
T-SQL user function:
CREATE FUNCTION dbo.udfComputeDistance
(
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float
)
RETURNS float
AS
begin
-- dLong represents the differences in longitudes
-- while dLat is the difference in latitudes
declare @dLong float
declare @dLat float
-- To keep the calculation easier to understand,
-- we have simplified it by computing it by parts.
-- This value temporarily holds the value of the
-- first calculation.
declare @temp float
-- Convert the decimal degrees to radians
set @lat2 = radians(@lat2)
set @lon1 = radians(@lon1)
set @lat1 = radians(@lat1)
set @lon2 = radians(@lon2)
-- Compute the degree differences
set @dLong = @lon2 - @lon1
set @dLat = @lat1 - @lat2
-- Compute the first part of the equation
set @temp = (square(sin(@dLat/2.0))) + cos(@lat2) * cos(@lat1) * (square(sin(@dLong/2.0)))
-- Return the approximate distance in miles
-- Note that 3956 is the approximate median radius of the Earth.
return (2.0 * atn2(sqrt(@temp), sqrt(1.0-@temp)))*3956.0
end
And C# function
private const double PI_OVER_180 = 0.0174532925;
private static double radians(double DecimalDegrees)
{
return DecimalDegrees * PI_OVER_180;
}
public static SqlDouble ComputeDistance(SqlDouble FromLat,
SqlDouble FromLong, SqlDouble ToLat, SqlDouble ToLong)
{
double lat1, lat2, lon1, lon2,
dLong = 0.0, dLat = 0.0, subCalc = 0.0;
lat1 = radians((double)(FromLat));
lon1 = radians((double)(FromLong));
lat2 = radians((double)(ToLat));
lon2 = radians((double)(ToLong));
dLong = (double)(lon2 - lon1);
dLat = (double)(lat2 - lat1);
subCalc = (Math.Pow(Math.Sin(dLat / 2.0), 2.0))
+ Math.Cos(lat2) * Math.Cos(lat1)
* (Math.Pow(Math.Sin(dLong / 2.0), 2));
return ((2.0 * Math.Atan2(Math.Sqrt(subCalc),
Math.Sqrt(1.0 - subCalc))) * 3956.0);
}
[STAThread]
public static void Main(string[] args)
{
Console.WriteLine(
ComputeDistance(40.7539,-96.6428, 41.28692,-96.07023));
Console.ReadLine();
}
ADO.NET blog announced that two projects has been set up inside Microsoft recently - both revealed at MIX 07.
First one has codename "Astoria" and it's goal is
to enable applications to expose data as a data service that can be consumed by web clients within a corporate network and across the internet. The data service is reachable over HTTP, and URIs are used to identify the various pieces of information available through the service. Interactions with the data service happens in terms of HTTP verbs such as GET, POST, PUT and DELETE, and the data exchanged in those interactions is represented in simple formats such as XML and JSON.
The first early release of Astoria will be a Community Tech Preview that you can download, as well as an experimental online service you can access over the internet.
Check out the Astoria webpage at http://astoria.mslivelabs.com for more information and a link to the download.
Second one is called "Jasper" and aim at faciliating data-driven development. Developing data-driven applications could be tedios taks as developers have to spend a lot of time developing supporting infrastructure and Data Access Layer insead of focusing on real business problem. There are many O/R Mapping tools that reduce ammount of work by offering DB-classes mapping along with code generation. I personally though MS try to catch up with LINQ to SQL ( a.k.a DLINQ) and Entity Model in Visual Studio Orcas... but it sounds like they aim higher and use Entity Model for:
- Dynamic generation of data classes so there is no configuration or design time code-gen to carry around.
- Rich query and O/R capabilities because “Jasper” is built on top of the Entity Framework.
- Auto-binding capabilities for ASP.NET, WinForms, and WPF to make binding data to a UI simple and automatic.
Learn more about “Jasper” on the MSDN Data Access Incubation Projects site