現在地から半径○メートル以内の○○を抽出するSQL

システム部の鈴木です。

ポケモンGOの日本リリースからはや2週間、皆様は引き続き楽しまれてますでしょうか?

自分は既に飽き出しておりギャラドスへの進化を見る事無く、
わざわざ買った携帯バッテリーを使う事も無く引退が危ぶまれております。

さて、ポケモンGOでまた脚光を浴びた「位置ゲー」ですが、
ポケモンGOの場合は現在地点にプレーヤーのキャラがいて
周囲の○メートルくらいにあるポケストップではアイテムがもらえたりしますよね?

こういった「現在地から半径○メートル以内にある○○を探す」系の事をやるときの
データをどうやって探すのか?ってのをやってみたいと思います。

やりたいこと

map0

弊社のあるアークヒルズフロントタワーを中心としたとき、
色々ある座標データまでの直線距離を計算し、それが○メートルに収まるかどうか?
ということがやりたいことです。

そして今回は、様々な座標が登録されたMySQLのDBから一気に該当する座標を取りたいと思います。

データベース

今回は例としてこのようなテーブルを作成しました。

<br />
CREATE TABLE IF NOT EXISTS `test` (<br />
  `id` int(11) NOT NULL AUTO_INCREMENT,<br />
  `place` varchar(255) NOT NULL DEFAULT &#8221;,<br />
  `lat` double NOT NULL DEFAULT &#8216;0&#8217;,<br />
  `lng` double NOT NULL DEFAULT &#8216;0&#8217;,<br />
  PRIMARY KEY (`id`),<br />
  KEY `lat` (`lat`,`lng`)<br />
) ENGINE=InnoDB;<br />

<br />
INSERT INTO `test` (`id`, `place`, `lat`, `lng`) VALUES(1, &#8216;東京駅&#8217;, 35.68121, 139.765751);<br />
INSERT INTO `test` (`id`, `place`, `lat`, `lng`) VALUES(2, &#8216;品川駅&#8217;, 35.62857, 139.738809);<br />
INSERT INTO `test` (`id`, `place`, `lat`, `lng`) VALUES(3, &#8216;横浜駅&#8217;, 35.465365, 139.62209);<br />
INSERT INTO `test` (`id`, `place`, `lat`, `lng`) VALUES(4, &#8216;二子玉川駅&#8217;, 35.611237, 139.626182);&lt;/pre&gt;<br />
&lt;pre&gt;<br />

以下のような感じのテーブルから、弊社から半径10km以内にある駅を探してみたいと思います。

<br />
+&#8212;-+&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8212;+<br />
| id | place      | lat       | lng        |<br />
+&#8212;-+&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8212;+<br />
|  1 | 東京駅     |  35.68121 | 139.765751 |<br />
|  2 | 品川駅     |  35.62857 | 139.738809 |<br />
|  3 | 横浜駅     | 35.465365 |  139.62209 |<br />
|  4 | 二子玉川駅 | 35.611237 | 139.626182 |<br />
+&#8212;-+&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8212;+<br />

ではこの中から半径10kmに収まる座標を取りだすにはどうすればよいでしょうか?

中学で習ったアレを使います

2地点間の距離を出すには3平方の定理を使いましょう

map02

3平方の定理は aの二乗+bの二乗 = cの二乗 というものです。
この公式は文系出身の自分でも中学の時に聞いた覚えがあります。

現在地であるアークヒルズの座標を lat = x1, lng = y1 (35.668185, 139.739487 )
そして目標となるATTビルの座標を lat = x2, lng = y2

とすると、緯度の差が x1 – x2 = a
経度の差が y1 – y2 = b
ということです。

上の図の例だと、例えば赤い線の半径に収まる座標がどれかを探したいわけですが
要は斜辺の長さがその半径の長さに収まっているかどうかが分かればよいという事ですね。

さらに地球の丸みを考慮して正確に距離を出すためには
弧度法のラジアン(弧度)に変換し、3平方の定理に当てはめればよさそうです。
そして地球の一週の距離は約 6378km らしいので、それを使えばこんな感じで出来るようです。

 

 

<br />
SELECT id, place, lng, lat, (6378 * acos(cos(radians(35.668185)) * cos(radians(lat)) * cos(radians(lng) &#8211; radians(139.739487)) + sin(radians(35.668185)) * sin(radians(lat)))) AS distance<br />
FROM test HAVING distance &lt; 10 ORDER BY distance<br />

このSQLを実行した結果がこちらです。

<br />
+&#8212;-+&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-+<br />
| id | place  | lng        | lat      | distance          |<br />
+&#8212;-+&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-+<br />
|  1 | 東京駅 | 139.765751 | 35.68121 | 2.782586467919596 |<br />
|  2 | 品川駅 | 139.738809 | 35.62857 | 4.410253359933821 |<br />
+&#8212;-+&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-+<br />

見事に近い順で絞り込むことに成功しました。
直線距離なので実際の道路で進んだ距離とは異なりますが、便利に使えそうですね。

ご存知でしたか?Google Map APIの使用にAPI Keyが必須になりました

システム部の鈴木です。

もはやwebサイト制作だけではなく日々の生活に無くてはならなくなったGoogle Map
世界中の人が無料で使えて、これを活用したサービスだって無料で作れる。
当たり前になりすぎてしまいこの恩恵の価値を忘れてしまうくらいでしたが
ここにきて少し動きがあった事、ご存知でしたか?

Google Map API

これを使うと、自分のサイトの中にこんな感じで、沢山の好きなアイコンを並べたり
様々なカスタマイズを加えた独自のマップを作ることができます。

現在のAPIはVer.3 となり、Ver.2 までは煩わしかったAPI Keyが不要になった、ということで制作者にとっては非常にありがたいアップデートになりました。

2016-07-01_21.47.20

ところが、それは突然の出来事でした・・・

Mapが表示されない!?

いつも通りに作ったはずが全く表示されない?
今まで動いていたサイトのコードを流用しているんだから間違ってるはずもないのに?

そこでChromeのデベロッパーツールでコンソールを開くと見慣れないエラーが。。

Google Maps API warning: NoApiKeys https://developers.google.com/maps/documentation/javascript/error-messages#no-api-keys

NoAPIKeysエラー?

APIKeyはいらないはず、と思いながら調べたところ、Google が2016年6月22日に以下のようなアナウンスを出していた事が分かりました。

http://googlegeodevelopers.blogspot.jp/2016/06/building-for-scale-updates-to-google.html

これによると6/22より、スタンダードプラン(無料のもの)を使って新しく作成するアプリケーションでは
API Keyが必須になったとあります。

Google developers blog でもそんな事書いていなかった気がするんですが。。

API Keyはどうやって取るの?

まずは予めGoogle アカウントにログインをしておいてください

160701

Google Map API のサイトにある「キーを取得」を選択します

 

160701_02

するとこんな感じのモーダルが表示されるので「続ける」を選択

 

160701_03

今度はGoogle API Console に遷移します。
「プロジェクトを作成」を選択したまま「続行」します。

160701_04

するとこのような「認証情報」という画面が表示されます。

ここでAPI Keyを発行するのですが、とりあえず「作成」します。

 

160701_05

なにやらAPIが発行されたようです。

 

160701_06

まだこの段階では発行されたAPIが使われるサイトのドメインが登録されていません。

作成されたAPIキーの名前を選択してドメインを登録しましょう。

 

160701_07

API Key発行の画面が表示されるので、ここで改めてアプリケーションを作成するサイトのドメインを設定します。
テストサイトと本番サイトがサブドメインでわかれている場合は、サブドメインをワイルドカードにすれば環境ごとにキーを設定する必要が無く便利です。

 

これで、API Keyの準備ができました。

あとはアプリケーション側で、APIのロードをこんな感じで変更します。

変更前
変更後
これで今までどおり、Google Map APIが使用出来るはずです。

前に作ったアプリケーションはどうなるの?

6/22以前にすでに稼働しているアプリケーションは、今のところAPI Keyなしでも動きます。
今のところはAPI Keyをいつまでに付けなければいけない、という事も無さそうです。

しかし今後いつ、今回のように突然の変更があるかは分かりません。

今後はAPI Keyを必須にするという方針に沿って、直せるうちになおした方が良いでしょう。

WordPress管理画面 カスタム投稿の記事一覧画面で絞り込み検索を追加する

システム部の鈴木です。

先日、WordPressでファイルアップロードの機能におけるセキュリティホールの対策を行った最新版
4.5.2 がリリースされました。

弊社では沢山のサイトを管理させていただいておりますが、WordPressを導入しているサイトも多く
今回のような緊急性の高いセキュリティアップデートのときは一苦労です。

そんなWordPressについての小ネタです。

 カスタム投稿(Custom Post Type)

ブログというよりもCMSとしてWPを使う場合は、通常の投稿メニューでは無く
用途ごとにメニューを設けてそれぞれに適した入力項目を持った管理画面を作ったり、という事がよくあります。

そういった独自のメニューを作るときには「カスタム投稿(Custom Post Type)」という機能を使います。

 カスタム分類(Custom Taxonomy)

通常の投稿メニューとは別にカスタム投稿でメニューを作成したら、
その中でもカテゴリを設けたくなる場合があります。

それを実現するのが「カスタム分類(Custom Taxnomy)」というものです。

 

これらは Custom Post Type UI などのプラグインを使えばどちらも簡単に管理画面からの設定だけで作ることができます。

 

以上を踏まえて今回の本題です。

投稿一覧の画面には以下のようなカテゴリで絞り込むメニューがあると思います

20160527

カスタム投稿の一覧ページだと、カスタム分類を設定しても出てこないんですよね。
でも、検索があれば非常に便利。

ということで調べたところ、以下のようなコードが散見されました。

 

<br />
function add_post_taxonomy_restrict_filter()<br />
{<br />
    global $post_type;<br />
    if (&#8216;music&#8217; == $post_type) {<br />
        $menu = &lt;&lt;&lt;EOH<br />
          &lt;select name=&quot;genre&quot;&gt;<br />
            &lt;option value=&quot;&quot;&gt;カテゴリー指定なし&lt;/option&gt;<br />
EOH;<br />
            $terms = get_terms(&#8216;genre&#8217;);<br />
            foreach ($terms as $term) { ?&gt;<br />
                $menu .= &#8216;&lt;option value=&quot;&#8217; . $term-&gt;slug . &#8216;&quot;&gt;&#8217; . $term-&gt;name . &#8216;&lt;/option&gt;';<br />
            }<br />
        $menu .= &#8216;&lt;/select&gt;';</p>
<p>        echo $menu;<br />
    }<br />
}<br />
add_action(&#8216;restrict_manage_posts&#8217;, &#8216;add_post_taxonomy_restrict_filter&#8217;);<br />

これを使用しているテーマの function.php に書きましょう、というもの。
この例だと “music” というカスタム投稿で “genre” というカスタムタクソノミーから絞り込みを行う、というものです。
<select>のname属性には絞り込みに使うカスタムタクソノミーを指定するとの事です。

 

しかしこれ、どうもWordPressの4.4.1くらいからはうまく動かないみたいです。
メニューは出てきますがどうも上手く絞り込みがされません。

そこで訂正したものがこちらです。

<br />
function add_post_taxonomy_restrict_filter()<br />
{<br />
    global $post_type;<br />
    if (&#8216;music&#8217; == $post_type) {<br />
        echo &#8216;&lt;input type=&quot;hidden&quot; name=&quot;taxonomy&quot; value=&quot;music&quot;&gt;';<br />
        echo &#8216;&lt;select name=&quot;term&quot;&gt;';<br />
        echo &#8216;  &lt;option value=&quot;&quot;&gt;カテゴリー指定なし&lt;/option&gt;';</p>
<p>        $terms = get_terms(&#8216;music&#8217;);<br />
        foreach ($terms as $term) {<br />
            echo &#8216;&lt;option value=&quot;&#8217; . $term-&gt;slug . &#8216;&quot;&gt;&#8217; . $term-&gt;name . &#8216;&lt;/option&gt;';<br />
        }<br />
        echo &#8216;&lt;/select&gt;';<br />
    }<br />
}<br />
add_action(&#8216;restrict_manage_posts&#8217;, &#8216;add_post_taxonomy_restrict_filter&#8217;);</p>
<p>

変更のポイントは主に2つ

  • カスタム分類名は hidden で渡す
  • <select>のname属性は 検索対象とするキー、つまり “term” を指定する

この修正で上手く行きました。

 

 カスタム投稿、カスタム分類は非常によく使う機能なので、今後は本体で是非サポートしてほしいですね。

それまではこの方法をお試しください。

【WordPress】管理画面だけHTTPS接続にする方法

システム部の鈴木です。

前回の投稿からかなり間が空いてしまいましたが、その間も色々とWordPressを活用した案件を担当させていただきました。
その中で対応したものの一つになりますが、管理画面だけHTTPS接続にする方法をご紹介したいと思います。

最近、特に昨年辺りからは常時SSL接続がデファクトになるなどと言われていますが
レンタルサーバを使っている場合などは特に、その対応が難しい場合があります。

例えば

  1. 共用のSSLが使えるけどドメインが変わってしまう
  2. HTTP接続とHTTPS接続でweb公開領域(ドキュメントルート)が分かれている

など、管理画面ではSSLを使いたいけど使えないケースがあると思います。

そこで調べたところ、日本語の情報はあまり無かったのですが
やはり海外には同じ問題を抱えている人がいたようでした。

HTTPS Domain Alias

使い方は非常に簡単です。

  1. 他のプラグインと同様にプラグインディレクトリに設置
  2. wp-config.php に以下の2行を追加します

<br />
define(&#8216;FORCE_SSL_ADMIN&#8217;, true);<br />
define(&#8216;HTTPS_DOMAIN_ALIAS&#8217;, &#8216;example.org&#8217;);<br />

2行目のドメイン部分は、SSL接続時のドメインを指定します。
httpのときとドメインが変わってもOKです。

たったこれだけ。非常にシンプルで便利です。
今のところWP 4.4系でのみ試していますがバッチリ機能しています。

 

HTTPS接続だけweb公開領域が別になっていて、管理画面だけHTTPSにしたいという場合は
WordPressのインストールディレクトリのエイリアスかシンボリックリンクでも設定してあげればフロント画面はHTTP接続、管理画面側だけはHTTPS接続にする、といった事も可能です。

 

LinuxサーバでwebサイトのキャプチャーをFlash込みで撮る方法

システム部の鈴木です

とある案件でたくさんのwebサイトのキャプチャー画像をFlashも含めて取得する必要が出ました
その際に取った方法をご紹介したいと思います

今回は以下の方法を採用しました

  • Linuxサーバ(CentOS 6.6)を使う
  • 仮想ディスプレイ(Xvfb)のX Window上でFlashプラグインをインストールしたFirefoxを起動
  • ImageMagic の import コマンドでキャプチャを保存

この方法、ググれば同じような方法を紹介しているページはたくさん見つかると思いますが
微妙に違ったり、その通りにやっても上手くいかない事もあると思います

そこで、今回実際にやってみて上手くいった方法を残しておきたいなと思います

Continue reading

Really Simple CSV Importer を使って Advanced Custom Fields で作ったフィールドを登録する

システム部の鈴木です。
WordPressで多くの投稿を一気にインポートしたい場合、皆様はどうされてますでしょうか?

そんなときに便利なのが、CSVファイルから一気に投稿をインポートできる
Really Simple CSV Importer

ですが、今回は Advanced Custom Fields を使って作成した画像などのメディアアップロードのカスタムフィールドに登録したい場合にどうするか、ちょっと調べてみました
Continue reading

WordPressでドメイン関連の設定を一気に変えたいときのSQL

システム部の鈴木です。

5月に入ってますます暑くなってきましたね。
こういう時こそ、普段なかなか動かせない身体を動かしたいもの。
ということで家から地元の駅までの短い距離ですが、自転車通勤を再開しました。

距離は短いですが、キツめの坂を上って下って、また上って下って上って・・・
の繰り返しなのでなかなか脚にきます。。

冬は防寒してても自転車は寒いのでバスに乗ってましたが
今の時期は気持が良いので、このままちゃんと続けたいものです。

 

さて、今回はWordPressのサイトを別のサーバに移したい場合の子ネタです。

wp_logo


WordPressを別サーバに移動するとき、新サーバ側にはこれらを移動します。

  1. サイトのhtmlドキュメントや画像、プログラム等一式
  2. DB(MySQL)のデータ

それも行ったうえで、wp-config.php で新サーバのDB設定に書き換えたり
新サーバ側での一時的なテスト用にドメインを設定したりといったことをします。

現在のサーバはひとまずそのままにして、移行して一通りの動作確認をしてから
DNSを切り替えるなりして新サーバ側を公開する、ってパターンが多いと思いますが
新サーバ側でテスト用のドメインを設定していた場合に
WordPress の記事ページへのリンクをたどっても元々のドメインのURLで表示されてしまいます。

これはWordPress側で各ページのパーマリンクがDBに記録されているからなのですが、
動作確認をするにはちょっとこれでは作業がしにくいですね。

 

そこで、MySQLで以下のクエリを実行して、一気にパーマリンクなどの設定を変えてしまいましょう。

<br />
UPDATE wp_options SET option_value = REPLACE(option_value, &#8216;http://hogehoge.com&#8217;, &#8216;http://new.hogehoge.com&#8217;);<br />
UPDATE wp_posts SET guid = REPLACE(guid, &#8216;http://hogehoge.com&#8217;, &#8216;http://new.hogehoge.com&#8217;);<br />

 

 

2つのSQLがありますが、どちらも'http://hogehoge.com'から'http://new.hogehoge.com'に変更したい場合の例です。

最初のものはWordPress管理画面から設定できるドメインの設定部分を変えるものです。
記事や固定ページなどのパーマリンクは2つ目のSQLで変更します。

これでドメインが変わってもリンクが切れること無くサイトのチェックが行えます。

OpenSSLのバージョン確認方法あれこれ

システム部の鈴木です。

先日、OpenSSLのバージョン1.0.1~1.0.2における深刻な脆弱性が見つかりました。
http://www.jpcert.or.jp/at/2014/at140013.html

heartbleed

何が問題かというと、この脆弱性を利用してサーバのメモリ上にあるデータを盗むことができちゃうというものです。

該当のバージョンでHeartBeatエクステンションが有効な場合のバグとのことからHeartBleed と呼ばれているバグですが
幸いなことに弊社で管理しているサーバはすべて該当のバージョンには当てはまらず大きな問題もありませんでした。
しかし深刻な問題なのでまだ未確認な方はぜひ確認を行ってください。

というわけで、OpenSSLのバージョン確認方法あれこれです。

 

Continue reading

javascriptでphpのincludeみたいな事をしてみる

システム部の鈴木です。
年度末で弊社も大忙しですが、小ネタを一つ。

phpとかですと共通のヘッダやフッタなどを共通化するというのは良くありますが、
それをJavaScriptでやってみたいと思います。

仕様

  • 共通ファイルのhtmlを作成、部品毎に<!–inc_****–><!–/inc_****–>で囲んでおく
  • ajaxで共通ファイルをロード
  • 読み取る側のhtmlでは、<!–inc_****–><!–/inc_****–> の **** 部分を指定して表示

共通ファイルのhtml

例えば幾つかのリストがあって、複数のページで使いまわしたいとします。
それを共通のファイルにぶちこんでおきます。

<br />
&lt;!&#8211;inc_lsit1&#8211;&gt;<br />
  &lt;ul&gt;<br />
    &lt;li&gt;てすと1&lt;/li&gt;<br />
    &lt;li&gt;てすと1&lt;/li&gt;<br />
  &lt;/ul&gt;<br />
&lt;!&#8211;/inc_lsit1&#8211;&gt;<br />
&lt;!&#8211;inc_lsit2&#8211;&gt;<br />
  &lt;ul&gt;<br />
    &lt;li&gt;てすと2&lt;/li&gt;<br />
    &lt;li&gt;てすと2&lt;/li&gt;<br />
  &lt;/ul&gt;<br />
&lt;!&#8211;/inc_lsit2&#8211;&gt;<br />

javascript

include.js とかで保存しときます
共通ファイルのURLは今回は決め打ちですが、共通ファイルが多様でない想定のものなので決め打ちで。

<br />
$(function(){<br />
  var tmp = $.ajax({<br />
    url: &#8216;/path/to/include.html&#8217;,<br />
    cache: false,<br />
    async: false<br />
  }).responseText;</p>
<p>  var target = $(&#8216;[id^=inc_]&#8217;);</p>
<p>  jQuery.each(target, function() {<br />
    var include = $(this).attr(&#8216;id&#8217;);<br />
    var reg = new RegExp(&#8216;&lt;\!&#8211;&#8216;+include+&#8217;&#8211;&gt;([\n\r]|.)*&lt;\!&#8211;\/&#8217;+include+&#8217;&#8211;&gt;&#8217;);<br />
    var html = tmp.match(reg);<br />
    $(this).html(html[0].replace(reg, &#8221;));<br />
  });<br />
});<br />

読み取る側のhtml

include.js をロードし、読み取りたい部分をidで指定します
以下の例では共通ファイルから<!–inc_list1–><!–/inc_list1–>の範囲だけを抜き出して表示させます

<br />
&lt;script src=&quot;include.js&quot;&gt;&lt;/script&gt;<br />
&lt;div id=&quot;list1&quot;&gt;&lt;/div&gt;<br />

以上です。

今回のように特定のタグ~特定のタグのような範囲を抜く場合は最短マッチを使います。
さらに今回は複数行にマッチさせています。
加えてマッチさせる条件に変数を使いたいので RegExp オブジェクトでパターンを作成してます。
該当部分はここですね。

<br />
    var reg = new RegExp(&#8216;&lt;\!&#8211;&#8216;+include+&#8217;&#8211;&gt;([\n\r]|.)*&lt;\!&#8211;\/&#8217;+include+&#8217;&#8211;&gt;&#8217;);<br />
    var html = tmp.match(reg);<br />

また読み込む側のページで何箇所か抜き出したい、今回の例ならlist1もlist2も表示させたい場合もあると思うので
そのために $(‘[id^=inc_]’) でページ内の表示させたい部分をまとめて拾って、jQuery.each() で順に処理させてます。

いわゆる共通化が目的ならphpが使えればもちろんその方が簡単なのですが、
staticページで作っててmod_rewriteも使えないなど制約があるときにはこんな方法もあります、という小ネタでした。

またはちょっとしたスクレイピングをささっとやりたい場合に使えるかもしれないですね。

Codeignitor はじめました。のその後。

こんにちは。システム部の鈴木です。
先日、新年会で肥ったといじられました。暖かくなってから本気出します。

以前書いてからだいぶ間が空いてしまったCodeIgnitorを使って早速社内向けのサービスを作ってみました。
今回はそのときに気がついた点とか書いていこうと思います。

 

Continue reading