Latitude and Longitude in SQLite DB

[acidhax]

I have the Lat/Lng of locations in an SQLite table. I also generated the SQLite DataContext class through SQLMetal.

I need SQLite to SORT BY whichever location is closer to a given Lat/Long coordinate. I'm using LINQ, and it doesn't seem to support ExecuteQuery* or whatever the WP7 equivalent could be.

Is it possible to run calculations via LINQ?

I've found this equation that should calculate the distance quickly. I simply have no idea how to use this in my existing DataContext.

((<lat> - LATITUDE_COLUMN) * (<lat> - LATITUDE_COLUMN) +
 (<lng> - LONGITUDE_COLUM) * (<lng> - LONGITUDE_COLUM))

 

[acidhax]

Actually, I think I figured it out...

var result = (from dblot in dc.Parkinglots
                               let distance = ((x - dblot.Lat) * (x - dblot.Lat) + (y - dblot.Lng) * (y - dblot.Lng))
                               orderby distance
                               select dblot);

 

[RogueCode]

Actually, I think I figured it out...

var result = (from dblot in dc.Parkinglots
                               let distance = ((x - dblot.Lat) * (x - dblot.Lat) + (y - dblot.Lng) * (y - dblot.Lng))
                               orderby distance
                               select dblot);

You could also have something like this:

var result = (from dblot in dc.Parkinglots
                       orderby ((x - dblot.Lat) * (x - dblot.Lat) + (y - dblot.Lng) * (y - dblot.Lng))
                       select dblot).Take(30);

Which is essentially exactly the same, but without the ugly 'let'. Also the Take is just tacked on because you should only take what you need.

Finally, although it's not useful in this example, a nice syntax is something like this:

var result = from meep  in myList
                  select new
                  {
                       a = meep.Something - meep.SomethingElse,
                       b = meep.AnotherThing - 20
                  };

And can be very useful.

 

[kinslayer]

And you are right that you can only use linq with sqlce on the phone. No raw SQL support. Linq is pretty awesome though.

Sent from my Nokia Lumia 800 using Board Express