Well I would love to be able to release this back to the community as a complete Geocoding/Locator solution. The problem is that it currently requires a bit of CSV manipulation to upload the geocoded lat/long values into the template variables table.
Essentially the whole setup currently works as follows:
1. Addresses are put into a table called "markers" which looks like this:
id docid address lat lng
2. The lat and lng columns are automatically popluated using Googles own geocoding script
http://code.google.com/apis/maps/documentation/services.html#Geocoding
3. The table is then exported to CSV when I wrangle it so that I can upload the lat and lng as normal template variables to the modx_templatevaraible_values table.
4. The search is then performed with the script developed here.
That’s it.
Ideally I would like the geocoding script to query the modx table directly, rather than the custom table, and then write directly to the modx tv table, where the latutude and longitude values are empty. But the rather complex structure of the tables and my lack of knowlege about advanced SQL queries make this a tall order.
In case you were wondering, the geocoding script currently looks like this:
<?php
$username="username";
$password="pass";
$database="database";
define("MAPS_HOST", "maps.google.co.uk");
define("KEY", "ABQIAAAA_LQfAehgxBDv5NXAPWWRbhQismpbGqJLdUil0k2vBhWw9rjFrhT7SX_RqZ6lpB_-yXung9oWNazS_A");
// 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 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" . "&oe=utf8&sensor=true&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"];
$docid = $row["docid"];
$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 "<p>Address <strong>" . $address . "</strong> (Doc ID <strong>".$docid."</strong>) failed to geocode.<br />";
echo "Received status " . $status . "</p>
\n";
}
usleep($delay);
}
}
?>
Looking at it now, I see that there are some simple improvements that could be made, such as adding
but I have been preoccupied sorting out the search script.
On another note, the postcode maths is pretty correct I think, but some of the geocoding results leave something to be desired.
As I said, I would be more than happy to release this to the community, it’s just that it is (in my opinion) still lacking a bit of quality.
