postgresqlを使った位置情報の計算について その２

 PostgreSQL Advent Calendar 201118日目を担当します@soudai1025です。
先日はスーパーエンジニア石井達夫さん の鋭意開発中！pgpool-IIのオンメモリクエリキャッシュ機能でした。
そんなわけで本日の内容ですが実ははslonyとPostgreSQL9.0から実装されたレプリケーションの話をしようかなと思ったのですが　第 1 回 Slony-I を使用したシステム構築『Slony-Iを動かしてみよう』などがとても素晴らしい資料が豊富なので断念しました。
PostgreＳＱＬは日本語ドキュメントが豊富なのがいいところですよね。
なので昔にちょっとエントリーをかいたことがあるpostgresqlを使った位置情報の計算についての実践的な内容を記載したいと思います。


まずおさらいですが位置情報をつかった距離の計算はSQLで

SELECT　sqrt(power((対象緯度 - 自分の緯度) * 111, 2) + power((対象経度 - 自分の経度) * 91, 2)) AS distance

とすることで計算できます。
そして今いる緯度経度はJavaScriptとjQueryを使って以下のとおりで取ってこれます。

$(document).ready(function() {
    var latlng = getUrlVars();
    if (latlng["lat"] == undefined || latlng["lon"] == undefined) {
        navigator.geolocation.getCurrentPosition(success, error);
    } else {
        if (latlng["lat"] != undefined) {
            clat = latlng["lat"];
        }
        if (latlng["lng"] != undefined) {
            clng = latlng["lon"];
        }
        location.href = 'map.php?lat=' + clat + '&lon=' + clng + '&range=0.5';
    }
    function success(position) {
        var clat = position.coords.latitude;
        var clng = position.coords.longitude;
        location.href = 'map.php?lat=' + clat + '&lon=' + clng + '&range=0.5';
    }
    function error(msg) {
        var clat = 34.4923;
        var clng = 133.3613;
        location.href = 'map.php?lat=' + clat + '&lon=' + clng + '&range=0.5';
    }
});

この例では緯度経度を取得した後にmap.phpに投げてます。
parameterのrangeは検索範囲の距離をキロメートルで指定している形です。
これをphp側で取得しSQLとして使用するとこんな形になります。

SELECT 
    sqrt(power((hoge.lat - 33.333333) * 111, 2) + power((hoge.lon - 133.333333) * 91, 2)) AS distance
FROM hoge
WHERE 
    circle(point(hoge.lon * 91.0, hoge.lat * 111.0), 0.5) @ circle(point(133.333333 * 91.0, 33.333333 * 111.0), 0.5)

ここまで出来るとSQLで取得したデータを連想配列にし、phpでJSONに変換して すれば比較対象の緯度経度、自分の緯度経度、距離でリストからGoogleMAPにピンを挿し込むことも可能です。
さてここまで来るとかなり実践的に業務で使えるお話になりますね。
でも今日はここまで！
続きはこちらで発表したいと思います。

オープンセミナー広島

ここではデモも含めて実践的なコーディングとWEBアプリをスマフォアプリに変換するところまでしますのでご興味がある人は是非お越しください。
あれ？よく見ると昨日のアドベントカレンダー担当の石井さんも発表されるじゃないですか！
というわけで皆さん奮ってご参加ください♪


というわけでＯＲマッパーじゃこんなこと出来ねぇだろ！ってこと位置情報の計算の話でした。
それでは明日はdai_yamashitaさんです。
引き続き PostgreSQL Advent Calendar 2011をお楽しみください。