Haversine Formula Using SQL Server to Find Closest Venue - VB.NET

Haversine formula using SQL server to find closest venue - vb.net

I think you'd do best putting it in a UDF and using that in your query:

SELECT v.lat, v.lng, v.name, p.lat, p.lng, p.postcode, udf_Haversine(v.lat, v.lng, p.lat, p.lng) AS distance FROM venuepostcodes v, postcodeLngLat p WHERE p.outcode = 'CB6' ORDER BY distance

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

Linq to sql Haversine formula

why not going 100% SQL as it's the best way to make the calculations, and simply get a table already filled up with the distances?

from an 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 used like:

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 the best is always to have everything on SQL so your hosting server has less to do, simply ad a VIEW to your SQL and call that view, let's 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 call that view directly.

Haversine query into Oracle

I'd recommend you take a slightly different approach.

Check out this site: http://psoug.org/reference/functions.html

Look for the part referring to "calc distance"

deg2rad() expects parameter 1 to be double, object given

When you are using the below query

$lon = DB::table('tbl')
    ->SELECT('lon')
    ->where("id", $users_id)
    ->get();

you get a collection. you have to loop through it to get your desired property. you need to use first() to get just an object.

$lon = DB::table('tbl')
    ->SELECT('lon')
    ->where("id", $users_id)
    ->first();

But even now your problem is not solved. You get an object. You have to use it's property. So make it like

$lonFrom = deg2rad($lon->lon);

lon is the value you get from the database. $lon->lon gives you something like 0.25588484 which is required for deg2rad() method.

---

Related Topics