Using LINQ find nearby places from database

We want to receive list of nearby places from database using LINQ in ASP.NET 2012 and would like some feedback on our strategy.


My table and fake data:

     PlaceId    Name       Latitude   Longitude 
       1          A          18.1        20.1
       2          B          18.2        20.2
       3          C          18.3        20.3

1) In our project the client current location (latitude and longitude) is taken as input

2) At server side ,depending upon the client current location, we need to find nearby places from the database using LINQ

Here's the code for SQL which I earlier used , but now we want to use LINQ.

SELECT  name, Latitude, Longitude , 
  ( 3959 * acos( cos( radians(?) )* cos( radians( Latitude) ) * cos( radians( Longitude ) - radians(?) ) 
 + sin( radians(?) ) * sin( radians( Latitude) ) ) ) AS distance 
FROM TABLE_NAME 
HAVING distance < ? 
ORDER BY distance LIMIT 0 , 20

[But the question is how to write such an query in LINQ.]

My work on this:

While searching for the solution, I came across this code

        var Value1 = 57.2957795130823D;
        var Value2 = 3958.75586574D;

        var searchWithin = 20;

    double latitude = ConversionHelper.SafeConvertToDoubleCultureInd(Latitude, 0),
          longitude = ConversionHelper.SafeConvertToDoubleCultureInd(Longitude, 0);

    var location = (from l in sdbml.Places
                    let temp = Math.Sin(Convert.ToDouble(l.Latitude) / Value1) *  Math.Sin(Convert.ToDouble(latitude) / Value1) +
                             Math.Cos(Convert.ToDouble(l.Latitude) / Value1) *
                             Math.Cos(Convert.ToDouble(latitude) / Value1) *
                             Math.Cos((Convert.ToDouble(longitude) / Value1) - (Convert.ToDouble(l.Longitude) / Value1))
                         let calMiles = (Value2 * Math.Acos(temp > 1 ? 1 : (temp < -1 ? -1 : temp)))
                         where (l.Latitude > 0 && l.Longitude > 0)
                         orderby calMiles
                        select new location
                             {
                                    Name = l.name
                                });
                        return location .ToList();

But the problem is ,how to reference ConversionHelper or under which namespace it comes.

All advice is appreciated.

Answers


so, if all that you want is to calculate distance between two co-ordinates, why don't you use Dot Net's GeoCoordinate?

it goes something like

 var firstCordinate = new GeoCoordinate(latitude1, longitude1);
 var secondCordinate = new GeoCoordinate(latitude2, longitude2);

 double distance = firstCordinate.GetDistanceTo(secondCordinate);

you can find it inside namespace System.Device.Location.

So this will save you from all those Math.Cos and Math.Sin and your linq will be plain and simple. (probably a foreach loop will do)

so your entire query can be summarized as:

List<Location> locations = new List<Location>();
foreach(var place in sdbml.Places)
{
   //your logic to compare various place's co-ordinates with that of
   //user's current co-ordinate
}

Need Your Help

What do 'statically linked' and 'dynamically linked' mean?

c# c++ linker

I often hear the terms 'statically linked' and 'dynamically linked', often in reference to code written in C, C++ or C#, but I don't know much of anything about either. What are they, what exactly ...

Adjusting the distance of a swipe event in jquery mobile

jquery jquery-mobile swipe

Is it possible to adjust the distance that is necessary to trigger the swipe event, and if so how is it done?.

VB.NET - Access menu event from another form, works only on form_load

.net vb.net events

I have two forms in my application, one form is to create new connections, the other is the main form which holds the menu that will carry the connection names.