One of the main features of the OurPlace app is that created Activities can be designated as being related to a given location. For this to be of any real use, the OurPlace API needs to be able to query these locations and return which ones are closest to a given latitude/longitude point. This is something I hadn’t had to do before.

“Ok”, I thought. “Let’s look up how we go about measuring the distance between two lat/long points. I bet it’s more complicated than you would think, because they’re on the surface of a globe, not on a 2D plane.”

Low and behold, the answer is miserable:

public static double GetDistanceBetween(double lat1, double lon1, double lat2, double lon2, char unit)
{
    double theta = lon1 - lon2;
    double dist = Math.Sin(deg2rad(lat1)) * Math.Sin(deg2rad(lat2)) +
                  Math.Cos(deg2rad(lat1)) * Math.Cos(deg2rad(lat2)) *
                  Math.Cos(deg2rad(theta));
    dist = Math.Acos(dist);
    dist = rad2deg(dist);
    dist = dist * 60 * 1.1515;
    if (unit == 'K')
    {
        dist = dist * 1.609344;
    }
    return (dist);
}
private static double deg2rad(double deg)
{
    return (deg * Math.PI / 180.0);
}

“Jesus, that looks expensive”, I mutter. “I sure as hell don’t want that to run against every row in my dataset. I need to divide and conquer.”

And so I did. Knowing that the Google Places API could return localities which pertain to wider areas, I decided that I would keep a separate database table of those to apply the above operation on. Then, places within that locality could be searched against. This would avoid having to run the operation on every row in the Places table, but introduced a separate (otherwise unused) PlaceLocalities table and utilised some pretty horrible, maths-ridden code which–to be frank–I don’t fully understand.

But… it worked. Was it performant? Probably not. But I could feed my API the user’s location, and it would return any Activities that had been made within 3km–without querying the entirety of the Places table with some disgusting maths operations. And so I left it.

Two years later, through some absent-minded Google searching, I stumbled upon the SQL Spatial Types.

Bugger.

It turns out that there’s a data type made for the exact thing I wanted to do, and it could even be used with Linq. All I needed to do was add a new column of the DbGeography type into my database, and instantiate it by feeding the lat & long into the constructor. You can then use a built-in Distance function to get the distance between it and another point:

DbGeography pointA = DbGeography.FromText(string.Format("POINT({1} {0})", latitude1, longitude1));  
DbGeography pointB = DbGeography.FromText(string.Format("POINT({1} {0})", latitude2, longitude2));

double? distanceInMeters = pointA.Distance(pointB);

Using this, you can search, filter and order results according to their distance from a target:

IEnumerable<Place> places = db.Places.Where(pl => pl.Location.Distance(thisLoc) <= 3000).OrderBy(pl => pl.Location.Distance(thisLoc));

Implementing this in OurPlace (including updating the existing places which had been posted over the last two years to use this new column) took all of 30 minutes. I don’t remember how much time I spent on the other method, but it was a lot longer than that.

The lesson learned? Check that the wheel hasn’t already been invented and included in a standard library before getting your chisel out.