calculate distance with linq or subsonic

Question

i have this MySQL statement from a search page, the user enters there postcode and it finds the nearest stiocklist within 15 MIles of the entered postcode.

 SELECT *  , (
(
ACOS( SIN( "+SENTLNG +" * PI( ) /180 ) * SIN( s_lat * PI( ) /180 ) + COS( " + SENTLNG +" * PI( ) /180 ) * COS( s_lat * PI( ) /180 ) *  COS( (
" + SENTLANG + " - s_lng
) * PI( ) /180 ) ) *180 / PI( )
) *60 * 1.1515
) AS distance_miles
FROM new_stockists
WHERE s_lat IS NOT NULL
HAVING distance_miles <15
ORDER BY distance_miles ASC
LIMIT 0 , 15  

but now i am using linq and subsonic and not got a clue how do do this in linq or subsonic your help would be much appreciated, please also not that i have to sent in a dynamic from address, thats the postcode mentioned at the top of the page, i do a call to google to get then lng and lat from them for the postcode given.

Answer 1

Create a new view in SQL Server using this part of the SQL:

SELECT * , (your equation here) as distance
FROM new_stocklists
WHERE s_lat is not NULL

Then you can create a Linq object for your view (SQLMetal will do this or Linq to SQL in Visual Studio). Then you can use Linq to query this view. Let's say your object is StockDistance:

var list = db.StockDistance.Where(x=>x.distance<15)
    .OrderBy(x=>x.distance)
    .Take(15);

Answer 2

You can create a stored procedure in MS SQL that does what your query does, and then call that query from your app. Linq does support stored procedures - kindof like this

partial class StockistsDataContext
{
    [Function(Name = "dbo.NewStockistsByDistance")]
    public ISingleResult<NewStockist> NewStockistsByDistance(
        [Parameter(DbType = "Int", Name = "s_lat")] int lat,
        [Parameter(DbType = "Int", Name = "s_lng")] int lng)
    {
        var result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), lat, lng);
        return ((ISingleResult<NewStockist>)(result.ReturnValue));
    }
}

It's a little irritating to fall back to stored procs, but I don't think the part of Linq that parses the Queryables expressiontrees can detect the revant math functions and map them to SQL.

Answer 3

I would suggest (if possible) getting the centre point of the postcode and then applying the Haversine formula to find all stores within a certain radius.

The formula here is in kilometres.
You will have to change the relevant numbers and it will work for miles.
Eg: Convert 6371.392896 to miles.

DECLARE @radiusInKm AS FLOAT
DECLARE @lat2Compare AS FLOAT
DECLARE @long2Compare AS FLOAT
SET @radiusInKm = 5.000
SET @lat2Compare = insert_your_lat_to_compare_here
SET @long2Compare = insert_you_long_to_compare_here

SELECT * FROM insert_your_table_here WITH(NOLOCK) WHERE (6371.392896*2*ATN2(SQRT((sin((radians(GeoLatitude - @lat2Compare)) / 2) * sin((radians(GeoLatitude - @lat2Compare)) / 2)) + (cos(radians(GeoLatitude)) * cos(radians(@lat2Compare)) * sin(radians(GeoLongitude - @long2Compare)/2) * sin(radians(GeoLongitude - @long2Compare)/2))) , SQRT(1-((sin((radians(GeoLatitude - @lat2Compare)) / 2) * sin((radians(GeoLatitude - @lat2Compare)) / 2)) + (cos(radians(GeoLatitude)) * cos(radians(@lat2Compare)) * sin(radians(GeoLongitude - @long2Compare)/2) * sin(radians(GeoLongitude - @long2Compare)/2))) ))) <= @radiusInKm

if you would like to perform the Haversine formula in C#,

double resultDistance = 0.0;
double avgRadiusOfEarth = 6371.392896; //Radius of the earth differ, I'm taking the average.

//Haversine formula
//distance = R * 2 * aTan2 ( square root of A, square root of 1 - A )
// where A = sinus squared (difference in latitude / 2) + (cosine of latitude 1 * cosine of latitude 2 * sinus squared (difference in longitude / 2))
// and R = the circumference of the earth

double differenceInLat = DegreeToRadian(currentLatitude - latitudeToCompare);
double differenceInLong = DegreeToRadian(currentLongitude - longtitudeToCompare);
double aInnerFormula = Math.Cos(DegreeToRadian(currentLatitude)) * Math.Cos(DegreeToRadian(latitudeToCompare)) * Math.Sin(differenceInLong / 2) * Math.Sin(differenceInLong / 2);
double aFormula = (Math.Sin((differenceInLat) / 2) * Math.Sin((differenceInLat) / 2)) + (aInnerFormula);
resultDistance = avgRadiusOfEarth * 2 * Math.Atan2(Math.Sqrt(aFormula), Math.Sqrt(1 - aFormula));

DegreesToRadian is a function I custom created.
It is a simple 1 liner of "Math.PI * angle / 180.0"

For LINQ, you can use the C# math functions as well as all C# checks. Eg: != equals not equal, etc.
See the following as an example.
It is not complete, so kindly tweak it to your liking.

var linqQuery = from linqCollection in insert_your_collection_here
where s_lat != nothing
select Math.ACos(Math.Sin(DegreesToRadian(sentlng) * Math.Pi / 180))

Look up the MSDN link below for all simple LINQ example. Have a play around with it, hope this helps

My blog entry - SQL Haversine

MSDN - 101 LINQ Samples