Distance Learning Map using Google Maps

When one of the distance learning-educators from the Milwaukee Public Museum approached me with the request of creating a map containing all the cities the museum had done a program for, I was excited that I finally had a reason to use Google Maps outside of Drupal. I quickly found that there was a limit on the number of Geocoder requests. Geocoding is converting an address like 123 Fake st. Milwaukee, WI into map coordinates. Google only allows 10 geocoder lookups per second. Since I had about 500 addresses to look up, adding a wait period between every 10 requests was not acceptable.

The next step was to look up each city and state and getting the coordinates, but doing that manual was not efficient. I needed a way to get the coordinates for each distinct city, and luckily for me, google provided an avenue to acheive this task.

The first thing I needed to do was modify the user's database table and add two columns, one for lattitude and the other for longitude. 

ALTER TABLE accounts ADD latitude FLOAT(10,6) NULL AFTER zip
ALTER TABLE accounts ADD longitude FLOAT(10,6) NULL AFTER latitude

With the additional fields in the table, I needed to update the existing rows. I still had the 10 requests per second limit, but since I was not doing a lookup on the fly I could add the wait peroid.

<?php
define('GMAPS_API_URL_JSON', 'http://maps.googleapis.com/maps/api/geocode/json?address=');
define('GMAPS_API_URL_XML', 'http://maps.googleapis.com/maps/api/geocode/?address=');

$db = new mysqli('localhost', 'user', 'password', 'database');

// Create stream context options for http.
// Format:
// $context_opts['wrapper']['option']
$context_opts = array(
	'http' => array(
		'method' => 'GET',
		'header' => 'Accept-language: en\r\n'
		)
	);

// Take the context options and create the context
$context = stream_context_create($context_opts);

// Query the database
$results = $db->query("SELECT DISTINCT id, CONCAT_WS(' ', CONCAT_WS(', ', city, state, country), zip) AS address FROM accounts");

// Set limiting counter
$ctr = 0;

// Iterate through the results
while($row = $results->fetch_assoc()) {
	// Because there is a limit of 10 requests per second we want to put the script to sleep
	if($ctr == 10) {
		// Sleep for 2 seconds, just to be safe.
		sleep(2);
		// Reset the counter
		$ctr = 0;
	}
	// Request the information from Google
	$content = file_get_contents(GMAPS_API_URL_JSON . urlencode($row['address']) . "&sensor=true", false, $context);
	// Decode the JSON
	$json = json_decode($content);
	// Get the information from the JSON object.
	$location = $json->results[0]->geometry->location;
	// Update the database table.
	$db->query("UPDATE accounts SET latitude=" . $location->lat . ", longitude=" . $location->lng . " WHERE id=" . $row['id']);
	$ctr++;
}

With the accounts table now updated with the latitude and longitude, we now can write a simple script that queries the database and retrieves the coordinates for each account.

<?php
$output = '';
$db = new mysqli('localhost', 'user', 'pass', 'database');
$results = $db->query('SELECT latitude, longitude FROM accounts');
$output = array();
while($row = $results->fetch_assoc()) {
      $output[] = array('latitude' =>  $row['latitude'], 'longitude' => $row['longitude'] );
}
echo json_encode($output);

Finally we can construct the Google map.

<script type="text/javascript">
function initialize() {
    	var mapOptions = {
      		center: new google.maps.LatLng(43.05222, -87.95583),
      		zoom: 8,
      		mapTypeId: google.maps.MapTypeId.ROADMAP
      	};

	    var map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions);
	    $.get("data.php", {}, function(data) {
			var json = $.parseJSON(data);
			alert(json[10].latitude);
			for(var ctr = 0; ctr < json.length; ctr++) {
				var marker = new google.maps.Marker({
					map:map,
					position: new google.maps.LatLng(parseFloat(json[ctr].latitude), parseFloat(json[ctr].longitude))
					});
				}
	      });
    }
</script>