I recently launched a startup that required geofencing. For those not familiar with the term, geofencing is the finding of known locations within a specific area around a central point. An example of what geofencing looks like can be seen to right:
Here is what we needed to deliver for our project:
- Track a list of known points
- Track the location of a specific item at a point in time
- Determine a list of those known points that were within a specific radius of that item during that time
The MySQL Spatial Solution
After performing some extensive research on the current methods for geofencing, I opted to utilize MySQL’s spatial extensions to help store and calculate possible known points within the perimeter of a specific location. This allowed us to store each point as a MySQL geometry, then perform a special calculation to detect known points within the radius.MySQL even offers indexing of geometric columns if you opt for the MyISAM engine for the table.
Geofencing with ActiveRecord
Since ActiveRecord doesn’t have direct support for the MySQL spatial extensions, the handy geokit-rails gem by Andre Lewis was used. With this gem, we were able to create the proper migrations to save and restore the geometry locations for our models. After writing a helper method to make the query as fast as possible, our work on the Rails side was complete. We had a little MySQL-specific code, but it was isolated within the migration (via the gem extensions) and the model itself.
I also performed some optimizations to our MySQL configuration and to the table itself. After that, I roughly measured the performance for our queries to <= 0.05 seconds against 4+ million known points stored in MySQL. More than enough for our performance needs!
Don’t Forget the Database!
Many developers forget about the power of using a database’s native extensions. Many architects and managers don’t want to bind to vendor extensions, preventing them from saving time and increasing performance.
MySQL’s spatial extensions, while following some industry standards regarding spatial data storage and support, wasn’t exactly a SQL standard. For this project, we made the right decision and saved considerable time of building the solution ourselves.
We were also reminded that sometimes spending time reviewing and selecting your architecture solutions during a discovery phase can pay off considerably.