Calculate POI distance from current location in wp7

17 08 2010

A while ago I went to the wp7 developer hub in Belgium. Not so long after that inspiring event I decided to create an application that shows train departures + delays. A cool feature would be to sort the stations depending on the distance to your current location. You want to find the departures very quickly as you might be using this application when running to your train station after your bus was late ðŸ™‚

This is ultra beta stuff but I will share what I learned so far. Ready to combine math, sqlite and wp7?

Find out where you are, or at least fake it!

With wp7 you can retrieve your current location very easy. You can even receive events when your location changes. I hear you thinking “cool! but I don’t have a real device …” Well I haven’t got one either, would be nice although (hint ^^)

No worries, Microsoft made a nice wp7 emulator and we can use a fake location service to virtually travel the world. Tim Heuer will explain you how to simulate geo location

Calculating distances? Haversine does the trick!

Ok you faked your location, now it is time to find interesting stuff in your neighborhood. Wow that would be sweet but how can you do that?
Well basically you calculate the distance between your current location and all the interesting locations (within a range). You don’t have to invent the math for that, others already did that for us and they came up with the Haversine formula.

Nice we have our current location and a formula to calculate distances. But where do you store all those locations?

Store your stuff the lite way.

I used a sqlite database to store the location of all the stations. What? Is there a database on wp7, can I haz it? Sure, a big thank you to Dan Ciprian Ardelean, he ported the open source sqliteclient by Frank Krueger. Read all about the SqliteClient project and many other cool wp7 libraries.

Haversine is going in your queries!

Now we just have to use the Haversine function in a sql query and we are done! Too bad sqlite does not support the math. No worries we can use a C# function instead! Just need to port this and put it all together.

Still need to do some refactoring but you can already see how it works.
Let me know what you think about my first blog post ðŸ˜‰

```
public void CreateHaversineFunction()
{
if (!_open)
{
throw new SQLiteException("Cannot create commands from unopened database");
}
else
{
Sqlite3.sqlite3_create_function(_db, "distance", 4, Sqlite3.SQLITE_UTF8, null, distanceFunc, null, null);
}
}

private static double ToRadian(double degrees) { return degrees * 0.01745327; } // degrees * pi over 180

static void distanceFunc(Community.CsharpSqlite.Sqlite3.sqlite3_context context, int argc, Community.CsharpSqlite.Sqlite3.Mem[] argv)
{
// check that we have four arguments (lat1, lon1, lat2, lon2)
if (argc != 4) return;

// check that all four arguments are non-null

if (Sqlite3.sqlite3_value_type(argv[0]) == Sqlite3.SQLITE_NULL || Sqlite3.sqlite3_value_type(argv[1]) == Sqlite3.SQLITE_NULL || Sqlite3.sqlite3_value_type(argv[2]) == Sqlite3.SQLITE_NULL || Sqlite3.sqlite3_value_type(argv[3]) == Sqlite3.SQLITE_NULL)
{
Sqlite3.sqlite3_result_null(context);
return;
}
// get the four argument values
double lat1 = Sqlite3.sqlite3_value_double(argv[0]);
double lon1 = Sqlite3.sqlite3_value_double(argv[1]);
double lat2 = Sqlite3.sqlite3_value_double(argv[2]);
double lon2 = Sqlite3.sqlite3_value_double(argv[3]);
// convert lat1 and lat2 into radians now, to avoid doing it twice below
// apply the spherical law of cosines to our latitudes and longitudes, and set the result appropriately
// 6378.1 is the approximate radius of the earth in kilometres
}
```

We have our “distance” sqlite C# function and can use it in an sqlite query:

```        public List GetStations(double lat, double lon)
{

List stationList = new List();
try
{
OpenDb();
db.CreateHaversineFunction();

SQLiteCommand cmd = db.CreateCommand(string.Format(
"SELECT *, distance(Latitude, Longitude, {0}, {1}) as Distance FROM station ORDER BY Distance LIMIT 30" , lat.ToString(), lon.ToString()));
stationList = cmd.ExecuteQuery().ToList();
db.Dispose();
}
catch (SQLiteException ex)
{
MessageBox.Show(ex.Message);
}

return stationList;
}
```

And finally we can use this function to show the closest locations.

```        void watcher_PositionChanged(object sender, GeoPositionChangedEventArgs<GeoCoordinate> e)
{
Dispatcher.BeginInvoke(delegate
{
lstStations.ItemsSource = trainRepository.GetStations(e.Position.Location.Latitude, e.Position.Location.Longitude);
watcher.Stop();
});
}
```

2 responses

18 08 2010

Seems like a useful application for Belgian travelers.
Keep up the good WP 7 dev work!

14 04 2011

I modified my SQLiteClient.cs and added code to SQLiteConnection class.

When I execute the query

db.CreateHaversineFunction();

08 SQLiteCommand cmd = db.CreateCommand(string.Format(

09 “SELECT *, distance(Latitude, Longitude, {0}, {1}) as Distance FROM station ORDER BY Distance LIMIT 30” , lat.ToString(), lon.ToString()));

10 stationList = cmd.ExecuteQuery().ToList();

It throw execption that “no such function: distance”