You are here

Home » Blogs » badjava's blog

Quick and dirty data geocoding using Drupal 6 and Google's Geocoding API

published by badjava on Thu, 11/03/2011 - 14:12
AttachmentSize
code.php_.txt2.79 KB
Tags: 
drupal 6
geocoding
Google API
mysql

I recently got a bunch of business data that I needed to match up with existing data for one of the sites that I look after. Unfortunately, the data was not in a super convenient format nor was it geocoded. There are some really neat APIs that have recently come out that make problems like this much easier to solve such as Factual's Crosswalk API but unfortunately this isn't available in Canada yet. Anyways, I managed to geocode the data pretty quickly and then match it up using a phone number or textual comparison.

I happened to have an older function laying around to geocode data using V2 of Google's Geocoding API. Although this doesn't support all the fancy new features of V3 such as neighbourhood data, it serves my purpose and has the added bonus of allowing me to do more than 2500 queries in a day which is the cap on V3's free version. You will need an API key, however.

I've pasted the code at the bottom of this post and attached a file as well in case there are formatting issues. The code was originally designed to work within a Drupal module specifically with nodes and can easily be adapted to do that again. That is why it makes a fake node and uses that information to do the geocoding.

The first function tools_geocode_data needs to be modified to your specific use case and match up with your data. In my case, I am querying a mySQL database and iterating thru a table called "data". You also will need to edit the $api variable in the tools_geocode_location function and enter your Google API key.

Happy coding!


function tools_geocode_data() {

$result = db_query("SELECT * FROM {data} WHERE (lat = 0 OR lat IS NULL)");

while ($data = db_fetch_object($result)) {

$node = new stdClass();

$node->field_location_street[0]['value'] = $data->address . " " . $data->city;
$node->field_location_postal[0]['value'] = $data->zipcode1 . $data->zipcode2;

$location = tools_google_geocode_location($node);

//drupal_set_message("GEOCODE: " . $result['lat'] . " - " . $result['lon']);

if ($location['lat']) {
$cnt += 1;
db_query("UPDATE {justeatimport} SET lat = %f, lon = %f WHERE id = %d", $location['lat'], $location['lon'], $data->id);
usleep(250000);
}

}

drupal_set_message("Updated $cnt businesses.");

}

function tools_geocode_location($node) {

$key = "INSERT YOUR API KEY HERE";

$query = array(
'key' => $key,
'sensor' => 'false', // Required by TOS.
'output' => 'xml',
//'ll' => 0,
//'spn' => 0,
'gl' => 'CA',
'q' => tools_address_flatten($node),
);

$url = url('http://maps.google.com/maps/geo', array(
'query' => $query,
'external' => TRUE,
));

//drupal_set_message("GOOGLE: " . $url);

$http_reply = drupal_http_request($url);

$status_code_match = array();
preg_match('/(.*)/', $http_reply->data, $status_code_match);
$status_code = $status_code_match[1];
if ($status_code != 200) {
return NULL;
}

$accuracy_code_match = array();
preg_match('/Accuracy="([0-9])"/', $http_reply->data, $accuracy_code_match);
$accuracy_code = $accuracy_code_match[1];
if ($accuracy_code return NULL;
}

$latlon_match = array();
preg_match('/(.*)/', $http_reply->data, $latlon_match);

//drupal_set_message("lat/lon: " . $latlon_match[1], 'status');

$latlon_exploded = explode(',', $latlon_match[1]);

return array('lat' => $latlon_exploded[1], 'lon' => $latlon_exploded[0]);

}

function tools_address_flatten($node) {
// Check if its a valid address
if (empty($node)) {
return '';
}

if ($node->nid) {
$term = taxonomy_node_get_terms_by_vocabulary($node, 1, 'vid');
$city = $term[1]->name;
$province = $term[1]->description;
}

$address = '';
if (!empty($node->field_location_street[0]['value'])) {
$address .= $node->field_location_street[0]['value'];
}

if (!empty($city)) {
if (!empty($node->field_location_street[0]['value'])) {
$address .= ', ';
}
$address .= $city;
}

if (!empty($province)) {
if (!empty($node->field_location_street[0]['value']) || !empty($node->field_location_city[0]['value'])) {
$address .= ', ';
}

$address .= $province;
}

if (!empty($node->field_location_postal[0]['value'])) {
if (!empty($address)) {
$address .= ' ';
}
$address .= $node->field_location_postal[0]['value'];
}

return $address;
}

?>