Spatial functions in MySQL 5.6, MariaDB 5.5, PostGIS 2.0 and others
Increasingly location based features are becoming an important part of apps and websites. Do you want to show me which of my friends are close, or perhaps what interesting places or events I might find nearby? This is where spatial functions and indexes enter the discussion. MySQL 5.6 finally brings us so called exact GIS functions. This is a big deal because it makes all the difference between the functions being usable versus not.
In this talk we will learn the basics of GIS in databases. We then make a comparison of what's new in MySQL 5.6 and MariaDB 5.5 and compare against other open source databases: PostgreSQL/PostGIS, SQLite/Spatialite, MongoDB, Solr and Sphinx.
As an example project we develop a simple reverse geocoder in Python and test it on top of PostGIS, MongoDB and MySQL. Reverse geocoding is the function where a (latitude, longitude) is given as input, and the reverse geocoder provides an address, or perhaps a Point of Interest as output. (Or often, more than one nearby addresses.)
Since the different database products provide different featuresets, not to mention some being relational and some not, we need to use a couple different strategies to run our reverse geocoder on top of each. We also present benchmark results of each.