When geography becomes a key element of a website, it can become necessary to provide a location based search. In this article, I take a look at how to perform a search within a given distance from a location.

Location, Location, Location

In the earlier days of the web, I found myself working on two websites with a common requirement. They both dealt with real world locations. And they both needed to let visitors search for places close to a point on a map.

At first it seemed like a straight forward matter. However, the curvature of the Earth throws a spanner in the works. Both websites wanted to give accurate results based on distance from a given location.

Preparing the Data

In both cases, the websites used a MySQL database to store information about the locations. The databases needed to be prepared for location based search. I added a longitude and latitude field to both the databases. It was a painstaking process looking up the exact coordinates for each existing entry and entering them in to the new fields.

Performing the Location Based Search

Having set up the database, I needed a query that would return all the places within a given distance of a location. Here comes the technical bit, example in PHP. The following, sanitised, variables are required by the query:

• $latitude: the latitude of the location the user wishes to search from.
• $longitude: the longitude of the location the user wishes to search from.
• $max_distance: How far from the location does the user wish to search, this figure is in miles. If you need to work in km, you will need to adjust the formula (or convert the km to miles).
• $page_size: How many matches should be returned.
• $start_pos: Which entry should be the first one returned. Combining this with $page_size, allows for paging.

The section of code below creates an SQL snippet that will be used multiple times in the final search query. This is the core of the search. It calculates the distance in miles between two points on the map:

$calc_distance = "sqrt(power(69.1 * (entities.latitude - $latitude), 2) + 
                       power(69.1 * (entities.longitude - $longitude) *
                             cos(entities.latitude / 57.3), 2))";

Having set up $calc_distance, the next step is to build the search query:

$sql = "Select *, ($calc_distance) As distance_ 
          From entities 
          Where (($calc_distance) <= $max_distance) 
          Order By ($calc_distance) Asc, entities.name Asc 
          Limit $start_pos, $page_size";

I’ve taken liberties with the formatting above, to make it easier to read. The select returns all fields in the “entities” table plus the distance in miles as a “distance_” field. The “where” clause restricts all matches to those in the required distance, it’s possible to add other restrictions here. The “order by” first orders by distance and then by “name”. Finally the “limit” controls paging. When executing this query, I get a paged list of entities, with the nearest ones first.

Next time, I will cover adding a clickable map to location based search.

SAMWare UK creates bespoke solutions and websites. Contact us to arrange a chat to discuss your needs and for a free no obligation quote.