Search using Geolocation data in MySQL

One of the many things brought to the forefront by HTML5 is Geolocation. It’s easy enough to get someones geolocation data using HTML5 but what do you do with it once you have it? Ideally you’d like to search through some sort of database be it store locations, real estate listings or what have your. This is where most people start to get tripped up.

There’s lots of different techniques for achieving this unfortunately they don’t seem to ever be explained very well as they relate to MySQL. Until now… dun dun dun dahhh.

The two most common technique are the Haversine formula and the Spherical Law of Cosines. The method I’ve chosen to use in my example is the Spherical Law of Cosines. The formula is a bit short and uses fewer mathematical functions.

The example is comprised of two parts. The first is the Spherical Law of Cosines. The second part is defining a search radius. Defining this search radius will significantly narrow the scope of our search. This is a good thing.

Alright enough chitchat. Here’s the sample MySQL query. It’s built in PHP below but can easily be converted to your language of preference.

Note: I’m Canadian so the example below is done using kilometers. If you want miles you’ll need to multiple $earths_radius and $surface_distance_coeffient by 0.6214 to convert them to miles.

If your interested in digging deeper into the theory and math behind the Spherical Law of Cosines or the Haversine formula checkout the Movable Type Scrips site they have tons of background information.

6 responses to “Search using Geolocation data in MySQL”

  1. Andrew says:

    Thanks Tim for this script – I’ve been working on a radius search solution for a project and your script is by far the simplest I’ve found and with a few modifications is now incorporated and working nicely.  Thanks

  2. Cip says:

    Excellent post. Thank you

  3. Thanks for this it will give me a good place to start on a project I have to do.

  4. […] Go here: Search using Geolocation data in MySQL […]

  5. […] The origin sql-query is from here: http://funkjedi.com/technology/308-search-using-geolocation-data-in-mysql/ […]

  6. deralexander says:

    THX a Lot. great.
    just one question is the radius km or Miles?

Leave a Reply