PHP  Geo Encoding Using ODBC Connection

Ross Turner
Ross Turner used Ask the Experts™
on
Hi EE,

I'm trying to use this  Article to make a geo encoding script so i can get the long and lat for a list of customers.

However I'm not using mySQL as my connection string it's ODBC.

I seem to be having a devil of a job converting it to work can anyone help?

<?php
include 'connection.php';

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


// Select all the rows in the markers table
$query = "SELECT * from dbo.geoencoder";
$result = odbc_exec($conn,$query);
if (!$result) {
  die("Invalid query: ");
}

// 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 = odbc_fetch_row($result)) {
  $geocode_pending = true;

  while ($geocode_pending) {
	$address = odbc_result($row,"Address");
	$id = odbc_result($row,"[CallID");
    $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 markers2 " .
             " 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 "Address " . $address . " failed to geocoded. ";
      echo "Received status " . $status . "
\n";
    }
    usleep($delay);
  }
}
?>

Open in new window


Thank you

Ross Turner
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016

Commented:
What exactly is the problem?
Ross TurnerManagement Information Support Analyst

Author

Commented:
Hi Ray_Paseur,

What I'm trying to achieve in the long run is when my maps page is loaded on our local intranet it will show all our current issue locations on a google earth api thats embedded.

All my locations that I have in my database are addresses and postcodes not Long / Lat co-ordinates.

So I might be completely wrong here...but I believe you have to geo encode the addresses before you can pass them to Google Earth.

So what i was trying to do is:
1) Get the addresses out of my database (odbc connection)
2) Send them via the google script above to be geo encoded.
3)And then somehow get them to Display in Google Earth.


So far when i run the php script it hit these errors:

1:Warning: odbc_result() expects parameter 1 to be resource, boolean given in C:\wamp\www\Service\geoencoder.php on line 24

2:Warning: odbc_result() expects parameter 1 to be resource, boolean given in C:\wamp\www\Service\geoencoder.php on line 25

3:Function split() is deprecated in C:\wamp\www\Service\geoencoder.php on line 31

4:Undefined offset: 3 in C:\wamp\www\Service\geoencoder.php on line 34

and to top it off it then says

5:Address failed to geocoded. Received status 602


I thought well there might be an issue with the data coming out of the db.

so I create the same script and echo'd it to a table and it worked great, all the addresses came out on screen.

Just a little bit stumped at the moment.....
Most Valuable Expert 2011
Top Expert 2016

Commented:
Looking at line 20:

while ($row = odbc_fetch_row($result)) {

What is in $row?  You can use var_dump() to print out the contents.

Split() can be replaced with this:
http://php.net/manual/en/function.preg-split.php
http://php.net/manual/en/function.explode.php

You might consider geocoding the addresses once, and saving the lat/lon pair in your data base so you do not have to call Google repeatedly for the same information.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Ross TurnerManagement Information Support Analyst

Author

Commented:
hmm... well i ran the var_dump($row) variable (which is very handy!!!) and it came out with an address :) and well because my query is * from the table all the other columns but i can see:

address' => string 'xxxxx,xxxxx,xxxxx,xxxxxx' (length=56)
Ross TurnerManagement Information Support Analyst

Author

Commented:
Hey Ray_Paseur,

I managed to come up with this and it's encoding all the address :)
 
What do you think this script look ok ?

<?php
include 'connection.php';

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


// Select all the rows in the markers table
$query = "SELECT address from dbo.geoencoder";
$result = odbc_exec($conn,$query);
if (!$result) {
  die("Invalid query: ");
}


// 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 = odbc_fetch_array($result)) {
 $geocode_pending = true;
 


 while ($geocode_pending) {
	$address=odbc_result($result,'address');	
    $request_url = $base_url . "&q=" . urlencode($address);
    $csv = file_get_contents($request_url) or die("url not loading");
 
//var_dump($csv);
		
    $csvSplit = explode(",",$csv);

//var_dump($csvSplit);

    $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];

echo " <b>Address:</b> " . $address ." <b>Lat:</b> " . $lat ." <b>Lng:</b> " . $lng." <br> " ;

    } 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);
  }
}
?>

Open in new window


Cheers
Most Valuable Expert 2011
Top Expert 2016
Commented:
It looks good to me, although you might want to make the usleep() function call unconditional if you're going to be hitting the API with a lot of calls.  By way of sharing, here is my geocoder example.
<?php // RAY_class_SimpleGeoCoder.php
error_reporting(E_ALL);


// DEMONSTRATE HOW TO CALL THE YAHOO! AND GOOGLE GEOCODERS


// A NOMINAL DATA CLEAN-UP FUNCTION
function get_clean_text_string($s)
{
    return preg_replace('/[^A-Z 0-9_\.,\-#]/i', NULL, $s);
}


// A FREEFORM LOCATION STATEMENT IS OPTIONAL (BUT NOT USED HERE)
$location = $a = $c = $s = $z = NULL;

// THESE ARE THE GET-FORM VARIABLES
$vars = array( 'a', 'c', 's', 'z' );

// FILTER THE ARGUMENTS
if (!empty($_GET))
{
    foreach ($_GET as $k => $v)
    {
        if (!in_array($k, $vars)) continue;
        $$k = get_clean_text_string($v);
    }
    if ($a != NULL) { $location .= $a . ' '; }
    if ($c != NULL) { $location .= $c . ' '; }
    if ($s != NULL) { $location .= $s . ' '; }
    if ($z != NULL) { $location .= $z . ' '; }
    $location = trim($location);
}

// PREPARE THE FORM
$form = <<<ENDFORM
<doctype html>
<head>
<title>$a $c $s $z Yahoo/Google Geocoder Query</title>
</head>
<body>
<form method="get">
Addr: <input type="text" name="a" autocomplete="off" value="$a" />
City: <input type="text" name="c" autocomplete="off" value="$c" />
ST:   <input type="text" name="s" autocomplete="off" value="$s" size="2" />
Zip:  <input type="text" name="z" autocomplete="off" value="$z" size="8" />
<input type="submit" value="Go" />
</form>
</body>
</html>
ENDFORM;

if (!$location)
{
    echo $form;
    die();
}


// PREPARE THE GEOCODER
$y_demo          = new SimpleGeoCoder;
$y_demo->address = $a;
$y_demo->city    = $c;
$y_demo->state   = $s;
$y_demo->zip     = $z;

// TEST THE YAHOO! GEOCODER
$y_demo->geocodeYahoo();
echo "<pre>";
echo PHP_EOL;
echo "YAHOO! DATA FOR <strong>$location</strong>";
echo PHP_EOL;
print_r($y_demo);
echo "</pre>";



// PREPARE THE GEOCODER
$g_demo           = new SimpleGeoCoder;
$g_demo->address  = $a;
$g_demo->city     = $c;
$g_demo->state    = $s;
$g_demo->zip      = $z;

// TEST THE GOOGLE GEOCODER
$g_demo->geocodeGoogle();
echo "<pre>";
echo PHP_EOL;
echo "GOOGLE DATA FOR <strong>$location</strong>";
echo PHP_EOL;
print_r($g_demo);
echo "</pre>";


// ALL DONE
echo $form;
die();



// SIMPLE GEOCODER CLASS
class SimpleGeoCoder
{

    // DECLARE THE INPUT DATA
    public $location; // USE THIS FOR A FREEFORM QUERY, OR USE THESE PARTS
    public $address;
    public $city;
    public $state;
    public $zip;

    // DECLARE THE OUTPUT DATA
    public $latitude;
    public $longitude;
    public $precision;
    public $warning;
    public $geocoder;

    // DECLARE THE CONSTRUCTOR
    public function __construct()
    {
        $this->latitude  = 0.0;
        $this->longitude = 0.0;
        $this->precision = 0;   // HIGHER IS BETTER, BUT Y AND G USE DIFFERENT SCALES
        $this->warning   = '';
        $this->geocoder  = '';
    }

    // DECLARE THE YAHOO! VERSION OF THE WORKHORSE
    public function geocodeYahoo()
    {
        $this->geocoder = 'Yahoo!';
        $yahooUrl       = "http://where.yahooapis.com/geocode?gflags=Q&appid=";
        if (defined("YAHOO_API"))
        {
            $yahooUrl .= YAHOO_API;
        }

        // YOU CAN ASK FOR A FREEFORM QUERY
        if ($this->location != '')
        {
            $yahooUrl .= "&location=" . urlencode($this->location);
        }

        // YOU CAN ASK FOR INDIVIDUAL PIECES OF AN ADDRESS
        else
        {
            $loc = urlencode
            ( trim($this->address)
            . ', '
            . trim($this->city)
            . ', '
            . trim($this->state)
            . ' '
            . trim($this->zip)
            )
            ;
            $yahooUrl .= "&location=" . $loc;
        }

        // EXECUTE YAHOO GEOCODER QUERY SEE http://developer.yahoo.com/geo/placefinder/guide/requests.html
        // NOTE - USE ERROR SUPPRESSION OR IT WILL BARK OUT THE YAHOO API KEY - ON FAILURE RETURNS HTTP 400 BAD REQUEST
        if ($yfp = @fopen($yahooUrl, 'r'))
        {
            $yahooResponse = '';
            while (!feof($yfp)) { $yahooResponse .= fgets($yfp); }
            fclose($yfp);
        }
        else
        {
            return FALSE;
        }

        // EXAMINE THE RESULT
        if ($yahooResponse != '') // NOT EMPTY, WE GOT DATA
        {
            $ydata    = new SimpleXMLElement($yahooResponse);
            // echo "<pre>";var_dump($ydata);

            // CHECK FOR ANY ERROR MESSAGE, IF NONE, EXTRACT THE DATA POINTS
            $y_err    = (string)$ydata->Error;
            if ($y_err)
            {
                $this->warning   = $ydata->ErrorMessage;
                return FALSE;
            }
            else
            {
                $this->precision = (string)$ydata->Result->quality;
                $this->latitude  = (string)$ydata->Result->latitude;
                $this->longitude = (string)$ydata->Result->longitude;
                $this->address   = (string)$ydata->Result->line1;
                $this->city      = (string)$ydata->Result->city;
                $this->state     = (string)$ydata->Result->statecode;
                $this->zip       = (string)$ydata->Result->postal;

                $this->location
                = (string)$ydata->Result->line1
                . ', '
                . (string)$ydata->Result->line2
                . ' '
                . (string)$ydata->Result->line3
                ;
            }
        }

        // NO RESULT - SOMETHING IS SICK AT YAHOO
        else
        {
            return FALSE;
        }
        return TRUE;
    } // END function geocodeYahoo



    // DECLARE THE GOOGLE VERSION OF THE WORKHORSE
    public function geocodeGoogle()
    {
        $this->geocoder = 'Google';
        $googleUrl      = "http://maps.google.com/maps/geo?output=csv";
        $googleUrl      = "http://maps.googleapis.com/maps/api/geocode/xml?sensor=false";
        if (defined("GOOGLE_API"))
        {
            $googleUrl .= "&key=" . GOOGLE_API;
        }

        // YOU CAN ASK FOR A FREEFORM QUERY
        if ($this->location != '')
        {
            $googleUrl .= "&address=" . urlencode(trim($this->location));
        }

        // YOU CAN ASK FOR INDIVIDUAL PIECES OF AN ADDRESS
        else
        {
            $loc = urlencode
            ( trim($this->address)
            . ', '
            . trim($this->city)
            . ', '
            . trim($this->state)
            . ' '
            . trim($this->zip)
            )
            ;
            $googleUrl .= "&address=$loc";
        }

        // EXECUTE GOOGLE GEOCODER QUERY SEE http://code.google.com/apis/maps/documentation/geocoding/
        if ($gfp = @fopen($googleUrl, 'r'))
        {
            $googleResponse = '';
            while (!feof($gfp)) { $googleResponse .= fgets($gfp); }
            fclose($gfp);
        }
        else
        {
            return FALSE;
        }

        // EXTRACT THE DATA
        $gdata    = new SimpleXMLElement($googleResponse);

        // ON FAILURE
        if ($gdata->status != 'OK') return FALSE;

        // GEOCODE ACCURACY IS A TEXT STRING NOW
        $this->location  = (string)$gdata->result->formatted_address;
        $this->precision = (string)$gdata->result->type;
        $this->latitude  = (string)$gdata->result->geometry->location->lat;
        $this->longitude = (string)$gdata->result->geometry->location->lng;
        return TRUE;
    } // END function geocodeGoogle

} // END class SimpleGeocoder

Open in new window

Ross TurnerManagement Information Support Analyst

Author

Commented:
Hi Ray_Paseur,

Thank you for your help it got me heading in the right direction.....
 
You're a Legend.

Ross :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial