MDX geographic distance calculation

Question

I'm using SQL Server 2005 Analysis Services and I'm trying to calculate distance inside of an MDX query - so that I can get counts of the items that are near my current location. I've created a dimension with Latitude & Longitude, and have also created a .NET assembly to do the math - but am having a hard time getting it all to work out in the query.

My query to find items in a 100 mile radius looks something like this:

select   FILTER([DimProducts].[Product].[Product],
         ZipCalculatorLib.GetDistance(43.474208, [Zip].[Latitude], 96.687689, [Zip].[Longitude]) < 100)  on rows,
     [Measures].[RowCount] on columns
from     MyCube;

And my distance code in .NET looks like this:

public static double GetDistance(double startLat, double endLat,
    double startLong, double endLong)
{
    return Math.Sqrt(Math.Pow(69.1 * (startLat - endLat), 2) + Math.Pow(Math.Cos(endLat / 57.3) * 69.1 * (startLong - endLong), 2));
}

However, when I run that query, I come up with zero records. If I change the distance from 100 to 10000 - I get counts similar to what should be in the 100 mile radius. It looks like the .NET class isn't doing the square root - but I've tested that code many times over, and it looks right.

Does anyone have any suggestions as to where I should look to fix my problem?

EDIT: I started to wonder if maybe the latitude and longitude weren't being passed into my GetDistance function correctly - so I added a line of code there to throw an exception to show me what they were. I added the following:

throw new ArgumentException("endLat", string.Format("endLat: {0}, endLong: {1}", endLat, endLong));

And now when I run my query, I get the following error:

Execution of the managed stored procedure GetDistance failed with the following error: Exception has been thrown by the target of an invocation.endLat Parameter name: endLat: 1033, endLong: 1033.

So now the question becomes: how do I get my actual latitudes values to pass through that function in the filter? It looks like just a language code is being passed in now.

Answer 1

Hello Scott.

How are you sure that you are calculating this in miles?

I'm not sure if SQL Server 2008 is availuable, if it is, you should use its geography datatype to calculate distances.

If not, check libraries like SharpMap and Proj.Net - They will let you build a true geographic point and calculate accurate distances between those objects.

Answer 2

[Zip].[Latitude] is a member expression. In order to pass this to a function as a numeric value SSAS will return the equivalent of ([Zip].[Latitude], Measures.CurrentMember). And you can't directly filter one dimension based on another in MDX. By definition, different dimensions are completely independent and in OLAP terms every product could potentially exist at every Zip location.

What I suspect that the logic would have to look like is the following:

select   
NONEMPTY([DimProducts].[Product].[Product] *
FILTER([Zip].[Zip].[Zip] , ZipCalculatorLib.GetDistance(43.474208, [Zip].[Latitude].CurrentMember.MemberValue, 96.687689, [Zip].[Longitude].CurrentMember.MemberValue) < 100),[Measures].[RowCount])  on rows,        [Measures].[RowCount] on columns
from     MyCube;

This gets all of the Zip members that have a latitude/longitude within 100 miles, cross joins that with products and then returns those that have a nonempty RowCount.

Answer 3

I ended up solving my problem using a subcube. by creating the subcube, I was able to filter for the distance - and then after that just did a select for the dimension that I was looking for. Here's what I ended up with...

create subcube MyCube as
select  Filter
        (
            (
                [DimLocation].[Latitude].[Latitude], 
                [DimLocation].[Longitude].[Longitude] 
            ), 
            (
                ZipCalculatorLib.GetDistance(
                    43.474208, 
                    [DimLocation].[Latitude].CurrentMember.MemberValue, 
                    96.687689, 
                    DimLocation.Longitude.CurrentMember.MemberValue) < 100
            )
        ) on 0 from MyCube;

select  DimProducts.Product.Product on rows,
        Measures.RowCount on columns
from       MyCube;