In my previous post about geo-spatial search in MySQL I described (along with other things) how to use geo-distance functions. In this post I will describe the geo-spatial distance functions in more details.

If you need to calculate an exact distance between 2 points on Earth in MySQL (very common for geo-enabled applications) you have at least 3 choices.

• Use stored function and implement haversine formula
• Use UDF (user defined function) for haversine (see below)
• In MySQL 5.6 you can use st_distance …

Geo-enabled (or location enabled) applications are very common nowadays and many of them use MySQL. The common tasks for such applications are:

• Find all points of interests (i.e. coffee shops) around (i.e. a 10 mile radius) the given location (latitude and longitude). For example we want to show this to a user of the mobile application when we know his/her approximate location. (This usually means we need to calculate a distance between 2 points on Earth).
• Find a ZIP code (U.S. Postal address) for the given location or determine if this location is within the given area. Another example is to find a school district for the given property.

MySQL had the spatial functions originally (implementation follows a subset of OpenGIS standard). However, there are 2 major limitation of MySQL spatial functions …