18 Oct 2009 - Warsaw
They know where you are
Location data and maps are currently all over the internet (I don’t dare say “everywhere”). From the obvious Google Maps and it’s uses (“club I recommend to get hammered in”) to classified ad and auction sites (where you don’t necessarily see the map per se, but can use location-based search). And there’s going to be more of it, considering the growth of smartphones with built-in GPS and the following location-using apps.
It means, simply, that if you haven’t encountered the need to have location-based search in your webapp, chances are you’re going to, very soon.
I want to become “them”!
The whole location-based search stuff is non-trivial due to one reason: Earth is not flat, it’s a sphere (a spheroid to be precise), so we don’t have simple (x,y) coordinates that we could use to calculate distance using trivial pitagorean formula. Instead, location on Earth is determined using a pair of two numbers: latitude and longitude, which are basically a pair of angles.
It means that in order to calculate distance between two points, given their latitude, longitude pairs, we need to use quite fancy formula in order to at least pretend to be accurate.
But we want to do a search based on the results of this formula. And this one’s pretty complicated. And it’s definitely not something you’d like to repeat in every single SQL statement when searching for stuff in a-few-kilometer proximity of given (center) point.
Stored procedures are the shit
Basically if you don’t want to repeat something in every SQL statement, you put it in DB’s stored procedures to be called at any time desired using some simple syntax. This of course is a bit against the idea of “dumb” databases that DHH promotes, but we have to make a choice. I choose to look at the horrendous “great circle distance formula” only once, to write a function that’s going to calculate distances using it between any two given points.
We’re going to use stored procedures also because of the fact that every complete SQL RDBMS supports them — yes, even MySQL, got a procedures language called MySPL.
Teasers are wrong, I know
This post is actually a meta-post, because after this lenghty and scary introduction I’m going to show how to implement distance-based search in Rails (well, it’s actually going to be pretty useful for developers using any language and framework) using these database engines in posts about to follow (in the upcoming week):
- PostgreSQL
- MySQL
- MongoDB
The list entries above will consequently change into links leading to article about implementing distance-based search using the given DB. Stay tuned!
And yes, you read it properly: while first two posts about to follow are going to be about well-known, popular and estabilished players in world of database servers for web application, the third one is going to be about implementing this non-trivial functionality using new kid on the block, a document-oriented database MongoDB gaining popularity amongst Ruby/Rails devs. I already fell in love with it after reading a lot and using a bit. And that’s not only me — the fact that on the upcoming RuPy conference in Poland there’re going to be two talks about MongoDB (with one from Obie Fernandez himself) speaks for itself.