|
使用邮编数据构建定位器
作者：Nick Bollweg
帮助您的用户用新方式了解您的数据，然后回答问题：“您是否知道数据在哪里？”
2006 年 10 月发布
如果您的机构具有和真实世界中存在的对象、人员以及地点有关的任何数据，您可能需要给它们一个地址。几乎所有数据库都需要一个地址实体，或者在其他某个实体中具有一些与地址有关的属性。大多数时间，开发人员需要根据数据库中准备好的一些空间标准来显示对这些位置的选择，这通常并不麻烦。马里兰州？纽约？没问题！然而，像“Z 中哪个 X 距离 Y 最近？”或者“Y 附近有多少个 X？”这样的其他问题会使开发人员不寒而栗。如果您是一家使用最新软件的大企业，您可以使用其他组件来增强您的数据库，如 Oracle Locator（Oracle 数据库 10g 的所有版本均有提供，包括免费的 Oracle 数据库 10g 快捷版）或 Oracle Spatial（企业版的一个选件）。但如果只需要该信息但不想购买它，该怎么办？
邮编数据相对易于理解、易于验证，因此，那些地址实体和属性可能会包括邮编数据。邮编数据加上少量的外部数据、一些努力以及一些技巧性的几何知识或 Oracle Locator 的功能，您就可以为任何具有邮编的地方显示基于距离的结果了。这种方法顶多是凑合，不应当用于关键任务的应用程序，但对于快速存储定位器或虚拟化技术，它应该足够了。
如果您期望具有相对低的通信量，而且使您的支持邮编的应用程序在公共网站上可用，您可以使用基于服务的地图传递应用程序以声名显赫的 JavaScript 来显示您的位置。在本手册中，我们将探究如何通过 Phoogle 与 Google Maps 相集成。
跟踪数据
要利用这项技术，您需要一些数据以便将美国邮政服务的邮编与它们的纬度和经度相关联。许多提供商都提供该服务，他们通常对数以万计频繁更改的邮编进行定期更新。对于本手册，我使用的数据有些陈旧，但来自于美国人口普查局的 1999 年邮编数据。以下示例将使用表格 ZIP，其定义如下：
CREATE TABLE ZIP
( ZIP_CODE VARCHAR2(5),
CITY VARCHAR2(64),
STATE VARCHAR2(2),
LATITUDE NUMBER(10, 7),
LONGITUDE NUMBER(10, 7),
ZIP_GEO_LOCATION SDO_GEOMETRY, --only use if you have Locator
PRIMARY KEY ( ZIP_CODE ) VALIDATE );
您还需要一些位置；只要定义了邮编，任何类型的数据都可以。该示例使用一个名为 LOCATION 的表，其定义如下：
CREATE TABLE LOCATION
( LOCATION_ID NUMBER,
ADDRESS VARCHAR2(128),
CITY VARCHAR2(32),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(5),
LOCATION_GEO_LOCATION SDO_GEOMETRY, --only use if you have Locator
PRIMARY KEY ( LOCATION_ID ) VALIDATE );
如果使用 Oracle Locator，您还需要空间索引。与使用下述大圆近似值进行的查询相比，使用这些索引的 Locator 查询可读性更好、性能更佳。要设置这些查询，您需要在 USER_SDO_GEOM_METADATA 中创建一些有关您要创建的索引的元数据，然后创建索引本身。
INSERT INTO USER_SDO_GEOM_METADATA --A view based on MDSYS.SDO_GEOM_METADATA_TABLE
(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
('LOCATION', 'LOCATION_GEO_LOCATION',
SDO_DIM_ARRAY
(SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
8307);
INSERT INTO USER_SDO_GEOM_METADATA --A view based on MDSYS.SDO_GEOM_METADATA_TABLE
(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
('ZIP', 'ZIP_GEO_LOCATION',
SDO_DIM_ARRAY
(SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
8307);
在此处遇到的一个问题就是触发器中有一个明显的错误，即在插入新的空间查询时会进行一些日常管理工作：新索引的所有权（和 MDSYS.SDO_GEOM_METADATA_TABLE 中记录的一样，只能由 DBA 访问，不能由实际的模式所有者访问）归 ANONYMOUS 所有，而不是数据库用户 HR。我最后必须以 DBA 身份运行这个查询，才能保证索引创建正常进行：
UPDATE
MDSYS.SDO_GEOM_METADATA_TABLE
SET
SDO_OWNER = 'HR'
WHERE
SDO_TABLE_NAME IN ('ZIP', 'LOCATION')
如果根据对 SDO_GEOM_METADATA_TABLE 的检查一切都合格，您就可以插入空间索引了。
CREATE INDEX
ZIP_SIDX
ON
ZIP(ZIP_GEO_LOCATION)
INDEXTYPE IS
MDSYS.SPATIAL_INDEX;
CREATE INDEX
LOCATION_SIDX
ON
LOCATION(LOCATION_GEO_LOCATION)
INDEXTYPE IS
MDSYS.SPATIAL_INDEX;
如果您没有 Oracle Locator，一些索引对于高效操作很重要：以下是我定义的一些索引：
CREATE INDEX IDX_ZIP_LAT ON ZIP ( LATITUDE );
CREATE INDEX IDX_ZIP_LAT_D ON ZIP ( LATITUDE DESC );
CREATE INDEX IDX_ZIP_LON ON ZIP ( LONGITUDE );
CREATE INDEX IDX_ZIP_LON_D ON ZIP ( LONGITUDE DESC);
CREATE INDEX IDX_LOCATION_ZIP ON LOCATION ( ZIP_CODE );
认识邻居
Oracle Locator 提供一些强大的查询机制，使得对空间数据的查询更合乎逻辑。对于接下来几部分中描述的相对复杂的查询，下面使用 sdo_nn 的“最近的邻居”查询将胜任，并且将产生明显更佳的性能：
SELECT
ADDRESS,
CITY,
STATE,
l.ZIP_CODE,
SDO_NN_DISTANCE (1) DISTANCE
FROM
LOCATION l
WHERE
'TRUE' = SDO_NN(
l.LOCATION_GEO_LOCATION,
(SELECT ZIP_LOCATION FROM ZIP WHERE ZIP_CODE = :zip ),
'sdo_num_res=999 unit=mile')
AND
SDO_NN_DISTANCE (1) < :miles
ORDER BY
DISTANCE
与 Locator 方法对应的包装方法同样很简洁：
public function getLocationsWithin( $zip, $miles ){
$q = << query from above >>;
$bind = array("miles" => $miles * 1, "zip" => $zip);
$results = array();
$this->q( $results, $q, $bind );
$this->d( $results );
return $results;
}
如果您因为没有 Locator 或者因为非常好奇而阅读接下来的三部分，您将看到此处已为您做了相当多的工作。不使用 Locator 方法的唯一原因其实就是您的机构此时无法升级数据库。
大圆
几何学中让我们能够找到球体上距离的概念被称作大圆。一些资源彻底解释了所涉及的数学知识。尽管如此，对我们的解决方案而言，我们只需要大致了解这个概念：一个使用 SQL 函数和几个快捷方式的公式。
大圆 (Great Circle) 描述了在球体上绘制的与球体具有相同周长的任何圆圈。地球有两个著名的大圆：赤道和本初子午线。还可以将大圆看作这样一个地方：您可以通过它将一个球体切割成相等的两份（即两个半球）。然而遗憾的是，地球并不是球形的……它的顶部和底部有点儿扁。这会使我们的计算变得有些复杂，但幸运的是，这个问题已经由更聪明的人解决了。
用户是世界的中心
本文使用的数学概念来自于 John Weeg 的文章，他实现了一个基于邮编的 PL\SQL 距离函数。他引用了另一个链接（遗憾的是，该链接不再可用），但他的公式依旧有用。第一步要查找用户所期望的中心的纬度和经度，其他位置必须在该中心周围给定距离内。在这个阶段，我们还可以相对于该位置计算地球的周长：
select LATITUDE,
LONGITUDE,
3963 - ( 13 * sin( latitude * 0.017453293 ) ) as RADIUS
from
ZIP
where
ZIP_CODE = :zip
上面的确切值是地球的半径 (3963) 和 π/8。上面提到了地球是扁圆的，因此当您接近北极和南极时，地球周长会缩小，所以每远离赤道一英里就必须减去一定数量。而且，因为您必须在其他地方进行更正，所以 Oracle 的三角函数处理的是弧度，而不是纬度和经度通常提供的角度：乘以 π/8 即可进行这种转换。此处一个小的优化方法是存储并提前计算这些角度值。
矩形化圆
借助于 PL/SQL 的函数逼近的便利，Weeg 的方法可以设置中间变量来实现他的目标。由于您希望将该解决方案实施为纯粹由查询驱动的，因此该查询可能有些复杂。您已经收集了有关该用户位置的信息，因此可以将一些值和字符串作为 PHP 变量进行计算并插入到 SQL 语句中：
- $slat 和 $slon：用户位置的纬度和经度
- dlat 和 $dlon：一个查询片段，它确定了当前行的纬度/经度与用户的纬度/经度之间的差异
- $ep：前面讨论的 π 的八分之一
- rad：用户所在位置地球的半径
如下所示：
SELECT
address,
city,
state,
l.zip_code,
distance
FROM
location l,
(SELECT
$rad * 2 * ASIN(
LEAST(
1,
SQRT(
POWER( SIN( $dlat / 2 ), 2 ) +
(
COS( $slat ) *
COS( latitude * $ep ) *
POWER( SIN( $dlon / 2 ), 2 )
)
)
)
)
AS distance,
zip_code
FROM
zip
WHERE
latitude BETWEEN $minlat AND $maxlat AND
longitude BETWEEN $minlon AND $maxlon) d
WHERE
l.zip_code = d.zip_code
AND distance <= :miles
ORDER BY
distance ASC
$minlat、$maxlat、$minlon 和 $maxlon 这四个值是最优化参数，它们帮助 Oracle 避免了对 ZIP 表中的每行进行距离计算；通过描述一个矩形（宽度为所希望的英里半径）两次，这应该会显著减少距离计算。
快速入门
既然知道了数据的性质，那么您可以开始考虑实施了。由于该方法不使用 Oracle Spatial 扩展（它可以立刻处理该过程），因此您需要在桌面上试验一个正常运行的 Oracle 数据库和一个支持 PHP 的 Web 服务器。此时的实时保护程序是 Oracle 数据库快捷版和 Zend Core for Oracle。除了前面提到的外部数据，如果您希望尝试 Google 提供的基于服务的映射，您需要从 Google 和 PHP Google Maps API 获取一个 API 密钥；本手册使用 Phoogle Maps，它是一个与 Google Maps API 和 geocoder.us 数据库相集成的 PHP 类。注意，要使用 Phoogle 示例，您的示例应用程序应该位于注册 Google Maps API 密钥的目录中。没有 Phoogle 的页面没有此限制。
包装
我选择使用一个包装类来封装收集所需信息所必需的所有计算。下面就是一些重要的方法：
public function getLocationsWithin( $zip, $miles ){
$this->startLocation = $this->getStartLocation( $zip, $miles );
$ep = $this->eighth_pi;
$slon = $start['LONGITUDE'][0] * $ep;
$slat = $start['LATITUDE'][0] * $ep;
$rad = $start['RADIUS'][0];
$dlon = "abs( ( longitude * $ep ) - $slon )";
$dlat = "abs( ( latitude * $ep ) - $slat )";
$maxd = ( $miles / $rad * 360 );
$maxlat = $start['LATITUDE'][0] + $maxd;
$minlat = $start['LATITUDE'][0] - $maxd;
$maxlon = $start['LONGITUDE'][0] + $maxd;
$minlon = $start['LONGITUDE'][0] - $maxd;
$q = << long query from above >>;
$bind = array("miles" => $miles * 1);
$results = array();
$this->q( $results, $q, $bind );
$this->d( $results );
return $results;
}
上述方法（由输出页面调用）执行文本处理，从而为您提供一个查询，以便返回一个包括距离的位置列表。然后，该方法查询数据库并返回结果。
private function getStartLocation($zip){
$ep = $this->eighth_pi;
$q = << short query from above >>;
$results = array();
$bind = array( "zip"=> trim( $zip ) );
$this->q( $results, $q, $bind );
return $results;
}
查找用户的位置后，该方法将返回与该位置有关的有用信息：纬度、经度以及更正的地球半径。
在同一页面上
完成了从数据库中获取位置的复杂工作后，将它们放在页面上应该是轻而易举的事。在该实施中，我选择了将内容作为一个表格显示，事实上，它就是表格数据。下面是一个非常简单的实施，肯定会从某些 CSS 美化中受益：
define("DBUSER", "HR");
define("DBPWD", "HR");
define("DBXN", "//localhost/testdb");
$zip = @$_REQUEST['zip'];
$miles = @$_REQUEST['miles'];
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"; ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>ZIP Code-based locator</title>
</head>
<body>
<div>
<form action="index.php" method="GET">
Find locations within <input name="miles" value="<?php echo $miles ?>" size="3" maxlength="3" miles of ZIP
<input name="zip" maxlength="5" size="5" value="<?php echo $zip ?>"
<input type="submit" name="go" value="go"
</form>
<?php
if( $zip ){
// open a connection.
if( $conn = oci_connect( DBUSER, DBPWD, DBXN ) ) {
include_once( "Locator.class.php" );
$loc = new Locator( $conn );
//$loc->setDebug( true );
$locations = $loc->getLocationsWithin( $zip, $miles );
oci_close($conn);
echo "<h2>".count( current( $locations ) )."Locations Found</h2>";
?><table><tbody><?php
foreach( array_keys( $locations ) as $col) {
?><th><?php
echo $col;
?></th><?php
}
foreach( current( $locations ) as $row => $value) {
?><tr><?php
foreach( array_keys( $locations ) as $col) {
?><td><?php
echo $locations[$col][$row];
?></td><?php
}
?></tr><?php
}
?></table></tbody><?php
} else {
?>Cannot connect to database.<?php
}
}
?>
</div>
</body>
</html>
将地点放到名称上
Google 和 Yahoo! 这两家最大的提供商使得相对新的基于高度交互性浏览器的地图更为有趣，他们提供的 API 允许开发人员在自己的应用程序中使用这些地图。在他们发布的日子里，包装类可用。我们在此将讨论的 Phoogle 类使得简单的地图变得非常简单，除了自定义（如果需要的话）外，几乎不会增加开发周期的时间。为什么不使用一个类似的 API 来解决本手册中的整个问题地址呢？要为远程服务获得邮编对于我们本地数据库中纬度/经度的信息优势，所有位置数据都需要可以提供给服务；这样，我们就可以维护外部用户访问我们信息的方式。
下面是一个有关如何在上面的文件中添加 Phoogle 使用的示例：在生成 HTML 头之前，您需要先实例化 Phoogle：
require_once 'phoogle.php';
$map = new PhoogleMap();
$map->setAPIKey( "< your key here >" );
应更改头标记，使其包括对 Phoogle 对象的调用：
<head>
<title>ZIP Code-based locator</title>
<? $map->printGoogleJS(); ?>
</head>
在检索完位置之后，但在生成表格视图之前，您需要添加以下代码来添加您的地址，居于地图中心，然后将其绘制在页面上。
foreach( $locations['ADDRESS'] as $row => $address) {
$map->addAddress("$address,
{$locations['CITY']},
{$locations['STATE']}
{$locations['ZIP']}");
}
$map->centerMap( $loc->startLocation['LATITUDE'][0],
$loc->startLocation['LONGITUDE'][0] ) ;
$map->showMap();
这样就可以了。这将在客户端页面上绘制一个小型地图，几乎不会增加额外的处理开销。而且，开发人员可以调整地图的大小，切换控件/视图，并且可以设置地图的缩放特征。而且，还可以进行进一步的自定义，包括自定义图像、事件和文本。
结论
基于邮编的定位器可以向贵机构的页面添加一些非常有用的信息，无需太多精力和开销，只产生上万条记录。另一种方法是订阅基于 Web 服务的地理编码服务，该服务不会受任何订阅数据都可能具有的数据老化问题的困扰。然而对于短期项目，该方法的优势是独立，除了您希望的人之外的任何人都不需要共享您的内容：您的用户！
使用第三方映射服务是对信息进行一些真实世界虚拟化的快捷方法，但可能应该被看作一个跳板：如果您的用户的确喜欢这种内容，您可能需要研究更真实的解决方案。如果您可以使用 Oracle Locator，您的工作将大大减少且结果会有所改进。使用这些方法快乐地工作吧
Nick Bollweg [nick.bollweg@gmail.com] 是明尼苏达州明尼阿波利斯市的自由开发人员。
将您的意见发送给我们
|
文章
Printer View