Page 1 of 1

Looking for paid help on small geocoding project

Posted: Thu Sep 10, 2009 3:39 am
by stewc
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

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….
 
 

Re: Looking for paid help on small geocoding project

Posted: Wed Sep 23, 2009 9:48 am
by onion2k
Is there a specific reason why it has to be Google Local?

Coz if there isn't... http://wikileaks.org/wiki/UK_government ... 8_Jul_2009 ... the entire Post Office postcodes database complete with GPS coordinates.

Re: Looking for paid help on small geocoding project

Posted: Tue Sep 29, 2009 11:17 am
by stewc
its more accurate for UK postcodes - thanks for the link too - very useful