Oracle + PHP Cookbook

 

郵便番号データを使用したロケータの構築


著者：Nick Bollweg

ユーザーのデータに関する理解を深め、データの場所を認識する新しい方法 ：「あなたのデータはどこですか？」

関連するダウンロード・リンク
Oracle Database 10g Express Edition
Zend Core for Oracle
US Census ZIP data

2006年10月公開

あなたの所属する企業が、現実の世界に存在する物や人、場所についてのデータをもっているなら、住所のデータももっていることでしょう。 住所エンティティがない、またはほかのエンティティにも住所関連の属性がないデータベースというのはあまり見かけません。 ほとんどの場合、開発者はデータベース内で利用可能な空間条件に基づいてこれらの位置の選択を示す必要がありますが、通常はそれほど困難なことではありません。 メリーランドもニューヨークも問題ないでしょう。 しかし、"Z内でYに一番近いXはどこですか"や、"Yに近いXはいくつありますか"といった質問は、開発者を震え上がらせることでしょう。 大企業で最新のソフトウェアを使用している場合、Oracle Locator（無償のOracle Database 10g Express Editionを含むOracle Database 10gのすべてのエディションで利用可能）やOracle Spatial（Oracle Database 10g Enterprise Editionのオプション）のような追加のコンポーネントを使用してデータベースを強化します。 しかし、新たな支出をかけずにこのような情報だけを望む場合はどうしたらようでしょう。

 

郵便番号データは比較的よく理解され、検証が容易で、おそらく前述の住所エンティティや属性に含まれています。 郵便番号と少量の外部データ、多少の作業、複雑なジオメトリ、またはOracle Locatorの機能を組み合わせて、距離ベースの結果を表示できます。 この方法で得られるデータは近似値にすぎず、ミッション・クリティカルなアプリケーションに使用すべきではありませんが、簡単な店舗検索や視覚化技術としては巧みなやり方です。

 

予想されるトラフィックが比較的低く、公開Webサイトでこれから郵便番号対応アプリケーションを利用可能にする場合、サービス・ベースの地図配布アプリケーションを使用して、JavaScriptで位置を示すことができます。 このレシピでは、Phoogleを使用したGoogle Mapとの統合を考察します。

 

データを追う

 

このテクニックを利用するには、米国の郵便番号と緯度および経度を関連づけるデータが必要です。 多くのプロバイダがこのサービスを提供しており、頻繁に変更される郵便番号に対して定期的に更新がおこなわれます。 このレシピでは、多少古いですが、すぐに利用可能な米国国勢調査局の 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を使用している場合は、空間索引と呼ばれるものも必要です。 この索引を使用するOracle 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のみアクセスが可能で、実際のスキーマ所有者はアクセスできない）は、データベース・ユーザーのHRではなく、ANONYMOUSに与えられました。 結局、この問合せを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
Oracle 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;
}
Oracle Locatorを所有していない、または非常に好奇心が強い方は、次の3つのセクションを読めば、かなりの量の作業がおこなわれることがわかるでしょう。 現実的に、Oracle Locator方式を使用しない理由は、その時点で企業のデータベースをアップグレードできない場合だけです。

 

大円


地球上の距離を明らかにできるジオメトリの一部は、大円と呼ばれる概念です。 いくつかのリソースでは 関連する数学を徹底的に説明しています。 しかし、ここでのソリューションにとって必要なのは、SQL関数を使用する式といくつかのショートカットという概念の大雑把な理解だけです。

大円は球体として同じ円周をもつ球体上に描かれるあらゆる円を表します。 地球には赤道と子午線の2つの有名な大円があります。 大円についてのもう1つの考え方は、球体を切って、2つの同じ切片すなわち半球とすることのできる場所です。 しかし、地球は完全な球体ではなく、わずかに上下が押しつぶされています。 このことが計算を多少複雑にしていますが、優秀な人たちにより解決されています。

 

ユーザーは世界の中心


ここで使用されている数学の概念は、郵便番号ベースのPL/SQL距離関数を実現する John Weegの記事を参考にしたものです。 記事で参照している別のリンクは、残念ながら現在は利用できませんが、式は正常に動作します。 最初のステップはユーザーの求める中心の緯度と経度を調べることで、別の位置はそこから所定の距離内でなければなりません。 さらにこの段階では、この位置に関連して、地球の円周を計算することも可能です。 
select  LATITUDE,
                LONGITUDE,
                3963 - ( 13 * sin( latitude * 0.017453293 ) ) as RADIUS
        from
                ZIP
        where 
                ZIP_CODE = :zip
前述したように地球は押しつぶされた形であり、北極か南極に近づくにつれて地球の円周が減少するという影響があることから、赤道から１マイル離れるごとに特定の値を引く必要があります。 さらに、ほかの位置でも修正の必要があるので、Oracleの三角関数は、通常提供される緯度と経度ではなく、半径で動作します。 この変換はπ/8を掛けることで実行できます。 ここでの小さな最適化は、これらの度数の値を保存して事前に計算しておくことです。

 

円を四角にする


PL/SQLの機能を使用したWeegのテクニックでは、目標を達成するための中間の変数を設定できます。 このソリューションを完全に問合せ駆動として実装する場合、この問合せは少し複雑になります。 ユーザーの位置に関する情報はすでに収集しているので、いくつかの値と文字列をPHP変数として計算し、SQL文に挿入できます。
  • $slatおよび$slon： ユーザーの位置の緯度および経度
  • dlatおよび$dlon： ユーザーの緯度/経度と現在の行の緯度/経度の差を特定する問合せの断片
  • $ep： 前述のπ/8
  • 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の4つの値は、Oracle DatabaseがZIP表内のすべての行で距離の計算をおこなうのを防止するための最適化です。目的の半径マイル数と同じ幅の四角形を2回描写することで、距離の計算を大幅に削減します。

 

最初に


ここまででデータの性質がわかったので、実装について考えます。 この方法は、このプロセスをすぐに処理することのできるOracle Spatialの拡張機能を使用しないので、デスクトップでこれを試してみるのに必要なのは、実行中のOracleデータベースとPHP対応のWebサーバーだけです。 このための実際の時間節約の手段は、Oracle Database XEおよびZend Core for Oracleです。 前述の外部データに加えて、Googleにより利用可能になるサービス・ベースの地図作製を試す場合は、GoogleとPHP Google Maps APIから APIキーを取得する必要があります。このレシピではGoogle Maps APIおよびgeocoder.usデータベースと統合するPHPクラスである Phoogle Mapsを使用します。 Phoogleの例を使用するには、Google Maps API Keyが登録されたのと同じディレクトリにサンプル・アプリケーションが存在する必要があることに注意してください。 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>

名前に場所を追加


地図情報をブラウザ・ベースでインタラクティブに利用することは、2大プロバイダである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]はミネソタ州ミネアポリス地区のフリーランスの開発者です。

ご意見ご感想をお寄せください。