Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm using Google's geocoder to store results in database then display them on a map on another page. The problem is that each company profile page needs to be visited before it's geocoded.

Is there a way to run the geocoder if a company address's lat/lng is 0 (i.e it hasn't been geocoded)?

<?php 

require("database.php");
// Opens a connection to a MySQL server
$con = mysql_connect("localhost", $username, $password);

if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("medicom_wp", $con);


    $company = get_field('company_name');
    $address = get_field('address');
    $city = get_field('city');
    $post_code = get_field('post_code');

    $sql = sprintf("select count('x') as cnt from markers where `name` = '%s'", mysql_real_escape_string($company));
    $row_dup = mysql_fetch_assoc(mysql_query($sql,$con));
    if ($row_dup['cnt'] == 0) {
        mysql_query("INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('".$company."', '".$address.", ".$city.", ".$post_code."', '0.0', '0.0', '')");
}
wp_reset_query();


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

// 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 += 1000;
    } else {
      // failure to geocode
      $geocode_pending = false;
      echo "Address " . $address . " failed to geocoded. ";
      echo "Received status " . $status . "
\n";
    }
    usleep($delay);
  }
}


?>

Before anyone says, I actually followed a tutorial from Google themselves saying how to store geocode results in a database, so in my eyes it doesn't go against their TOS because they're telling you to do it!

share|improve this question
    
If you have the address stored you can bulk geocode the business address. TOS as long as your 'map' is a Google map you can store geocodes in a db. batchgeo.com does this –  Mapperz Jun 21 '12 at 13:25
    
@Mapperz Because the data is entered via Wordpress and can change or be added to at any time the process needs to be automatic not manual. –  Rob Jun 21 '12 at 13:33
    
you can use a MySQL trigger - dev.mysql.com/doc/refman/5.0/en/triggers.html when that is fired a geocode request is sent out and waits a response - if vaild then is stored in your db. –  Mapperz Jun 21 '12 at 13:36
add comment

1 Answer

You can use most of this code on this Tutorial Site:

http://www.1stwebdesigner.com/tutorials/interactive-travel-map-google-maps-api/

replacing the 'manual' intervention with a trigger in mysql when your wordpress is updated.

This will then geocode the new entry and store it back in the MySQL database.

The Official Tutorial only geocodes once - the mysql trigger will keep it up-to-date https://developers.google.com/maps/articles/phpsqlgeocode

Trigger solution - all this needs modifying to suite your needs http://www.daniweb.com/web-development/databases/mysql/threads/329172/grrr...-new-trigger-problem

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.