Find closest location using longitude and latitude by linq from database

Question

I need to get nearby location using (longitude, latitude )

I have a table where the locations are saved in database with longitude and latitude fields,

I want to retrieve the nearest locations, and I have Sql code and want to convert it to linq, where I'm using ASP.Net MVC5 Here is my code:

SELECT Name, ( 3959 * acos( cos( radians(24.743055) ) * cos( radians( 
latitude ) ) * 
cos( radians( longitude ) - radians(46.669702) ) + sin( radians(24.743055)  
)* 
sin( radians( latitude ) ) ) ) AS distance 
FROM Event
ORDER BY distance

I wrote it in linq like this:

double startlat = Convert.ToDouble(db.Users.Where(u => u.Id == 2).Select(u 
=> u.latitude).Single());


double startlng = Convert.ToDouble(db.Users.Where(u => u.Id == 2).Select(u 
=> u.longitude).Single());


var c = (from ev in db.Event
where (3959 * Math.Acos(Math.Cos((Math.PI * startlat / 180.0)) * 
Math.Cos(Math.PI * (Convert.ToDouble(ev.latitude)) / 180.0) *
Math.Cos((Math.PI * (Convert.ToDouble(ev.longitude)) / 180.0) - (Math.PI * 
(startlng) / 180.0)) + Math.Sin(Math.PI * (startlat) / 180.0)) *
Math.Sin(Math.PI * (Convert.ToDouble(ev.latitude) ) / 180.0) ) < 2500
select ev.Name).ToList();

but I get this error:

LINQ to Entities does not recognize the method 'Double Acos(Double)' 
method, and this method cannot be translated into a store expression.

I tried to use "public static double ToRadians" and pass the value, but it didn't work because it's a static

Any Ideas ?

Obviously you've read https://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-met (and likely similar posts on the same error message).. Can you please clarify why that route does not work for you? — Alexei Levenkov, Oct 31 '19 at 00:08

score 1 · Answer 1 · answered Oct 31 '19 at 05:45

Firstly, you should get your user's location data in one hit:

var startCoords = db.Users.Where(u => u.Id == 2).Select(u 
=> new { u.latitude, u.longitude }).Single();

To get the closest location, could this be simplified to sum the X delta and Y delta and take the smallest total delta?

var closestEvent = db.Event.Select(x => new { x.EventId, delta = Math.Abs(x.latitude - startCoords.latitude) + Math.Abs(x.longitude - startCoords.longitude)})
.OrderBy(x => x.delta)
.FirstOrDefault();

This might be an over-simplification, but given X,Y coordinates, comparing the sum of an absolute difference would give you a rough ranking as the crow flies. If a more detailed formula is needed, or you want to feed those into something like Google Earth for a road check or other pathfinding, the rough check could return the top 10 or so events and coordinates to memory, where a more detailed check can be run against these without worrying about transformation to SQL issues.

Find closest location using longitude and latitude by linq from database

1 Answers1