laravel mysql 距離 geo lat lng 検索

mysql で 位置情報検索してみようぜ

・geometry 型を使おうぜ
・検索で使うから SPATIAL INDEX も貼る。
・ライブラリとか使わない。使ったけどバグってたんで。
・距離はkmで取得しない

まずは位置情報テーブルを作る

CREATE TABLE `geos` (
`name` VARCHAR(30) NOT NULL,
`location` GEOMETRY NOT NULL
) ENGINE = InnoDB;

インデックスを貼る

ALTER TABLE `geos` ADD SPATIAL `location` (`location`);

なぜかインデックスを貼れない場合は以下をやる。
#1416 - Cannot get geometry object from data you send to the GEOMETRY field

UPDATE `geos` SET `location` = POINT(0,0)

位置情報を挿入

経度、緯度 の順番

INSERT INTO `geos` (`name`, `location`)
VALUES ('札幌駅', GeomFromText('POINT(141.350857 43.067656)')),
('上野駅', GeomFromText('POINT(139.776381 35.712297)')),
('名古屋駅', GeomFromText('POINT(136.881537 35.170915)')),
('アメリカ', GeomFromText('POINT(37.77493 -122.419416)')),
('品川駅', GeomFromText('POINT(139.73876 35.628471)'));

(laravel で挿入する場合は)

        //use DBしとく
        $model = Geo::firstOrNew(['id' => 3]);
        $model->name = 'テスト保存';
        $model->location = \DB::raw("GeomFromText('POINT(139.776381 35.712297)')");
        $model->save();

直接SQL実行

SELECT
id,name,
ROUND(
GLENGTH(
GEOMFROMTEXT( CONCAT( 'LineString( 136.8735744 35.168256 , ', X( location ) ,  ' ', Y( location ) ,  ')' )
)
) * 111000 ) AS distance
FROM
geos
ORDER BY
distance

速度は？

インデックス使えないみたいだけど、上記のSQLがいろいろ試した中で最速。
(合計 11103, Query took 0.0298 seconds.)

laravel

ユーザーモデルにjoinして使おう。

        $lat = '136.8735744';
        $lng = '33.590599';

//        ユーザーの name と geo の name がかぶると取得できんので、 as 句使っておく
        $res = User::with('geos')
            ->join('geos', 'users.id', '=', 'geos.id')
            ->selectRaw(
                "users.name as user_name,geos.id,geos.name,ROUND(
GLENGTH(
GEOMFROMTEXT( CONCAT( 'LineString( ".$lat." ".$lng." , ', X( location ) ,  ' ', Y( location ) ,  ')' )
)
) * 111000 ) AS distance"
            )
            ->orderBy('distance', 'ASC')//遠い順、近い順
            ->limit(10)
            ->get();

        foreach ($res as $v) {
            echo $v->id. ' : ' .$v->user_name.'さん 住所 '.$v['geos'][0]['name'].' 距離: '.$v->distance.'<br>';
        }