cron job - geocode address field to lat & long in mySQL database

Question

Has anyone done this, or have an idea?

I have a real estate script that for whatever reasons (google mapping limits, script itself) only gets the coordinates for half of the listings. I want to run a script that of the lat & long field are empty the script will use the address, city, state to populate the lat & long fields.

THoughts?

Edit:

Here's a code from google, that does allow you to geocode for a database: http://code.google.com/apis/maps/articles/phpsqlgeocode.html

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

I'm not sure where to start getting this to work with my database and my lat & long rows (declat and declong)

Any help would be appreciated. Thanks!

Answer 1

The best approach is obviously parsing the response and looking for the error code returned.

The most usual reason for this happening is

• The requests going out too fast (add some throttling to your script)

• Unicode characters like ÄÖÜ Umlauts being encoded wrongly

By the way, what you doing may be a violation of Google's Terms of Service:

Note: the Geocoding API may only be used in conjunction with a Google map; geocoding results without displaying them on a map is prohibited. For complete details on allowed usage, consult the Maps API Terms of Service License Restrictions.

Answer 2

You are allowed 2500 geocodes per day. That's is a fair amount, but you can't just bombard the service with your requests. You need to provide a delay between each request, or else it will start blocking service.

Once you geocode, you should store the value within the database and mark it as geocoded, so you don't have to request it again. Then you can just geocode the rest as time goes on.