在 Oracle XE构建 Google Earth 接口

核心提示： 这是与 Geocoder.us 连接最简单的情况，下一步就是从数据库获取地址，在 Oracle XE构建 Google Earth 接口(6)，然后通过 geocoder.us 返回的经度和纬度更新数据库，首先，以下代码将读取我们的地址表，对每个地址进行地理编码，需要结合使用 PHP 与

这是与 Geocoder.us 连接最简单的情况。

下一步就是从数据库获取地址，然后通过 geocoder.us 返回的经度和纬度更新数据库。

首先，需要结合使用 PHP 与 Oracle，请参阅这些说明。我按照这些说明使用的“gotcha”是不在默认位置的 apxs 副本，因此在配置时，我将 --with-apxs2=/usr/local/apache/bin/apxs 替换为 --with-apxs2=/usr/sbin/apxs。

以下代码将读取我们的地址表，对每个地址进行地理编码，然后用经度和纬度更新该表。

<?PHP
# create a connection to the database instance on localhost. If you
# have not done anything 'clever' the username 'system' will work
# with the password that you defined at installation
$conn=oci_connect('username','password', "//127.0.0.1/XE");
# Query our address table
$sql = "SELECT name, address1, city, state, zip from address";
# oci_parse is part of the Oracle PHP library to parse the SQL statement
$stmt = oci_parse($conn, $sql);
# oci_execute not surprisingly executes the statement we parsed in the previous lineoci_execute($stmt);
# This loads the associative array $row with the values of each row in our
# database in turn
while ( $row = oci_fetch_assoc($stmt) ) {
# print_r dumps a variable, including all of the keys for an associative array
print_r($row);
# assemble the query variable for our call to geocoder.us
$address = $row["ADDRESS1"] . "," . $row["CITY"] . "," . $row["STATE"] . " " . $row["ZIP"];
# pull the name out of the associative array
$name = $row["NAME"];
# the url to the free service of geocoder.us to return the data in CSV format
$url = "http://rpc.geocoder.us/service/csv?address=" . (urlencode($address));
# open the url
$w = fopen($url,"r");
#parse the CSV returned from the page into the array $result
$result = fgetcsv($w,8000);
fclose($w);
$latitude = $result["0"];
$longitude = $result["1"];
# query to update the address table with the lat/long we got from geocoder.us
# granted it is poor database design to have such an uncertain key as 'name'
# be our primary key…I'll leave it as an exercise to the reader to implement
# this code in a way that doesn't make DBA's cry.
$sqlu = "update address set =$latitude, =$longitude where NAME='$name'";
echo "sqlu $sqlu
";
# as before, parse the SQL statement
$stmtu = oci_parse($conn, $sqlu);
# and execute the statement
oci_execute($stmtu);
}
?>