谷歌之间的动态关系映射API v3和MySQL

I am designing my very own first web page. It's a real estate page where some users will add open house information while others can pull the info. The code for the database and the web is very good (after two months...) and inserting and getting data is sweet... Now the Google maps. I am trying to have something like trulia or even the very original housingmaps.com (It seems that they started the mash up, right?). crimereports.com is very nice, too.

我正在设计我自己的第一个网页。这是一个房地产页面，用户可以在其中添加开放房屋信息，而其他人可以提取信息。数据库和web的代码非常好(两个月后…)，插入和获取数据是非常美妙的……现在,谷歌地图。我想要一些像trulia这样的东西，甚至是最原始的housingmaps.com(看起来他们是在搞土豆泥，对吧?)crimereports.com也很不错。

GOAL: pull addresses from database, get long and lat from geocode, insert it back to table, display in google map. Plus: as people pan the map, new info pops into map.

目标:从数据库中提取地址，从geocode中获取长地址和lat，将其插入到表中，在谷歌映射中显示。另外:当人们翻动地图时，新的信息就会出现在地图上。

Here is the code for retrieving addresses, geo code, and add lat and lng back to database.

这是检索地址的代码，geo代码，并将lat和lng添加回数据库。

<?php
//require("phpsqlajax_dbinfo.php");

// Opens a connection to a MySQL server
$username = "abc"; //personal info changed to abc
$password = "abc";
$hostname = "abc"; 
$database = "abc";
$connection = mysqli_connect($hostname, $username, $password);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

//if (!$connection) {
  //die("Not connected : " . mysql_error());
//}

define("MAPS_HOST", "maps.google.com");
define("KEY", "my .............key........code");



// Set the active MySQL database
$db_selected = mysqli_select_db($connection, $database);
if (!$db_selected) {
  die("Can\'t use db : " . mysql_error());
}

// Select all the rows in the markers table
$query = "SELECT * FROM brokerstour.property WHERE 1";
$result = mysqli_query($connection, $query);
if (!$result) {
  die("Invalid query: " . mysql_error());
}

// Initialize delay in geocode speed
$delay = 0;
$base_url = "http://maps.googleapis.com/maps/api/geocode/output?parameters";

// Iterate through the rows, geocoding each address
while ($row = @mysqli_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 property " .
             " 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);
  }
}
?>

Here is what I have changed to the Google Tutorial (original code): 1) updated php: mysqli to avoid errors. Not getting more php errors. 2) changed the url from "http://" . MAPS_HOST . "/maps/geo?output=xml" . "&key=" . KEY; TO "http://maps.googleapis.com/maps/api/geocode/output?parameters" trying to update code to API v3.

下面是我修改的谷歌教程(原始代码):1)更新的php: mysqli以避免错误。没有得到更多的php错误。2)将url从“http://”改为“http://”。MAPS_HOST。“/地图/地理吗?输出= xml”。“关键=”。关键的;“http://maps.googleapis.com/maps/api/geocode/output?参数“试图将代码更新到API v3。

If I use old url, i get error 610. With new url I get:

如果我使用旧的url，我会得到错误610。有了新的url，我得到:

---

( ! ) SCREAM: Error suppression ignored for ( ! ) Warning: simplexml_load_file(): I/O warning : failed to load external entity "maps.googleapis.com/maps/api/geocode/output?parameters&q=4200+Park+Blvd+" in C:.... on line 57 .....

(!)尖叫:错误抑制被忽略为(!)查抄:simplexml_load_file(): I/O警告:未能加载外部实体地图。parameters&q = 4200 +园区+大马路+”在C:....在57 .....行

url not loading

Full disclosure: I am very new and maybe the above doesn't even make sense. However I have been stuck on this problem for a week. (By the way for those new to google maps, the book Beginning Google API 3 is a must. It does not deal with database though, but explains the google maps api very well).

完全透露:我是一个全新的人，也许以上这些都是毫无意义的。然而，我在这个问题上坚持了一个星期。(顺便说一下，在谷歌地图上，谷歌API 3是必须的。虽然它不处理数据库，但是很好地解释了谷歌映射api)。

Could someone please give any a hint, book, tutorial? Any help is a great help. Thank you for your time.

有人能给点提示吗，书，教程?任何帮助都是很大的帮助。谢谢您的时间。

Line 57 is $xml = simplexml_load_file($request_url) or die("url not loading");

第57行是$xml = simplexml_load_file($request_url)或die(“url not loading”);

2 个解决方案

#1

The $base_url = "http://maps.googleapis.com/maps/api/geocode/output?parameters"; You do not specify output.It should be either xml or jason. Also the parameters you pass are address and either sensor = false or sensor = true

美元base_url = " http://maps.googleapis.com/maps/api/geocode/output?parameters ";您不指定输出。应该是xml或jason。您传递的参数是地址，或者是传感器= false，或者是传感器= true

EDIT

编辑

Remove

删除

$geocode_pending = true;
while ($geocode_pending) {

END EDIT

最后编辑

$q = $row["address"];
$base_url = "http://maps.googleapis.com/maps/api/geocode/xml?address=";
$request_url = $base_url.urlencode($q)."&sensor=false";
$xml = simplexml_load_file($request_url) or die("url not loading");
if($xml->status=="OK"){
    // Successful geocode
    $lat = $xml->result->geometry->location->lat;
    $lng = $xml->result->geometry->location->lng; 

EDIT

编辑

    echo $lat.","$lng."<br>";//
    } 
    }

Remove rest of code and make sure this works before incementally adding rest of code

删除代码的其余部分，并确保这些代码可以在代码中添加其他代码之前完成。

#2

This is a partial success. This code accomplishes: a) get addresses from table; b) get lat and lnt from google; c) insert data back into table.

这是部分的成功。此代码完成:a)从表中获取地址;b)从谷歌获取lat和lnt;c)将数据插入表中。

I still need to work on the delay. Besides, I am a 3 month old programmer... and to get the above working took me two long weeks... I left the delay in there just for reference (it was tutorial from Google with changes form other people in the web.)

我还需要加班。另外，我是一个3个月的程序员……为了完成上述工作，我花了两个星期的时间……我把延迟留在那里只是作为参考(它是来自谷歌的教程，由web上的其他人修改)。

I tried to vote up the answer from Mr. Strachan, but I lack reputation inside stack overflow... Could not do it. That fix also works. Thank you for your time.

我试着给斯特拉坎先生的答案投票，但是我在stack overflow里面没有名声……不能这样做。修复同样适用。谢谢您的时间。

<?php

$conn = mysql_connect('localhost', 'rxxt', 'xxxxxxx');
if(!$conn)
 {
   echo "Database Error.";
 }
 else
 {
   echo "Connected.";
   mysql_select_db('YOUR TABLE', $conn);
   $query = "SELECT * from YOUR TABLE";
   $result = mysql_query($query, $conn) or die($query.mysql_error());
    while($row = mysql_fetch_array($result))
    { 

      $address = $row['address'];
      $id = $row['id'];

      $loc_address = str_replace(" ", "+", $address); //to replace spaces by '+' signs
          $location = $loc_address;
      echo $location."<br>";
      $delay = 0;  // this delay is here just to avoid errors - 
          $url = "http://maps.google.com/maps/api/geocode/json?address=".$location."&sensor=false";
          echo $url;
            $output=file_get_contents($url);
            $out= json_decode($output);
            $lat = $out->results[0]->geometry->location->lat;
            $lng = $out->results[0]->geometry->location->lng;
            echo '<br>Lat is '. $lat;
            echo '<br>Long is '. $lng;


       $query = sprintf("UPDATE YOUR TABLE " .
             " 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);

      echo "Successfully inserted.";
    // Below is just for reference - they say we need the delay. The may problem: does not get the status to compare to 200, 620, etc.   
    /*$status = $xml->Response->Status->code;  
    if (strcmp($status, "200") == 0) {
            // Successful geocode
            echo '<br />successfull geocode</strong><br />';
            $geocode_pending = false;
    }
    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); 
    }
 }
mysql_close($conn);
?>