Using PHP/MySQL w/Drupal to update missing Google Geocode data

We recently needed to fill missing geocode values in a project's database. Using some PHP magic and taking advantage of hook_cron() this is how we filled the missing values:

/**
 * Implementation of hook_cron()
 * When the cron runs, try to resolve any locations that have a lat or lon = 0
 */
function overmap_cron() {
  // Fetch all locations with lat == 0 || lon == 0 and bin into $locations array
  $locations = array();
  $query = "
    SELECT *
    FROM {location} l
    WHERE l.latitude=0 
    AND l.longitude=0";
  $result = db_query($query);
  while ($data = db_fetch_array($result)) {
    $locations[] = $data;
  }
 
  // Loop through the geocode-less addresses and re-submit to google maps to try to get valid location data
  $identifiers = array('name', 'street', 'city', 'province', 'postal_code', 'country');
  foreach ($locations as $location) {
    // Boolean sentinel variable that control while loop
    $geocode_pending = true;
    while ($geocode_pending) {
      // Build address array and implode into single string
      $search = array();
      foreach ($identifiers as $identifier) {
        if ($location[$identifier] != '') {
          $search[] = trim($location[$identifier]);
        }
      }
      $search = implode(' ', $search);
 
      // Execute query and loda into an xml object
      $search = 'http://maps.google.com/maps/geo?output=xml&q=' . urlencode($search);
      $xml = simplexml_load_file($search);
 
      // Deal with returned values based on the status code
      $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
        $lat = $coordinatesSplit[1];
        $lng = $coordinatesSplit[0];
        // Create new object and update record in DB correct geocode data
        $record = new stdClass();
        $record->lid = $location['lid'];
        $record->latitude = $lat;
        $record->longitude = $lng;
        drupal_write_record('location', $record, 'lid');
      } else if (strcmp($status, "620") == 0) {
        // sent geocodes too fast
        $delay += 100000;
      } else {
        // failure to geocode
        $geocode_pending = false;
      }
      usleep($delay);
    }
  }
}
Filed under:

Comments

This is an amazing little tool. Thank You. I have been looking for it for while.

My only comment is for people that are only getting some locations to update. If you remove the 'name' from the identifiers array google will find the addresses easier.

cheers,

you can run this code with DEVEL module > PHP execute php block.

Here the code for Drupal 7 ( I added a quick patch for french postcode)

thanks a lot for this code ! save my nights !

   $locations = array();
  $query = "
    SELECT *
    FROM {location} l
    WHERE l.latitude=0 
    AND l.longitude=0";
 
$uid = 1;
 $result = db_query($query, array(':uid' => $uid));
print $result->rowCount();
 
 
 while ($data = $result->fetchAssoc()) {
    $locations[] = $data;
  }
 
  // Loop through the geocode-less addresses and re-submit to google maps to try to get valid location data
  $identifiers = array('name', 'street', 'city', 'province', 'postal_code', 'country');
  foreach ($locations as $location) {
    // Boolean sentinel variable that control while loop
    $geocode_pending = true;
    while ($geocode_pending) {
 
	  // add "0" in front postal_code with lenth of 4 letters
	  //if (strlen($identifiers['4']) = 4) {
		//  $identifiers['4'] = "0" . $identifiers['4'];
	  //}
 
	  //print_r($identifiers);
 
	  // Build address array and implode into single string
	  $search = array();
      foreach ($identifiers as $identifier) {
        if ($location[$identifier] != '') {
 
		  //correct French Post_Code with adding zero in front of 4 digits post code. 
		  if (substr($location[$identifier], 0, 1) == '4' and strlen($location[$identifier] = 4)) {
			$location[$identifier] = "0" . $location[$identifier];
		  }
		  $search[] = trim($location[$identifier]);
        }
      }
      $search = implode(' ', $search);
 
      // Execute query and loda into an xml object
      $search = 'http://maps.google.com/maps/geo?output=xml&q=' . urlencode($search);
	  print " \n ";
	  print $search;
      $xml = simplexml_load_file($search);
 
      // Deal with returned values based on the status code
      $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
        $lat = $coordinatesSplit[1];
        $lng = $coordinatesSplit[0];
        // Create new object and update record in DB correct geocode data
        $record = new stdClass();
        $record->lid = $location['lid'];
        $record->latitude = $lat;
        $record->longitude = $lng;
        drupal_write_record('location', $record, 'lid');
      } else if (strcmp($status, "620") == 0) {
        // sent geocodes too fast
        $delay += 100000;
      } else {
        // failure to geocode
        $geocode_pending = false;
      }
      usleep($delay);
    }
  }
 
 
  print "resultat";
 
 
    $locations = array();
  $query = "
    SELECT *
    FROM {location} l
    WHERE l.latitude=0 
    AND l.longitude=0";
 
$uid = 1;
 $result = db_query($query, array(':uid' => $uid));
print $result->rowCount(); 
 

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <cpp>, <java>, <php>. The supported tag styles are: <foo>, [foo].
  • Web page addresses and email addresses turn into links automatically.
  • Lines and paragraphs break automatically.

Ready for transformation?