Performing Bulk Geocoding
As mentioned this methodology's greatest advantage is the ease in which it can help geocode large numbers of addresses. For example, suppose you wanted to create a map which charted the locations of area hospitals. For the purposes of this exercise I'll choose from five Columbus, OH-based hospitals, including The Ohio State University Medical Center, Children's Hospital, Grant Medical Center, Riverside Methodist Hospital, and Mount Carmel East. The hospital names and their corresponding addresses are stored in a MySQL table which might look like this:
CREATE TABLE hospitals (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(75) NOT NULL,
address VARCHAR(100) NOT NULL,
city VARCHAR(50) NOT NULL,
state CHAR(2) NOT NULL,
zip CHAR(5) NOT NULL,
latitude FLOAT(8,6),
longitude FLOAT(8,6)
)
The following script will retrieve the address information found in this table, encoding it for transmission via HTTP, and output the appropriate latitudinal and longitudinal coordinates:
<?php
// Your Google Maps API key
$key = "YOUR_KEY_HERE";
// Connect to the MySQL database
$conn = mysql_connect("localhost", "jason", "secretpswd");
// Select the database
$db = mysql_select_db("googlemaps");
// Query the table
$query = "SELECT id, address, city, state FROM hospitals";
$result = mysql_query($query) or die(mysql_error());
// Loop through each row, submit HTTP request, output coordinates
while (list($id, $address, $city, $state) = mysql_fetch_row($result))
{
$mapaddress = urlencode("$address $city $state");
// Desired address
$url = "http://maps.google.com/maps/geo?q=$mapaddress&output=xml&key=$key";
// Retrieve the URL contents
$page = file_get_contents($url);
// Parse the returned XML file
$xml = new SimpleXMLElement($page);
// Parse the coordinate string
list($longitude, $latitude, $altitude) = explode(",", $xml->Response->Placemark->Point->coordinates);
// Output the coordinates
echo "latitude: $latitude, longitude: $longitude <br />";
}
?>
Executing this script produces the following output:
latitude: 39.993932, longitude: -83.018649
latitude: 39.952297, longitude: -82.978954
latitude: 39.960770, longitude: -82.990525
latitude: 40.031565, longitude: -83.031058
latitude: 39.957899, longitude: -83.019699
Of course, this script can be easily modified to insert the data into the appropriate row.
Conclusion
I'll continue expanding this series as Google evolves it's mapping API. In the meantime, I invite you to email me with URLs to your Google Maps mashups. Also, please feel free to email me with specific API features you'd like me to cover in future articles!
About the Author
W. Jason Gilmore has developed countless Web applications over the past seven years and has dozens of articles to his credit on Internet application development topics. He is the author of three books, including most recently "Beginning PHP 5 and MySQL 5: From Novice to Professional", (Apress), now in its second edition, and with co-author Robert Treat, "Beginning PHP and PostgreSQL 8: From Novice to Professional" (Apress). Jason is Apress' Open Source Editorial Director, and is the co-founder of IT Enlightenment, a technical training company. Check out his blog at http://www.wjgilmore.com/.
IE 7
Firefox 2.0
