laravel-mysql-spatial

laravel-mysql-spatial copied to clipboard

As I point out in https://github.com/grimzy/laravel-mysql-spatial/issues/77, distanceSphereValue does not currently allow a 3rd distance parameter like distanceSphere does

So if I want to get all cities within a 50km radius, ordered by distance, with the distance itself available in the results, there isn't a way to perform this query efficiently right now.

You can achieve the desired results by using collection methods but it is not very performant on a large result set:

        $model->distanceSphereValue('current_city_location', $userLocation)
        ->get()
        ->where('distance', '<=', $distanceInMeters)
        ->sortBy('distance');

I suspect there is no reason why the distanceSphereValue scope doesn't allow this 3rd parameter and it could be easily added in as an optional?

Would very much appreciate if you could add it @grimzy

Many thanks!

I added a new custom scope which seems to do what I want and performs well, not sure if there's a more efficient way to write this query:

<?php

namespace App\Traits;

trait CustomSpatialTrait
{
    public function scopeDistanceSphereValueWithin($query, $geometryColumn, $geometry, $distance)
    {
        $this->isColumnAllowed($geometryColumn);

        $columns = $query->getQuery()->columns;

        if (!$columns) {
            $query->select('*');
        }

        $query->selectRaw("st_distance_sphere(`$geometryColumn`, ST_GeomFromText(?)) as distance", [
            $geometry->toWkt(),
        ])
        ->whereRaw("st_distance_sphere(`$geometryColumn`, ST_GeomFromText(?)) <= ?", [
            $geometry->toWkt(),
            $distance,
        ]);
    }
}