2

I am trying to find all db entries that have a lat lon that fall into a certain mile range from my location.

I am basing my answer off of this stackoverflow answer:

MySQL Great Circle Distance (Haversine formula)

Here is what I have:

                IQueryable<myTable> busLst = (from b in db.myTable
                                             where (3959 * Math.Acos(Math.Cos(radians(latLonRequest.lat)) * Math.Cos(radians(b.lat))
                                            * Math.Cos(radians(b.lon) - radians(latLonRequest.lon)) + Math.Sin(radians(latLonRequest.lat)) *
                                            Math.Sin(radians(b.lat)))) < latLonRequest.MaxDistance
                                             select b
                                            );

I am getting the following error:

"errorCode": "NotSupportedException",
    "message": "LINQ to Entities does not recognize the method 'Double Acos(Double)' method, and this method cannot be translated into a store expression.",
    "stackTrace": "[GetByLatLonRequest: 6/24/2013 6:57:14 PM]:\n[REQUEST: {lat:3,lon:3,maxDistance:10,measureSystem:N}]\nSystem.NotSupportedException: LINQ to Entities does not recognize the method 'Double Acos(Double)' method, and this method cannot be translated into a store expression.\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent,
Community
  • 1
  • 1
Aziz
  • 1,584
  • 4
  • 23
  • 43

2 Answers2

1

This means that linq doesn't know how to translate your function into SQL. What you have to do is pull out your data first and then run your logic on that: 

IQueryable<myTable> busLst = (from b in db.myTable.AsEnumerable()
                                             where (3959 * Math.Acos(Math.Cos(radians(latLonRequest.lat)) * Math.Cos(radians(b.lat))
                                            * Math.Cos(radians(b.lon) - radians(latLonRequest.lon)) + Math.Sin(radians(latLonRequest.lat)) *
                                            Math.Sin(radians(b.lat)))) < latLonRequest.MaxDistance
                                             select b
                                            );

This will enumerate the results before running your math functions.

Your better bet is building predicates: http://www.albahari.com/nutshell/predicatebuilder.aspx

bobek
  • 8,003
  • 8
  • 39
  • 75
  • Can you tell me the advantage of using predicates vs my current method? – Aziz Jun 29 '13 at 15:38
  • 1
    It lets you build multiple predicates (for example Where() clauses) and separate them into functions. It keeps your code cleaner and you can also do operations like OR on them. I don't think it's necessary in your example but it's something good to know that it exists. – bobek Jun 29 '13 at 15:48
1

Edit: Somehow thought OP was talking TSQL, but there was no such talk.

The function appears to be is definable in MySQL, why not create a SQL function or stored proc and go from there? BTW, depending on the number of locations, you might want to investigate geography/spatial data types as there are better indexes for them

http://dev.mysql.com/doc/refman/5.0/en/mysql-spatial-datatypes.html

JayC
  • 7,053
  • 2
  • 25
  • 41