HOW TO: Get Coordinates (Latitude/Longitude) for Company Directory
Posted: Mon Aug 30, 2010 10:32 pm
I found a way to ge tthe latitude and longitudes in Company Directory so if you, like me, have thousands of entries, this may be a good script for you.
1) Create a file to connect to your database (dbconnect.php):
2) Create another file (getgeocode.php) here and make sure you replace YOUR_API_KEY with your own:
That's it. Now just upload the files to your public directory and run getgeocode.php (http://yourdomain.com/getgeocode.php)
1) Create a file to connect to your database (dbconnect.php):
Code: Select all
<?
$username="USERNAME";
$password="PASSWORD";
$database="DATABASE";
?>
Code: Select all
<?php
require("dbconnect.php");
define("MAPS_HOST", "maps.google.com");
define("KEY", "YOUR_API_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 cms_module_compdir_companies 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=csv&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);
$csv = file_get_contents($request_url) or die("url not loading");
$csvSplit = split(",", $csv);
$status = $csvSplit[0];
$lat = $csvSplit[2];
$lng = $csvSplit[3];
if (strcmp($status, "200") == 0) {
// successful geocode
$geocode_pending = false;
$lat = $csvSplit[2];
$lng = $csvSplit[3];
$query = sprintf("UPDATE cms_module_compdir_companies " .
" SET latitude = '%s', longitude = '%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);
}
}
?>
