---
title: Using PHP/MySQL w/Drupal to update missing Google Geocode data
description: "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() {"
---

[Blog ](https://www.metaltoad.com/blog)

# [Using PHP/MySQL w/Drupal to update missing Google Geocode data](https://www.metaltoad.com/blog/using-phpmysql-wdrupal-update-missing-google-geocode-data)

 Written by [Metal Toad Staff](https://www.metaltoad.com/blog/author/metal-toad-staff) | Mar 3, 2009 12:00:00 AM

 Filed under:

- [Drupal](https://www.metaltoad.com/help/drupal)
- [Google](https://www.metaltoad.com/help/google)

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);
    }
  }
}
```

[View full post](https://www.metaltoad.com/blog/using-phpmysql-wdrupal-update-missing-google-geocode-data)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Metal Toad Staff"
  },
  "dateModified" : "2025-04-23T17:49:31.634Z",
  "datePublished" : "2009-03-03T00:00:00Z",
  "headline" : "Using PHP/MySQL w/Drupal to update missing Google Geocode data",
  "image" : {
    "@type" : "ImageObject",
    "height" : 500,
    "url" : "https://6104926.fs1.hubspotusercontent-na1.net/hubfs/6104926/Imported_Blog_Media/Metal_Toad_blog_generic-Aug-11-2023-10-00-55-5257-PM.jpg",
    "width" : 870
  },
  "mainEntityOfPage" : "https://www.metaltoad.com/blog/using-phpmysql-wdrupal-update-missing-google-geocode-data",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60.0,
      "url" : "https://6104926.fs1.hubspotusercontent-na1.net/hubfs/6104926/social-suggested-images/6104926.fs1.hubspotusercontent-na1.nethub6104926hubfsMetal_Toad_Logo_orange_horizontal-50-1.jpg",
      "width" : 240.00002
    },
    "name" : "Blog"
  }
}
```