PHPの中でSQL文を書いて緯度経度から指定半径内の住所の一覧を得る方法

Question

###前提・実現したいこと
PHPの中でSQL文を書いて緯度経度から指定半径内の住所の一覧を得たいと思います。
ベースのSQL文では正常に値を取得できましたが、
下記を参考に指定半径内の住所で絞り込もうとするとうまくできませんでした。
恐れ入りますが、アドバイス頂ければ幸いです。
https://www.firstlogic.co.jp/blog/lab/raku/71

WordPressを使用していますが、今回はあまり関係ないかと思いますので端折ります。

###ベースのSQL文

$result = $wpdb->get_results("
    SELECT post_title, id, m1.meta_value AS '_lat', m2.meta_value AS '_lnt', m3.meta_value AS 'googlemap'
    FROM $wpdb->posts
    INNER JOIN $wpdb->postmeta AS m1
    ON m1.post_id = $wpdb->posts.ID /* テーブルを結合-カスタムフィールド */
    INNER JOIN $wpdb->postmeta AS m2
    ON m2.post_id = $wpdb->posts.ID /* テーブルを結合-カスタムフィールド */
    INNER JOIN $wpdb->postmeta AS m3
    ON m3.post_id = $wpdb->posts.ID /* テーブルを結合-カスタムフィールド */
    WHERE post_type = 'areainfo'
    AND post_status = 'publish'     /* かつ公開済の記事 */
    AND m1.meta_key = '_lat'
    AND m2.meta_key = '_lnt'
    AND m3.meta_key = 'googlemap'
    ORDER BY post_date DESC
    LIMIT 5
");

上記の出力結果はこんな感じです。

Array ( [0] => stdClass Object ( [post_title] => ソフィアタワー勝どき [id] => 562 [_lat] => 35.6584463 [_lnt] => 139.7735894 [googlemap] => a:3:{s:7:"address";s:41:"東京都中央区勝どき３丁目12-12";s:3:"lat";s:10:"35.6584463";s:3:"lng";s:11:"139.7735894";} ) [1] => stdClass Object ( [post_title] => クレストレジデンス [id] => 561 [_lat] => 35.6571881 [_lnt] => 139.7719590 [googlemap] => a:3:{s:7:"address";s:41:"東京都中央区勝どき５丁目5-14 ";s:3:"lat";s:10:"35.6571881";s:3:"lng";s:18:"139.77195900000004";} ) )

###試したSQL文：追記版

$areainfo_lat = "35.6746700";
$areainfo_lnt = "139.7885380";
$result = $wpdb->get_results("
    SELECT post_title, id, m1.meta_value AS '_lat', m2.meta_value AS '_lnt', m3.meta_value AS 'googlemap'
,(6371 * ACOS(
    COS(RADIANS($areainfo_lat))
    * COS(RADIANS(m1.meta_value))
    * COS(RADIANS(m2.meta_value) - RADIANS($areainfo_lnt))
    + SIN(RADIANS($areainfo_lat))
    * SIN(RADIANS(m1.meta_value))
  )) AS distance
    FROM $wpdb->posts
    INNER JOIN $wpdb->postmeta AS m1
    ON m1.post_id = $wpdb->posts.ID /* テーブルを結合-カスタムフィールド */
    INNER JOIN $wpdb->postmeta AS m2
    ON m2.post_id = $wpdb->posts.ID /* テーブルを結合-カスタムフィールド */
    INNER JOIN $wpdb->postmeta AS m3
    ON m3.post_id = $wpdb->posts.ID /* テーブルを結合-カスタムフィールド */
    WHERE post_type = 'areainfo'
    AND post_status = 'publish'     /* かつ公開済の記事 */
    AND m1.meta_key = '_lat'
    AND m2.meta_key = '_lnt'
    AND m3.meta_key = 'googlemap'
  HAVING distance
  ORDER BY distance
    LIMIT 5
");

上記の出力結果はこんな感じです。
Array ( [0] => stdClass Object ( [post_title] => ソフィアタワー勝どき [id] => 562 [_lat] => 35.6584463 [_lnt] => 139.7735894 [googlemap] => a:3:{s:7:"address";s:41:"東京都中央区勝どき３丁目12-12";s:3:"lat";s:10:"35.6584463";s:3:"lng";s:11:"139.7735894";} [distance] => 2.2534462617295463 ) [1] => stdClass Object ( [post_title] => クレストレジデンス [id] => 561 [_lat] => 35.6571881 [_lnt] => 139.7719590 [googlemap] => a:3:{s:7:"address";s:41:"東京都中央区勝どき５丁目5-14 ";s:3:"lat";s:10:"35.6571881";s:3:"lng";s:18:"139.77195900000004";} [distance] => 2.453954933727737 ) )

Comments

[退会済みユーザー]

MySQLには空間関数群があるので、緯度経度を元にした距離での絞込に使えそうな気がしますが、ダメなんでしょうか。参考例→ http://qiita.com/mitani/items/6909406ac4fe0db2d35c

[igaiga]

ありがとうございます！拝見しましたが、データ登録はWordPressを使用するので「POINT」というデータ形式で保存できなそうに思ったのですがどうでしょうか？

[CHERRY]

distance の計算かなとも思いますが、HAVING distance <= 5.0 を外したら、結果は返ってくるのでしょうか？ 返ってきた結果を追記してもらえないでしょうか。

[igaiga]

CHERRYさんありがとうございます。値は帰ってきませんでした；；

[igaiga]

すみません。 * COS(RADIANS(_lat)) を* COS(RADIANS(m1.meta_value))、* COS(RADIANS(_lnt)を* COS(RADIANS(m2.meta_value)に直したら値が取れましたので追記しました。

[igaiga]

距離的には２００～３００ｍあたりのはずですが、2km以上あることになってますね。。。やっぱり計算がおかしいのかな。

[ikedas]

HAVING句を元に戻してもだめですか?

[igaiga]

できました。ありがとうございました！$areainfo_latと$areainfo_lntの座標が違うだけでした＾＾；

Answer 1

皆さんにアドバイスいただきながら完成させることができました。ありがとうございました！

$areainfo_lat = "35.6746700";
$areainfo_lnt = "139.7885380";
$result = $wpdb->get_results("
    SELECT post_title, id, m1.meta_value AS '_lat', m2.meta_value AS '_lnt', m3.meta_value AS 'googlemap'
,(6371 * ACOS(
    COS(RADIANS($areainfo_lat))
    * COS(RADIANS(m1.meta_value))
    * COS(RADIANS(m2.meta_value) - RADIANS($areainfo_lnt))
    + SIN(RADIANS($areainfo_lat))
    * SIN(RADIANS(m1.meta_value))
  )) AS distance
    FROM $wpdb->posts
    INNER JOIN $wpdb->postmeta AS m1
    ON m1.post_id = $wpdb->posts.ID /* テーブルを結合-カスタムフィールド */
    INNER JOIN $wpdb->postmeta AS m2
    ON m2.post_id = $wpdb->posts.ID /* テーブルを結合-カスタムフィールド */
    INNER JOIN $wpdb->postmeta AS m3
    ON m3.post_id = $wpdb->posts.ID /* テーブルを結合-カスタムフィールド */
    WHERE post_type = 'areainfo'
    AND post_status = 'publish'     /* かつ公開済の記事 */
    AND m1.meta_key = '_lat'
    AND m2.meta_key = '_lnt'
    AND m3.meta_key = 'googlemap'
  HAVING distance <= 0.5
  ORDER BY distance
    LIMIT 5
");

print_r($result);