Home › Web › Online Store Locator using PHP, MySQL and Google Maps API

Online Store Locator using PHP, MySQL and Google Maps API

Posted on Posted in Web Tagged with , , ,

Where is the closest McDonalds? This tutorial shows how to build a map-based locator for searching the closest stores. We will use PHP and MySQL for the backend and JavaScript with Google Maps API for the front end.

Setup the Database

The dataset here contains geographical locations of all McDonalds in North America. It is a CSV (Comma-Separated Values) file with three columns: longitude, latitude and address. There are 15163 locations.

-122.994495, 49.281079, 4805 E Hastings St Burnaby BC V5C 2L1
-123.024074, 49.265642, 3695 Lougheed Hwy Vancouver BC V5M 2A6
......

We create the following table holding the locations and import data from the CSV file. (Note that, we need to specify column names “lng, lat, address” when importing.)

CREATE TABLE `data_mcdonalds` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `lat` DOUBLE NOT NULL,
  `lng` DOUBLE NOT NULL,
  `address` VARCHAR(64) NOT NULL
);

Find the Closest Stores

There are two ways to search for the stores. One is to specify a central location (lat, lng) and find the closest stores based on the distance to the center. Another way is to specify a bounding box (ranges of lat and lng) and find all stores within the bound.

Searching by a bounding box is easy. Suppose we have the bounding box specified by four parameters: lat0, lat1, lng0, lng1. The following query find all stores within a box.

SELECT *
FROM `data_mcdonalds`
WHERE lat >= lat0 AND lat <= lat1
    AND lng >= lng0 AND lng <= lng1;

Searching with a center requires calculating the distance. Suppose we have the center at ($lat0, $lng0), the following query find the closest 20 stores within 10 km. Here 6371 is the earth’s radius in km.

SELECT *,
    ( 6371 * acos( 
    cos(radians($lat0)) * cos(radians(lat)) * 
        cos(radians(lng) - radians($lng0)) + 
    sin(radians($lat0)) * sin(radians(lat)) 
    ) ) AS distance
FROM `data_mcdonalds`
HAVING distance < 10
ORDER BY distance LIMIT 20;

Generate JSON Output

Next we use PHP to dynamically query the database and generate the result. This PHP file handles AJAX requests from the client.

function die_with_error($error) {
    $ret = array(
        "status" => "Failed",
        "error" => $error
    );
    die(json_encode($ret));
}

$lat = $_GET["lat"];
$lng = $_GET["lng"];
if (!$lat || !$lng)
    die_with_error("invalid parameters");

$hostname = '111.111.111.111';
$username = 'xxxxxx';
$password = 'xxxxxx';
$dbname = 'xxx';

mysql_connect($hostname, $username, $password)
    or die_with_error(mysql_error());
mysql_select_db($dbname) or die_with_error(mysql_error());
mysql_set_charset('utf8');

$query = sprintf("SELECT *,
        ( 6371 * acos( 
        cos(radians(%s)) * cos(radians(lat)) * 
        cos(radians(lng) - radians(%s)) + 
        sin(radians(%s)) * sin(radians(lat)) 
        ) ) AS distance
        FROM `data_mcdonalds`
        HAVING distance < 50              
        ORDER BY distance LIMIT 30 ",
    mysql_real_escape_string($lat), 
    mysql_real_escape_string($lng),  
    mysql_real_escape_string($lat)); 

$result = mysql_query($query); 
if (! $result)  
    die_with_error(mysql_error()); 
$result_array = array(); 
while ($row = mysql_fetch_assoc($result)) {  
    array_push($result_array, array(
        "id" => $row['id'],
        "lat" => $row['lat'],
        "lng" => $row['lng'],
        "address" => $row['address'],
        "distance" => $row['distance']
        ));
}

$ret = array(
    "status" => "OK",
    "data" => $result_array);
die(json_encode($ret));

An test of this code follows from this link.

Build a Map

Next we are ready to build a map using Google Maps API. In the HTML code, we define an input box allowing the user typing in addresses, and also a “div” element holding the map.

<input id="address" type="text" value="Vancouver" />
<input id="search" type="submit" value="Search" /></pre>
<div id="map_canvas" style="height: 600px; width: 800px;"></div>
<pre>

The following JavaScript code has several components:

  • Initialize a map
  • Use geocoding to find the lat/lng of an address string
  • Issue an AJAX request with a center location
  • Process AJAX responses and add markers on the map
var map = null;
var geocoder = null;
var markers = [];
var infoWindow = null;

jQuery('#search').click(function() {
    var address = jQuery('#address').val() || 'Vancouver';
    if (map === null)
        initializeMap();
    searchAddress(address);
});

function initializeMap() {
    var mapOptions = {
        zoom: 13,
        mapTypeId: google.maps.MapTypeId.ROADMAP
        }
    map = new google.maps.Map(
        document.getElementById('map_canvas'), mapOptions);
    geocoder = new google.maps.Geocoder();
    infoWindow = new google.maps.InfoWindow();
}

function searchAddress(address) {
    geocoder.geocode( { 'address': address},
        function(results, status) {
            if (status === google.maps.GeocoderStatus.OK) {
                var latlng = results[0].geometry.location;
                map.setCenter(latlng);
                searchStores(latlng.lat(), latlng.lng());
            } else {
                alert('Address not found: ' + status);
            }
    });
}

function searchStores(lat, lng) {
    var url = './store-ajax.php';
    var parameter = { lat: lat, lng: lng };
    jQuery.ajax({
        url: url,
        data: parameter,
        dataType: 'json',
        success: showStores
    });
}

function showStores(data, status, xhr) {
    if (data['status'] != 'OK')
        return;

    infoWindow.close();
    for (var i = 0; i < markers.length; i++) {
        markers[i].setMap(null);
    }
    markers.length = 0;

    for (var i in data['data']) {
        createMarker(data['data'][i]);
    }
}

function createMarker(store) {
    var latlng = new google.maps.LatLng(
                    parseFloat(store['lat']),
                    parseFloat(store['lng'])
                );
    var html = "<b>" + store['address'] + "</b>" +
               store['distance'] + " miles";
    var marker = new google.maps.Marker({
                    map: map,
                    position: latlng
                });
    google.maps.event.addListener(marker, 'click', function() {
            infoWindow.setContent(html);
            infoWindow.open(map, marker);
    });
    markers.push(marker);
}

The final result can be found here:
http://theoryapp.com/my/web/store-locator.html

We also have several extended demos: Searching by Dragging, Clustered Markers, With a Panel List.

Related Posts

Comments

comments