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

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;