Mike Polatoglou

Mike Polatoglou

Who am I?

PHP Developer in Brighton, UK. Working with Laravel and Drupal 8


What I write about


Recent Posts

Geospatial MySQL in Laravel 5.3

How to apply the ST_Distance_Sphere MySQL function to Laravel Eloquent ORM

How to add geospatial queries (https://dev.mysql.com/doc/refman/5.7/en/spatial-datatypes.html) to the Laravel ORM to do order by distance and display distance in query results.

As an example we will create a Store object that will have a location field. We will then query the closest stores to a location (latitude, longitude)

By default Laravel doesn't support the POINT type in migrations so we need to add the following in the migration file:

<?php

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

class CreateStoresTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('stores', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->nullable();
            $table->timestamps();
        });
        DB::statement('ALTER TABLE stores ADD location POINT' );
    }

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

This adds an extra field on the migration called location of type point.

Then in the Model we will add the following:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Store extends Model
{

    protected $guarded = ['id']

    protected $geofields = ['location'];

    public function setLocationAttribute($value)
    {
        $this->attributes['location'] = DB::raw("POINT($value)");
    }

    public function getLocationAttribute($value)
    {
        $loc =  substr($value, 6);
        $loc = preg_replace('/[ ,]+/', ',', $loc, 1);
        return substr($loc, 0, -1);
    }

    public function newQuery($excludeDeleted = true)
    {
        $raw='';
        foreach($this->geofields as $column){
            $raw .= ' astext(' . $column . ') as ' . $column . ' ';
        }
        return parent::newQuery($excludeDeleted)->addSelect('*', DB::raw($raw));
    }

    public function scopeDistance($query, $dist, $location)
    {
        return $query->whereRaw('ST_Distance_Sphere(location,POINT(' . $location . ')) < ' . $dist);
    }

    public function scopeWithDistance($query, $location)
    {
        return $query->selectRaw('ST_Distance_Sphere(location,POINT(' . $location . ')) AS distance');
    }
}

This adds the relevant setter and getter to our Model as well as it allows to query using the distance. We have 2 different methods to call on our controllers now:

public function withDistance($query, $dist, $location)

which gives us the distance from a location point (in the form of a string "latitude,longitude" and the:

public function distance($query, $location)

which filters locations within a set distance from a location point (again a string "latitude,longitude")

Now our controller looks something like:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Store;

class SearchController extends Controller
{

    /**
     * Performs the search of stores
     *
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function search(Request $request)
    {
        $data = $request->toArray();
        ...
        ...
        if(empty($data['longitude']) || empty($data['latitude'])) {
            return redirect('stores')->with(['message' => 'Search term cannot be empty']);
        }
        ...
        ...
        ...
        $stores = Store->distance(10000, $data['latitude'] . ',' . $data['longitude'])
            ->withDistance($data['latitude'] . ',' . $data['longitude'])
            ->orderBy($order, $orderby)
            ->paginate(20);

        return view('stores.search', [
                'stores' => $stores,
                'lat' => $data['latitude'],
                'lng' => $data['longitude']
            ]);

    }

}

This requires MySQL > 5.7.6 for the the ST_Distance_Sphere function (https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html). The great thing with this function is that it returns the distance in meters so no additional calculations are necessary.