Use PHP/MySQL to resolve the address to latitude and longitude in google map

To store the address resolution information associated with the address, we need to design an appropriate data architecture. In this example, we want to display restaurants or bars on map mashups. For such sites, a typical database table should contain the following necessary fields:

• Unique address id
• Location address in text string format
• The lat and lng values at the address resolved locations

In addition, we hope that the provided address database can be used by actual users. Therefore, we need to add a human-readable name for the location for unique identification, and add the type attribute to differentiate between restaurants and bars (only for fun ).

• Place Name in text string format
• Type of the venue (in this example, it is a bar or restaurant)

When creating an address table, note that the lat and lng values in the Google Maps API only need to be accurate to 6 digits to uniquely identify a location. To reduce storage space usage, we specify the lat and lng fields as FLOAT () numbers. The storage accuracy of floating point numbers of this size is 6 digits after the decimal point and a maximum of 4 digits before the decimal point, such as-123.456789 degrees.

You can use the SQL statement shown below to create an address table:

CREATE TABLE `markers` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL ,
  `type` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;

Note that the following sample code uses a virtual KEY constant. You must replace this KEY with your own KEY. Otherwise, a 610 Status Code is returned for all your requests.

The sample code for address parsing using PHP 5 and XML output is as follows:

<?php
require("phpsqlgeocode_dbinfo.php");
define("MAPS_HOST", "maps.google.com");
define("KEY", "abcdefg");
// 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 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" . "&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);
      $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 "Address " . $address . " failed to geocoded. ";
       echo "Received status " . $status . "\n";
     }
     usleep($delay);
   }
 }
 ?>