Using the jQuery Store Locator with thousands of locations

November 10th, 2014 2 minute read

A couple of months ago I assisted someone with a paid request to implement my jQuery Store Locator plugin with a database of over 50,000 locations. I hadn’t previously done this and wanted to share the solution I came up with for the benefit of anyone else who might be wondering how to proceed in the same situation. The key, of course, is to integrate with a back-end language but coming up with the appropriate database query that can quickly filter through lots of locations was somewhat tricky. In the newest version of my plugin I’ve added the appropriate parameters to the GET request now that I understand what’s involved: the origin latitude and longitude and I’m also passing the input that the user enters if you’re interested in logging that information for tracking purposes.

The answer that worked for me actually came right out of the O’Reilly book High Performance MySQL. PostgreSQL is actually much better for geospatial calculations but in my case it was an existing MySQL database and it would have been inconvenient to move it to something else. That said, I don’t think the following technique would be and issue with any database management system. Typically, nearest location calculations are done by creating a large sphere around the area (haversine formula) but if you try to attempt that with MySQL it really slows down the system and the performance is not reasonable at all. The trick is to pretend the earth is flat and just use a calculation that creates a large square around the potential area. The jQuery plugin does the haversine calculation, so the locations you feed it from the database don’t need to be extremely accurate.  Here are the steps that you can follow – the code examples are in PHP but it should be easy to translate to other languages if needed.

The first step is to make sure the locations database tables are indexed.

Next, you’ll want to validate and sanitize parameter values that are passed over from the jQuery plugin.

if ( $_GET['origLat'] ) {
    $origLat = filter_input(INPUT_GET, 'origLat', FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
}
if ( $_GET['origLng'] ) {
    $origLng = filter_input(INPUT_GET, 'origLng', FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
}

Next comes the database query that creates the gigantic square around the potential area. The following creates a 100 mile square around the origin location (0.0253 radians).  You’ll want to increase the LIMIT depending on the density of locations. The best way to test this is to test addresses or zip codes with the most locations in an area – usually this will be large cities such as New York or Los Angeles. In testing I upped my limit to as much as 1,000 and didn’t see any significant performance issues but the lower you can make it the better. The following is a MySQLi query (the DB connection information is not included):

$locations = $db->query("
SELECT * FROM locations
WHERE lat BETWEEN  $origLat - DEGREES(0.0253) AND  $origLat + DEGREES(0.0253)
AND lng BETWEEN $origLng - DEGREES(0.0253) AND $origLng + DEGREES(0.0253)
LIMIT 100
");

Then all you need to do is loop through the results of the query and output the JSON data.

$locations_output = array();

foreach( $locations as $location ){
	array_push( $locations_output, $location );
}

echo json_encode( $locations_output );

When you’re adding the store locator plugin settings don’t forget to change the dataType to json instead of the default xml.

Oops! We could not locate your form.

Posted in Web | 51 Comments