[laravel]緯度経度から最寄り駅を取得

駅情報取得

http://www.ekidata.jp/dl/
ここからcsv（駅データ）を取得

MySQLにテーブル作成

php artisan migrate

databases/migations/2017_05_06_143121_create_stations_table.php

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateStationsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('stations', function (Blueprint $table) {
            $table->increments('station_cd');
            $table->unsignedInteger('station_g_cd')->index();
            $table->string('station_name', 128);
            $table->string('station_name_k', 128);
            $table->string('station_name_r', 128);
            $table->unsignedInteger('line_cd')->index();
            $table->integer('pref_cd');
            $table->integer('e_status');
            $table->integer('e_sort');
            $table->timestamps();
            $table->softDeletes();
        });

        DB::statement("ALTER TABLE stations ADD COLUMN location POINT not null AFTER pref_cd");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('stations');
    }
}

composerにcsvをパースするライブラリ追加

composer require goodby/csv

seeder作成

php artisan db:seed

databases/seeds/StationsTableSeeder.php

<?php

use Illuminate\Database\Seeder;
use Goodby\CSV\Import\Standard\Lexer;
use Goodby\CSV\Import\Standard\Interpreter;
use Goodby\CSV\Import\Standard\LexerConfig;

class StationsTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {

        $config = new LexerConfig();
        $config->setDelimiter(",");
        $config->setIgnoreHeaderLine(true);
        $lexer = new Lexer($config);
        $interpreter = new Interpreter();
        $interpreter->addObserver(function(array $row) { 
            DB::table('stations')->insert([
                'station_cd' => $row[0],
                'station_g_cd' => $row[1],
                'station_name' => $row[2],
                'station_name_k' => $row[3],
                'station_name_r' => $row[4],
                'line_cd'   => $row[5],
                'pref_cd'   => $row[6],
                'location'   => DB::raw("(GeomFromText('POINT(". $row[9] . " ". $row[10]. ")'))"),
                'e_status'   => $row[13],
                'e_sort'   => $row[14],
                ]);
        });
        $lexer->parse(database_path('seeds/csv/station20170403free.csv'), $interpreter);
    }
}

緯度139.745433　経度35.658581から近い順に取得

mysql>  select station_cd, X(location), Y(location) from stations order by ABS(X(location) - 139.745433) + ABS(Y(location) - 35.658581) asc limit 10;
+------------+-------------+-------------+
| station_cd | X(location) | Y(location) |
+------------+-------------+-------------+
|    2800317 |  139.745069 |   35.662978 |
|    9930122 |  139.743642 |   35.655007 |
|    9930306 |  139.751535 |   35.661215 |
|    9930305 |  139.749824 |   35.654074 |
|    9930121 |   139.75466 |   35.656785 |
|    9930209 |   139.75466 |   35.656785 |
|    9930123 |  139.736116 |   35.656503 |
|    2800916 |  139.737051 |   35.654682 |
|    2800915 |     139.739 |   35.665595 |
|    9930304 |  139.748775 |    35.64818 |
+------------+-------------+-------------+
10 rows in set, 4 warnings (0.03 sec)