Linq to sql Haversin Formula

Question

I have an implementation of the Harversine formula in both C # and the example in TSQL. I'm not sure how best to implement the server side formula, so I can use it in a Linq query.

Ideally, I would just bind my local formula to a function on the server. Thus, avoiding the error "without translation into sql" and having everything pleasant and smooth.

Obviously, any idea of ​​the problem is helpful.

I know the types of geography in SQL2008. However, the code base I'm working with already has this dependency on Linq to SQL. I would expect it to be more effort than it costs!

thanks

Answer 1

why not switch to 100% SQL, as this is the best way to do the calculations and just get a table filled with distances?

from existing answer

CREATE FUNCTION dbo.udf_Haversine(@lat1 float, @long1 float, @lat2 float, @long2 float) RETURNS float BEGIN DECLARE @dlon float, @dlat float, @rlat1 float, @rlat2 float, @rlong1 float, @rlong2 float, @a float, @c float, @R float, @d float, @DtoR float SELECT @DtoR = 0.017453293 SELECT @R = 3937 --3976 SELECT @rlat1 = @lat1 * @DtoR, @rlong1 = @long1 * @DtoR, @rlat2 = @lat2 * @DtoR, @rlong2 = @long2 * @DtoR SELECT @dlon = @rlong1 - @rlong2, @dlat = @rlat1 - @rlat2 SELECT @a = power(sin(@dlat/2), 2) + cos(@rlat1) * cos(@rlat2) * power(sin(@dlon/2), 2) SELECT @c = 2 * atn2(sqrt(@a), sqrt( 1-@a )) SELECT @d = @R * @c RETURN @d END 

and is used as:

 var table = from r in db.VenuePostCodes select new { lat = r.Latitude, lng = r.Longitude, name = r.Name, distance = db.udf_Haversine( r.Latitude,r.Longitude, r.Latitude,r.Longitude2) }; 

but it’s best to always have everything in SQL, so your hosting server is smaller, just add VIEW to your SQL and call this view, imagine:

 SELECT latitude, longitude, name, latitude1, longitude2, postcode, udf_Haversine(latitude, longitude, latitude2, longitude2) AS distance FROM venuepostcodes ORDER BY distance 

and use LINQ to directly call this view.

Answer 2

@balexandre's answer is wonderful, but I was not happy with the provided SQL function (it lacks comments, the constant is ridiculously commented on, is it miles? KM? etc.)

 CREATE FUNCTION [dbo].[udf_Haversine](@lat1 float, @long1 float, @lat2 float, @long2 float) RETURNS float BEGIN DECLARE @dlon float, @dlat float, @rlat1 float, @rlat2 float, @rlong1 float, @rlong2 float, @a float, @c float, @R float, @d float, @DtoR float SELECT @DtoR = PI() / 180, -- Degrees to radians const @R = 6371 -- Radius of Earth in KM SELECT @rlat1 = @lat1 * @DtoR, @rlong1 = @long1 * @DtoR, @rlat2 = @lat2 * @DtoR, @rlong2 = @long2 * @DtoR SELECT @dlat = @rlat1 - @rlat2, @dlon = @rlong1 - @rlong2 SELECT @a = SIN(@dlat / 2) * SIN(@dlat / 2) + SIN(@dlon / 2) * SIN(@dlon / 2) * COS(@rlat2) * COS(@rlat1) SELECT @c = 2 * atn2(sqrt(@a), sqrt(1 - @a)) SELECT @d = @R * @c -- Final distance in KM SELECT @d = @d * 0.621371192 -- Final distance in miles RETURN @d END 

It is converted from the JavaScript implementation that we grabbed from here, with the addition of conversions in miles at the end:

 // Converted from JavaScript implementation: // http://www.movable-type.co.uk/scripts/latlong.html var R = 6371; // km var dLat = (lat2-lat1).toRad(); var dLon = (lon2-lon1).toRad(); var lat1 = lat1.toRad(); var lat2 = lat2.toRad(); var a = Math.sin(dLat/2) * Math.sin(dLat/2) + Math.sin(dLon/2) * Math.sin(dLon/2) * Math.cos(lat1) * Math.cos(lat2); var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); var d = R * c;