(TeradataのSQL実行環境に関してはこちらをご覧ください)

今回ご紹介するのは、距離計算に利用される関数です。ある座標が2つあり、その2つの距離がどのくらいかを計算します。TeradataのGeospatial関数には3つの関数が用意されています。

• 平面空間を前提: st_distance()

• (完全)球体を前提: st_sphericaldistance()

• 回転楕円体(楕円球体)を前提: st_spheroidaldistance()

単純な数学的距離を計算するのであればst_distance()を、地球上の座標を利用するのであれば、下2つのいずれかを利用しますが、地球は完全な球体(spherical)ではなく、回転楕円体(spheroidal)であるため、後者の方が正確であるとのこと。大きな差はないでしょうけどね。以下マニュアルの101ページあたりに触れられています。

ここで気になるのは、回転楕円体を前提とするとxとyはひっくり返すと計算結果が違う値になるであろうという点です。平面世界や完全な球体であれば、縦横がひっくり返ってもよさそうなものですが、楕円ということはどちらかが長く膨れているわけで、そうなると、例えばpoint(x y)としたときのxは緯度なのか、経度なのか、が気になります。ちなみに緯度と経度は以下のようなものです

• 経度: longitude、日本だと122から153度の範囲、横軸、西から東

• 緯度: latitude、日本だと20から45度の範囲、縦軸、南から北

緯度が先に呼ばれることが多く、Well Known Textの形式だとpoint(x y)のような記述となるため、point(緯度 経度)と思ってしまうのですが、正しくはpoint(経度 緯度)となります。マニュアルのサンプルでもpoint(経度 緯度)と記載しています(例: シカゴ市はpoint(-87.65 41.90)、-87.65が経度、41.90が緯度、上述マニュアルの102ページ)。ネットで検索しても同様に経度(longitude)をx、緯度(latitude)をyとするケースが多いです。一方で慣れ親しんだGoogle Mapでは逆の順番で「記載」されており、若干違和感は感じもするのですが、それに倣います。ノートブックにいくつか触ってみた結果がありますので見てみましょう。データはマガジン内以前の記事にて利用したデータを再利用しています。

関数の使い方と前提

関数の使い方ですが、2つの列を呪文の前とカッコの中の両方に入力します。両方がpointのst_geometry型である必要があります。st_sphericaldistance()とst_spheroidaldistance()の場合、パラメーターを追加しなければ地球を前提とした球体、楕円球体上での距離をメートルで計算、出力します。パラメーターを使えばいろいろな大きさの球体、そしていろいろなひしゃげ方の楕円を前提とした計算もできるようです。

完全他球体と楕円球体の差

完全球体と楕円球体でどのくらい距離が変わってくるのかも試してみました。隅田川の流心を基準にして、221メートル南にある月島の突端とを比較すると、48センチのギャップが生まれます。同じくかすみがうらとは2.57メートル、960キロ離れた長崎ハウステンボスとは1.7キロのギャップが生まれます。近場であれば大差ないですし、全体の距離の大きさに対してのギャップ割合も大きくはないですが、実距離自体は遠距離だとやはり差が大きくなりますね。

ストアドプロシージャの利用

実務で利用する場合によくあるのが、「顧客の最寄り駅を決定する」とか「顧客の最寄り店舗を決定する」といった処理です。例えば1,000万顧客に対して、100店舗の候補がある場合、そのままクロスジョインすると瞬間的には10億件のデータが作成され、距離計算を行い、その中から最も近い駅が決定されることになります。大きめのシステムでも一時的に10億件のデータが作成されるとなると、やはり躊躇します。そんなときには処理量を分割するために、ストアドプロシージャを用います。上記のサンプルでは、左側を店舗として2つ候補があり、この2つからどちらが近いかをqualify句で選ばせています。そして右側に投入する顧客を番号で絞り、番号を1つずつ増加させて1件ずつ順次処理をさせ、その結果をテーブルに格納しています。これなら瞬間的に発生するデータは2件ずつで済みます。1,000万先の顧客がいるのであれば、例えば顧客番号の最後の桁がランダムに0から9に近いのであればその値を取得して10分割できますので、上述のボリュームオーダーに対して、一時的に発生するデータは1億件で済みます。それでも不安なら顧客番号最後の2桁をとって100分割しても構いません。

以上です。

#geospatial #gis #teradata #sql #analytics #st_geometry