在Oracle数据库XE上构建Google Earth接口

2006 年 8 月发布

我们看待数据的方式形成了我们对世界的看法。在这之前，我们中的大多数人仍会忽略位置，因为我们缺少获取、治理以及显示数据空间组件所需的工具（或使用这些工具的能力）。这意味着，在我们的个人经历中缺失了地理空间这一环节。

而我们所做、所思、所经历的每一件事都是有地点的。

迄今为止，通过可视化引擎连接支持空间的数据库仍是一件十分复杂的事情：例如，将我们的客户（以及整个供给链）置于地图上。现在，这个问题得到了解决。在本文中，您将了解空间数据的概念，探究向现有数据添加空间属性的方法，以及学习如何使用免费的 Google Earth 程序“俯瞰”包含数据的航拍图像。

这些技术中的某些部分有点难于使用，这是因为我们刚开始接触地理空间可视化和分析工具，而且并非专业人士。其中一些工具令人赞叹，例如能够使用 Google Sketch Up 向 Google Earth 添加任意特征的 3D 模型，而且是免费的！但是，有些时候这些工具会缺少一些“常见”功能，例如，支持交叉标签的报表编写器不能处理多级汇总。然而，正如处于起步阶段的所有技术一样，在标准不完善以及某些工具仍然难以上手的情况下，花费时间来学习该技术的领航者将获得最丰厚的回报。

要使用空间工具，需要向数据添加空间属性。空间属性就是具有位置或空间组件的任何信息。有关空间坐标系统的细节数不胜数，包括使用的晶洞（地球外形的模型）、基准点（所选 X-Y-Z 轴的原点位置）以及地球曲面在平面地图上的投影（支持将球体曲面显示在平面地图上）。这个研究课题博大精深、令人着迷，但现在我们可以暂且将其忽略。

我们的目的是利用纬度和经度来表示地球上的某个位置。围绕地球的纬度线就像多层结婚蛋糕，底部那一层（或者说纬度 0）就是赤道。从赤道到北极或南极，纬度共有 90 度。每一纬度大概是 69 英里。赤道以北称为北纬，赤道以南称为南纬。

经度以南北极为轴将地球分割开，就像切开的桔瓣一样。赤道是一种自然地物，而经线则是从人为定义的本初子午线（穿过英格兰格林威治）开始。经线在靠近两极时逐渐靠近，最终汇聚在一起。每一经度在赤道上大约相距 69 英里，在南极或北极则为零距离。

为了简化这些事物的计算机表示，习惯上将赤道以南的纬度和本初子午线以西的经度表示为负数。纬度和经度通常表示为度、分、秒，甚或是十进制度数。十进制度数处理起来更为简便，因此本文将使用该单位。

例如，Oracle 总部位于 500 Oracle Parkway，Redwood Shores，CA 94065，或“大概”在 37.529526，-122.263969（北纬 37.529526 度，西经 122.263969 度）。

这个示例过于精确了！标识一栋建筑物不需要在地理坐标中精确到小数点后 6 位数字。但是，到底需要精确到多少位呢？我们知道一纬度是 69 英里。因此，一度的十分之一是 6.9 英里，一度的百分之一是 .69 英里，以此类推（参见下表）。假如我们只是用一个点来标记该栋建筑物，则精确到小数点后四位足矣。

点击查看大图

接下来，让我们看看空间数据的三种主要形式：点、线和多边形。

点很简单：一条纬线和一条经线就可以标记一个点。线和多边形就是一系列相连的点。线用于线状地物，例如道路与河流，而多边形是由若干条线组成，其中的线首尾相连，最终定义一个区域。送货卡车使用的路线可能是条线，而销售区域可能就是上述的多边形。

现在，我将具体说明如何使用免费的 Oracle 数据库 XE 数据库来尝试存储、治理和分析空间数据。

关于简单性

存储经度和纬度的最简单方法是在数域中进行存储。使用数域存储坐标可以很好地通过点处理简单事物。需要扩充时可使用 Locator（定位器），这是完整 Oracle Spatial 工具（随附在 Oracle 商业产品中）的 XE 子集。通过 Locator，您可以从使用简单的经/纬度转向使用“几何结构”。sdo_geometry 数据类型可以存储点、线、多边形以及更为复杂的几何图形，例如多点、多线和多个多边形。您还可以使用操作符，用于计算几何图形之间距离、查找最近的相邻物体，以及确定两个几何图形是否交叉（“州际公路 70 是否进入科罗拉多州？”）。

假如您的操作不仅仅是将点置于地图上，那么 Locator 十分适用，但对于简单问题，可以使用简单工具。

Oracle 数据库 XE（可以从 Oracle 技术网下载）具有 Windows 和 linux 两种版本。对于 Linux 安装，请下载 RPM，然后进行安装：rpm –ivh oracle-xe-10.2.0.1-1.0.i386.rpm

我在安装中碰到了两个小问题。首先，我没有足够的交换空间。我遵循 Red Hat 说明设置并激活了更多的交换空间，而不是在硬盘上创建一个较大的交换分区。简言之，您需要创建一个用于交换的文件，并将其指定为交换文件，然后将其打开。这组命令如下：dd if=/dev/zero of=/path-to-swap/swapfile bs=1024 count=1200000

mkswap /path-to-swap/swapfile

swapon /path-to-swap/swapfile

您将通过 Web 界面配置和使用（大多数时候）Oracle 数据库 XE。假如在本地计算机上安装了 XE，则将浏览器指向 http://127.0.0.1:8080/apex。假如是在远程服务器上进行的安装，您可能会碰到一个小问题，因为默认情况下 Web 浏览器只为本地客户端提供服务。

您可以使用 Web 界面的 Administration（治理）部分启用对远程客户端的访问。（或者，可以编辑位于 /etc/init.d/oracle-xe 的 XE 配置文件。）但是，这种方法存在两个问题。一个是，它公开数据库治理的方式会引发更多安全问题。另一个更为重要的问题是，为了支持对 Web 界面的远程访问，您需要拥有本地 Web 访问权限。这对于远程无头服务器而言是个挑战。一种解决方法是使用 Linux 和 Macintosh OSX 随附的 Open SSH 程序。Open SSH 包含一个内置的 Socks 4 代理。您可以使用以下命令连接远程服务器：

ssh -D1080 username@yourserver.com

之后，看起来您像是设置了一个到服务器的普通 ssh 连接，但幕后的 SSH 将监听端口 1080，并将所有请求传送至远程服务器。这还意味着，现在所有的 HTTP 通信都将进行加密直至到达您的服务器。在无法保证安全的公共网络上工作时，这是一个非常有用的特性。

最后一步是设置浏览器代理。在 Firefox 中，选择 PReferences->General->Connection。将您的 Socks 主机设置为端口 1080 上的本地主机，选择 Socks v4，确保其针对远程 127.0.0.1 和本地主机为 no proxy for。

这样，就可以使用 Web 界面来执行几乎所有可以通过数据库完成的操作了。因为业务数据（通常）是空间数据，所以来看一个向地址添加纬度和经度的示例。

通过 Web 界面的 SQL 部分创建一个地址表。 create table address (name varchar(128), address1 varchar(128),

address2 varchar(128), city varchar(128), state char(2),

zip char(9), latitude number(7,5), longitude number(8,5))

加载几个地址： insert into address (name, address1, city, state, zip)

values ('Oracle', '500 Oracle Parkway', 'Redwood Shores', 'CA', '94065')

insert into address (name, address1, city, state, zip)

values ('OReilly Media ', '1005 Gravenstein Highway North', 'Sebastopol', 'CA', '95472')

向其他数据添加经度和纬度的过程称为地理编码 (geocoding)。Oracle Spatial 的完整版包括支持地理编码的 SDO_GCDR 程序包。通过 Oracle 数据库 XE，您可以使用 Geocoder.us Web 服务为地址添加地理编码。假如只有两个地址，可能只需查看坐标并手动进行更新即可，但绝对不会只有两个地址！

Geocoder.us 提供了数个 Web 服务接口，用于获取地址并返回坐标。最简单的是逗号分隔 (Comma Separated Values，CSV) 接口。您可以在浏览器中输入一个 URL 并获取坐标。该地址是：http://rpc.geocoder.us/service/csv?address=500 Oracle Parkway,Redwood Shores,CA,94065

返回：37.529526,-122.263969,500 Oracle Pky,Redwood City,CA,94065

还可以从 php 进行调用。该代码将从命令行获取地址，调用 geocoder.us，然后返回坐标：<?PHP

$address = $argv[1];

echo "query address: $address \n";

$url = "http://rpc.geocoder.us/service/csv?address=" . (urlencode($address));

$w = fopen($url,"r");

$result = fgetcsv($w,8000);

fclose($w);

$latitude = $result["0"];

$longitude = $result["1"];

echo "latitude $latitude longitude $longitude\n";

包括地址并从命令行进行调用。（在从命令行调用 PHP 时，可以添加 –q 开关以取消普通 http 内容类型标题）：

php -q ./php_work.php '1600 pennsylvania ave, washington, dc'

这将返回如下结果：query address: 1600 pennsylvania ave, washington, dc

latitude 38.898748 longitude -77.037684

这是与 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 line

oci_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\n";

# as before, parse the SQL statement

$stmtu = oci_parse($conn, $sqlu);

# and execute the statement

oci_execute($stmtu);

}

无需帮手的三维地图

所有努力都是为了向您提供包含空间数据的数据库。现在，您可以清楚地了解客户所在的位置！利用该信息，您可以完成很多事情，例如，分析市场，计划宣传活动、销售路线以及送货路线，但首当其冲的是我们要在地图上看到这些数据。两年以前，在地图上显示数据十分困难，您必须使用昂贵且专业的工具，或使用学习起来异常困难的复杂工具。幸运的是，这一切都成为了过去。

Google Earth 是一个适用于 Windows 和 Mac 的桌面应用程序，其用途不仅限于地图，还提供了一个 3D 世界模型。在城镇数量不断增加的今日，它还包括了多个 3D 建筑物轮廓。您还可以使用 Google Sketch Up 将自己的 3D 模型添加到显示中。

Google Earth 可从 http://earth.google.com 获得。这是一个免费版本，但对于 Google Earth Plus，您每年需支付 20 美元，对于 Google Earth Pro，您每年需支付 400 美元。付费版本增加了诸如全球定位系统 (GPS) 集成之类的功能。（单击此处了解各个版本的不同之处。）网站说明非常简单，请立即下载使用吧！刚开始，您会看到一个从太空视角拍摄的地球视图。然后，可将视图放大来搜索位置、探索世界，就像 Lewis 和 Clark（当今探险军团的成员）一样。

活跃的 Google Earth 社区会发布一些空间信息文件。例如，单击此处阅读并下载有关 Lewis 和 Clark 探险之旅的 Google Earth 文件：29917-lewis_and_clark_eXPedition.kmz。您会注重到，Google Earth 文件的扩展名为 KML（即 Keyhole 标记语言，Google Earth 在以前称为 Keyhole）或 KMZ（通过 gzip 压缩的 Keyhole 标记语言）。KMZ 文件可通过 WinZip 或 Stuffit Expander 进行解压缩。

我喜欢我的 GUI，除非要尝试进行脚本编写，因此从 OSX 终端命令行，您可以输入以下命令：gunzip -S ".kmz" 29917-lewis_and_clark_expedition.kmz

打开该文件后，您将发现它只不过是一个 xml 文件。好吧，或许并不是十分简单，但易于阅读。请深入阅读 Google Earth KML 文档！

例如，以下是描述 Camp Disappointment 的地标。<Placemark>

<description><![CDATA[

Click to read entry</a>]]></description>

<name>17: Camp Disappointment</name>

</LookAt>

<styleUrl>root://styles#khStyle929</styleUrl>

<Point>

</Point>

</Placemark>

当您调出 Lewis and Clark 文件后，您将看到 Google Earth 是如何呈现该 Camp Disappointment 描述的。

<name> 元素以点元素的 <coordinates> 元素中指定的 X, Y, Z 坐标形式显示在地图上（经度 = X，纬度 = Y，海拔 = Z，在本例中为 0）。单击该点时，会弹出一个描述球，其中显示了名称和描述元素的内容，以及到此处去或从此点出发的选项。

请非凡注重，描述可以包含 URL。因此，假如该地标描述了您的一个客户，那么就可以在调出中包含有关该客户的其他属性，然后在您的 CRM 系统中嵌入一个指向客户页面的链接。

<styleURL> 元素包含一个指向标记该位置的“图钉”URL 的链接。在本例中，该样式包含在本地文件系统上。样式描述还可以包含在 KML 文档内或外部 URL 中。

最后，看一下 <LookAt> 元素。双击地标，您会到达 <LookAt> 元素中指定的经度和纬度，并看到由 <heading>、<tilt> 以及 <range> 元素描述的视图点。这答应您在指定视图点的地方创建地标。例如，以下是一个完整的 KML 文件，其内容为“前往旧金山的 Crissy Field 观赏金门大桥”：<?xml version="1.0" encoding="UTF-8"?>

<name>crissy field</name>

</LookAt>

<styleUrl>root://styles#default</styleUrl>

<Point>

</Point>

</Placemark>

</kml>

为了简单起见，您可以除去其中一些属性。以下是这两个明确地点的最简单、有用的 KML 描述：

<?xml version="1.0" encoding="UTF-8"?>

<name>First Place</name>

<Point>

</Point>

</Placemark>

<name>Random Place</name>

<Point>

</Point>

</Placemark>

</Folder>

</kml>

以下是抓取地理编码地址并生成 KML 文件的 PHP 程序：<?PHP

$conn=oci_connect('username','password', "//127.0.0.1/XE");

$sql = "SELECT name, address1, city, state, zip, latitude, longitude from address a";

$stmt = oci_parse($conn, $sql);

oci_execute($stmt);

print '<?xml version="1.0" encoding="UTF-8"?>' . "\n";

print '<kml xmlns="http://earth.google.com/kml/2.0">' . "\n";

print '<Folder>' . "\n";

while ( $row = oci_fetch_assoc($stmt) ) {

$address = $row["ADDRESS1"] . "," . $row["CITY"] . "," . $row["STATE"] . " " . $row["ZIP"];

$name = $row["NAME"];

$latitude = $row["LATITUDE"];

$longitude = $row["LONGITUDE"];

print "<Placemark>\n";

print " <name>$name</name>\n";

print " <description>$address</description>\n";

print " <Point>\n";

print " <coordinates>$longitude,$latitude,0</coordinates>\n";

print " </Point>\n";

print "</Placemark>\n";

}

print '</Folder>' . "\n";

print '</kml>' . "\n";

这是 caveman coder 版本，使用了专门用于显示所有细节的打印语句。在实际操作中，您可能需要使用 XML 库。请参阅以下位置的 Pear/XML/sql2xml 类说明文档：http://php.chregu.tv/sql2xml/。此外，通过使用动态 URL 参数和变量，我们可以轻松地在该查询（或其他查询）中添加所需内容，这样我们的空间分析用途将更为广泛。

保持关注

现在，您已经能够创建地址数据库，通过地理编码向该数据添加经度和纬度，然后将数据导出以便可以在 Google Earth 上查看数据（假设您仍未阅读 Lewis 与 Clark 以及探险之旅的定期发布信息，并且沉迷于愉快的探险中）。

Rich Gibson (http://mappinghacks.com) 是一位地图绘制、地理空间和地理编码方面的顾问，与人合著了 Mapping Hacks:Tips & Tools for Electronic Cartography (O’Reilly, 2005) 和 Google Maps Hacks (O'Reilly, 2006)。