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:
“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
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.
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:
Using this, you can search, filter and order results according to their distance from a target:
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.