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

Source: Internet
Author: User

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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.