My favorites ▼ | English ▼ | Sign in

Google Maps API

Google Maps API Premier

Same great maps plus a SLA, support, and control over ads

Geocoding Addresses with PHP/MySQL

Pamela Fox & Tom Manshreck, Google Geo Team
November 2007

This is the fourth article in our series on using PHP/MySQL with Google Geo APIs. If you're a PHP/MySQL developer, you may also be interested in our articles on loading markers from a database and creating KML from a database.

Objective

This tutorial is intended for developers who are familiar with PHP/MySQL, and want to learn how to use the Google Maps API Geocoder to geocode a database of addresses. 'Geocoding' is the process of converting an address into a set of latitude/longitude coordinates, making it possible to indicate addresses on a map. Though it is possible to geocode addresses on the fly using the Maps API GClientGeocoder JavaScript class, it is often preferable to pre-geocode addresses if possible to avoid repeated lookups for known addresses. This is common, for example, when creating a "store finder" to locate a company's businesses geographically on a map. Note that this technique should not be used for geocoding databases with thousands of addresses, as this goes against the intended use of this service.

The tutorial discusses how to set up known addresses within a MySQL database and then process those addresses into geographic coordinates, updating the database with the results. This tutorial is broken up into the following steps:


Creating an Address Table

Note: this tutorial walks you through creating and populating a table of addresses from scratch. If you already have a table of addresses available, simply ensure you add 'lat' and 'lng' fields as described below, and skip to the section on Processing Addresses with PHP.

In order to store the geocoding information associated with addresses, we need to design a proper schema for our data. In our example, we wish to show restaurants or bars on a map mashup. A typical database table for such establishments should contain fields that specify the following required fields:

  • The unique id of the address
  • The address of the establishment as a text string
  • The lat and lng values of the geocoded location

Additionally, we will want our address database to be usable to actual humans, so we should probably add a human-readable name for the establishment to uniquely identify it and just for fun, a type attribute to distinguish between restaurants and bars.

  • The name of the establishment as a text string
  • The type of the establishment (in this case a bar or a restaurant)

When you create your address table, note that lat and lng values in the Maps API need only 6 digits of precision to identify an establishment uniquely. To keep storage space requirements low, we specify lat and lng fields as numbers of size FLOAT(10,6). Floats of this size will store 6 digits of precision following the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees.

A sample definition of our address table using the phpMyAdmin interface appears below:

If you don't have access to phpMyAdmin or prefer using SQL commands instead, the SQL statement to create the address table is shown below (phpsqlgeocode_createtable.sql): 

CREATE TABLE `markers` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL ,
  `type` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;

Populating the Address Table

After creating our table of addresses, it's time to populate it with (ungeocoded) data. Sample data for 10 Seattle establishments is shown below in CSV format (phpsqlgeocode_data.csv):

Pan Africa Market,"1521 1st Ave, Seattle, WA",0.0,0.0,restaurant
Buddha Thai & Bar,"2222 2nd Ave, Seattle, WA",0.0,0.0,bar
The Melting Pot,"14 Mercer St, Seattle, WA",0.0,0.0,restaurant
Ipanema Grill,"1225 1st Ave, Seattle, WA",0.0,0.0,restaurant
Sake House,"2230 1st Ave, Seattle, WA",0.0,0.0,bar
Crab Pot,"1301 Alaskan Way, Seattle, WA"0.0,0.0,restaurant
Mama's Mexican Kitchen,"2234 2nd Ave, Seattle, WA",0.0,0.0,bar
Wingdome,"1416 E Olive Way, Seattle, WA",0.0,0.0,bar
Piroshky Piroshky,"1908 Pike pl, Seattle, WA",0.0,0.0,restaurant

Microsoft Excel and Google Spreadsheets both export to CSV (comma-separated values) format, and phpMyAdmin provides an IMPORT tab to import various file formats. A screenshot of phpMyAdmin used to transform this CSV into table data is shown below:

If you'd rather not use the phpMyAdmin interface, the SQL statements that accomplish the same results are show below (phpsqlgeocode_data.sql): 

INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Pan Africa Market', '1521 1st Ave, Seattle, WA', '0.0', '0.0', 'restaurant');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Buddha Thai & Bar', '2222 2nd Ave, Seattle, WA', '0.0', '0.0', 'bar');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`) VALUES ('The Melting Pot', '14 Mercer St, Seattle, WA', '0.0', '0.0', 'restaurant');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Ipanema Grill', '1225 1st Ave, Seattle, WA', '0.0', '0.0', 'restaurant');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Sake House', '2230 1st Ave, Seattle, WA', '0.0', '0.0', 'bar');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Crab Pot', '1301 Alaskan Way, Seattle, WA', '0.0', '0.0', 'restaurant');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Mama\'s Mexican Kitchen', '2234 2nd Ave, Seattle, WA', '0.0', '0.0', 'bar');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Wingdome', '1416 E Olive Way, Seattle, WA', '0.0', '0.0', 'bar');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Piroshky Piroshky', '1908 Pike pl, Seattle, WA', '0.0', '0.0', 'restaurant');

Processing the Addresses with PHP

At this point, you should have a table named markers filled with sample data. You now need to write some PHP to iterate through the table, send a request to the geocoder, handle the response, and update the table appropriately.

Before you begin, note that you should generally put your database connection information in a separate file. This is a good idea whenever you're using PHP to access a database, as it keeps confidential information in a file that you won't be tempted to share. In the Maps API forum, we've occasionally had people accidentally publish their database connection information when they were just trying to debug their XML-outputting code. The access file should look like this, but with your own database information (phpsqlgeocode_dbinfo.php):

<?
$username="username";
$password="password";
$database="username-databaseName";
?>

Now, we need to create another PHP file to iterate through these addresses, geocode the set of addresses, return the geocoded information, and update the fields with geocoded lat and lon values.

Retrieving the Addresses

To retrieve our addresses, we need to connect to our MySQL server, select the database in question, and run a query on the database to return those rows of interest. To do so, we use mysql_connect to establish a connection, mysql_select_db to select our particular database, and mysql_query to perform the address lookup on the database. We then use mysql_fetch_assoc to retrieve an associative array representing a row of the query results. Your server's PHP must be compiled with mysql functions enabled — if you get an error about them being undefined, read this page to learn how to recompile your PHP with mysql enabled.

Choosing Geocoder Output

The HTTP geocoder can respond with either XML or CSV output through changing the "output" query parameter. Examples of both responses are shown in the Maps API documentation. If your server is enabled with PHP 5 and the SimpleXML extension, it is preferable to retrieve XML output. Use the PHP simplexml_load_file function to create an XML object from the geocoder response, and use XPath expressions to extract the coordinates and status code.

If you are using PHP4 instead, you can still retrieve CSV output and process those results. Use the PHP function file_get_contents to retrieve the CSV response, and then split to separate out the coordinate values and status code of the returned CSV.

Timing the Geocode Requests

Regardless of what geocoder output you specify, your requests will be subject to the geocoder's maximum query rate and 15,000 queries per day based on your IP. Additionally, a status code of 620 will be returned by the geocoder if you query it faster than it can handle. (A full list of status codes is available here). To ensure you don't send queries too rapidly to the geocoder, you can specify a delay between each geocode request. We can increase this delay each time we receive a 620 status, and use a while loop to ensure we've successfully geocoded an address before iterating to the next one.

Changing the Base Country

The Maps geocoder is programmed to bias its results depending on from which domain it receives requests. For example, entering "syracuse" in the search box on maps.google.com will geocode the city of "Syracuse, NY", while entering the same query on maps.google.it (Italy's domain) will find the city of "Siracusa" in Sicily. You would get the same results by sending that query through HTTP geocoding to maps.google.it instead of maps.google.com, which you can do by modifying the MAPS_HOST constant in the sample code below. Note: You cannot send a request to a non-existent maps.google.* server, so ensure that a country domain exists before redirecting your geocoding queries to it.

Updating the Database

Now that we've sent off requests to the geocoder and received responses back, we need a way to place that new information in the MySQL database. We do so using the MySQL UPDATE command. A sample update command appears below: 

UPDATE markers SET lat = 45.5644, lng = 56.89 WHERE id = 1 LIMIT 1;",

This command instructs MySQL to update the marker table, setting the lat and lon fields to the given values where the id of the row matches the passed id. We also instruct MySQL to update at most one row. Once, we've updated the MySQL database for each address, our work here is done.



Sample PHP Code

Note that the sample code below uses a dummy KEY constant — you'll need to replace this key with your own key or all of your requests will return a 610 status code.

Sample code for geocoding with PHP 5 and XML output is shown below (phpsqlgeocode_xml.php):

<?php
require("phpsqlgeocode_dbinfo.php");

define("MAPS_HOST", "maps.google.com");
define("KEY", "abcdefg");

// Opens a connection to a MySQL server
$connection = mysql_connect("localhost", $username, $password);
if (!$connection) {
  die("Not connected : " . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die("Can\'t use db : " . mysql_error());
}

// Select all the rows in the markers table
$query = "SELECT * FROM markers WHERE 1";
$result = mysql_query($query);
if (!$result) {
  die("Invalid query: " . mysql_error());
}

// Initialize delay in geocode speed
$delay = 0;
$base_url = "http://" . MAPS_HOST . "/maps/geo?output=xml" . "&key=" . KEY;

// Iterate through the rows, geocoding each address
while ($row = @mysql_fetch_assoc($result)) {
  $geocode_pending = true;

  while ($geocode_pending) {
    $address = $row["address"];
    $id = $row["id"];
    $request_url = $base_url . "&q=" . urlencode($address);
    $xml = simplexml_load_file($request_url) or die("url not loading");

    $status = $xml->Response->Status->code;
    if (strcmp($status, "200") == 0) {
      // Successful geocode
      $geocode_pending = false;
      $coordinates = $xml->Response->Placemark->Point->coordinates;
      $coordinatesSplit = split(",", $coordinates);
      // Format: Longitude, Latitude, Altitude
      $lat = $coordinatesSplit[1];
      $lng = $coordinatesSplit[0];

      $query = sprintf("UPDATE markers " .
             " SET lat = '%s', lng = '%s' " .
             " WHERE id = '%s' LIMIT 1;",
             mysql_real_escape_string($lat),
             mysql_real_escape_string($lng),
             mysql_real_escape_string($id));
      $update_result = mysql_query($query);
      if (!$update_result) {
        die("Invalid query: " . mysql_error());
      }
    } else if (strcmp($status, "620") == 0) {
      // sent geocodes too fast
      $delay += 100000;
    } else {
      // failure to geocode
      $geocode_pending = false;
      echo "Address " . $address . " failed to geocoded. ";
      echo "Received status " . $status . "
\n";
    }
    usleep($delay);
  }
}
?>

Sample code for geocoding with PHP 4+ and CSV output is shown below (phpsqlgeocode_csv.php):

<?php
require("phpsqlgeocode_dbinfo.php");

define("MAPS_HOST", "maps.google.com");
define("KEY", "abcdefg");

// Opens a connection to a MySQL server
$connection = mysql_connect("localhost", $username, $password);
if (!$connection) {
  die("Not connected : " . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die("Can\'t use db : " . mysql_error());
}

// Select all the rows in the markers table
$query = "SELECT * FROM markers2 WHERE 1";
$result = mysql_query($query);
if (!$result) {
  die("Invalid query: " . mysql_error());
}

// Initialize delay in geocode speed
$delay = 0;
$base_url = "http://" . MAPS_HOST . "/maps/geo?output=csv&key=" . KEY;

// Iterate through the rows, geocoding each address
while ($row = @mysql_fetch_assoc($result)) {
  $geocode_pending = true;

  while ($geocode_pending) {
    $address = $row["address"];
    $id = $row["id"];
    $request_url = $base_url . "&q=" . urlencode($address);
    $csv = file_get_contents($request_url) or die("url not loading");

    $csvSplit = split(",", $csv);
    $status = $csvSplit[0];
    $lat = $csvSplit[2];
    $lng = $csvSplit[3];
    if (strcmp($status, "200") == 0) {
      // successful geocode
      $geocode_pending = false;
      $lat = $csvSplit[2];
      $lng = $csvSplit[3];

      $query = sprintf("UPDATE markers2 " .
             " SET lat = '%s', lng = '%s' " .
             " WHERE id = %s LIMIT 1;",
             mysql_real_escape_string($lat),
             mysql_real_escape_string($lng),
             mysql_real_escape_string($id));
      $update_result = mysql_query($query);
      if (!$update_result) {
        die("Invalid query: " . mysql_error());
      }
    } else if (strcmp($status, "620") == 0) {
      // sent geocodes too fast
      $delay += 100000;
    } else {
      // failure to geocode
      $geocode_pending = false;
      echo "Address " . $address . " failed to geocoded. ";
      echo "Received status " . $status . "
\n";
    }
    usleep($delay);
  }
}
?>

Conclusion

If you've followed along, you now know how to geocode sample data, process it using PHP, and store it a MySQL database. If you find that you need to geocode more than 15K addresses per day, or that the Google geocoder doesn't cover the regions you're interested in, then consider using additional geocoding web services. A list of geocoding resources is available here.

Now that you've finished geocoding your addresses, check out our articles on loading markers from a database and creating KML from a database. If you have any problems with or questions about this tutorial, please post in the Maps API forum.