Looking for paid help on small geocoding project
Posted: Thu Sep 10, 2009 3:39 am
Hi,
I'm looking to get the following script working (cant post attachment so sorry for listing long code). I got it from wheresrhys.co.uk but I just can't
get it to work.
Basically it reads a list of UK postcodes from a database, geocodes it via Google LocalSearch (important),
then stores the returned longitude and latitude values back in the database.
I set it up, it runs but does nothing. I dont't understand how it enters the loop to start.
Looking to get this done pretty quickly.
Please see attached file for more details.
Thanks
Stew
I'm looking to get the following script working (cant post attachment so sorry for listing long code). I got it from wheresrhys.co.uk but I just can't
get it to work.
Basically it reads a list of UK postcodes from a database, geocodes it via Google LocalSearch (important),
then stores the returned longitude and latitude values back in the database.
I set it up, it runs but does nothing. I dont't understand how it enters the loop to start.
Looking to get this done pretty quickly.
Please see attached file for more details.
Thanks
Stew
Code: Select all
1. Create a html page geocode.html with the following content:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" >
<head>
<title></title>
<meta name="description" content="" />
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" href="" type="text/css" media="screen" />
<script type="text/javascript" src="1_jquery-1.3.2.js"></script>
<script src="http://www.google.com/jsapi" type="text/javascript"></script>
<script type="text/javascript" src="geocode.js"></script>
</head>
<body>
<div id="counter"></div>
</body>
</html>
2. Create a php file (in the same directory), geocode.php, with the following rough structure (it will only be accessed via ajax, so is very stripped down):
<?php
require_once ('mysqlConnect.php'); //or other database connection details
if($_GET){
update_record();
send_new_data();
}
//gets the next record without a geocode and sends the id and postcode to the browser
function send_new_data() {
$query = @mysql_query("SELECT int_id, str_postcode FROM geocodes_table WHERE lat = '' AND str_postcode != '' ORDER BY int_id LIMIT 1");
if(($query) &&mysql_num_rows($query)) {
$row = mysql_fetch_array($query, MYSQL_ASSOC);
echo $row['int_id'].','.$row['str_postcode'];
}
}
//updates the last record with data sent from browser
function update_record() {
$int_id = $_GET['int_id'];
$lat = $_GET['lat'];
$lng = $_GET['lng'];
if($int_id > 0)
{
$update = "UPDATE geocodes_table SET lat = '".$lat."', lng = '".$lng."' WHERE int_id = ".$int_id;
$result = @mysql_query($update);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
if(mysql_affected_rows() == 1){
}
}
}
?>
3. Create a javascript file geocode.js, saved in the same directory again
var localSearch = new google.search.LocalSearch();
var int_id = 0;
var counter;
$(function() {
counter = $('#counter');
send_and_get_next(0, 0, 0)
});
function send_and_get_next(id, lat, lng) {
$.ajax({
type: "GET",
url: "postcode.php",
data: "int_id="+id+"&lat="+lat+"&lng="+lng,
success: function(response) {
response = response.split(',');
int_id = response[0];
$(counter).html(int_id);
getPointFromPostcode(response[1]);
}
});
}
function getPointFromPostcode(postcode) {
localSearch.setSearchCompleteCallback(null,
function() {
if (localSearch.results[0]) {
var resultLat = localSearch.results[0].lat;
var resultLng = localSearch.results[0].lng;
send_and_get_next(int_id, resultLat, resultLng);
}else{
send_and_get_next(int_id, 'error', 'error');
}
});
localSearch.execute(postcode+', uk');
}
4. Running the code
Once you’ve altered the database connection details, and SQL query to suit your setup, simply open geocode.html in your browser. A counter will tell you which record you’re on. To stop the code simply close your browser/browser tab.
How it all works
In a nutshell (ignoring the special case of starting off the loop) the code repeatedly performs the following process:
….in geocode.php, send_new_data() finds a record which has no latitude value and sends it’s id number and postcode as an ajax response to set_and_get_next(). This keeps track of the id in a global variable and sends the postcode to getPointFromPostcode(), which uses google’s local search to get a geocode. Once it’s found a geocode it passes it to set_and_get_next(), which sends it to geocode.php in an ajax request. There update_record()… well… updates the record, and send_new_data() finds a record which has no la….