How to sort query results by distance in Laravel QueryBuilder / MySQL Spatial Package?

Question

First I want to show you the current database structure. There are three tables:

dishes (id, name)

locations (identifier, name, coordinates (POINT))

dish_location (location_id, dish_id)

Now I want to implement an API that receives the position (latitude, longitude) of the user and returns a list of dishes sorted by distance in km. I already have a method that takes two latitudes and two longitudes and gives me the distance. But I'm sure you can show me a way, which is a more efficient way to do this directly in a MySQL query.

Optional: I want to use the "load more" function in the API. So, I pass the score of the items already received or how can I solve this in this case?

I use this for MySQL spatial package

Answer 1

First, let's see how to do this with the basic query builder. Then we discuss how to fulfill this query using Eloquent models:

function paginateDishesFromPoint(Point $point, $pageSize) { $distanceField = "ST_Distance_Sphere(locations.coordinates, " . "ST_GeomFromText('{$point->toWKT()}') AS distance"; return DB::table('dishes') ->select('dishes.*', DB::raw($distanceField)) ->join('dish_locations', 'dish_locations.dish_id', '=', 'dishes.id') ->join('locations', 'locations.id', '=', 'dish_locations.location_id') ->orderBy('distance') ->paginate($pageSize); } 

The ST_Distance_Sphere() function calculates the distance by which we can sort the results. The Laravel paginate() method performs automatic pagination using the page parameter passed through the request URL. Read the pagination documents for more information. Using the function above, we can get a broken set of results as follows:

 $point = new Point($latitude, $longitude); $sortedDishes = paginateDishesFromPoint($point, 15); 

... where Point is the Grimzy\LaravelMysqlSpatial\Types\Point class of the package we use , and 15 is the number of results per page.

Now try to do this with Eloquent models. We will use the local query area to encapsulate the logic needed to create the part of the query that performs the ordering:

 class Dish extends Model { ... public function locations() { return $this->belongsToMany(App\Location::class); } public function scopeOrderByDistanceFrom($query, Point $point) { $relation = $this->locations(); $locationsTable = $relation->getRelated()->getTable(); $distanceField = "ST_Distance_Sphere($locationsTable.coordinates, " . "ST_GeomFromText('{$point->toWKT()}') AS distance"; return $query ->select($this->getTable() . '.*', DB::raw($distanceField)) ->join( $relation->getTable(), $relation->getQualifiedForeignKeyName(), '=', $relation->getQualifiedParentKeyName() ) ->join( $locationsTable, $relation->getRelated()->getQualifiedKeyName(), '=', $relation->getQualifiedRelatedKeyName() ) ->orderBy('distance'); } } 

This implementation uses metadata on models to add table and field names to the query, so we do not need to update this method if they change. Now we can get the ordered set using the model:

 $point = new Point($latitude, $longitude); $sortedDishes = Dish::orderByDistanceFrom($point)->paginate($pageSize); 

$sortedDishes is an instance of the Laravel LengthAwarePaginator that wraps the Collection models. If we pass the results in a view, here's how to display them in a Blade template:

 <ul> @foreach($sortedDishes as $dish) <li>{{ $dish->name }} is {{ $dish->distance }} meters away.</li> @endforeach </ul> <a href="{{ $sortedDishes->nextPageUrl() }}">Load more...</a> 

As shown above, paginator provides convenience methods that we can use to easily navigate between calculated results.

Alternatively, we could use AJAX queries to load the results. Just remember to pass the current page + 1 to the page parameter of the request data.

Answer 2

SQL

 SELECT d.name AS `Dish`, l.name AS `Location`, ST_Distance(l.coordinates, POINT(<<<longitude>>>, <<<latitude>>>)) AS `Distance` FROM dish_locations dl JOIN dishes d ON d.id = dl.dish_id JOIN locations l ON l.id = dl.location_id ORDER BY `Distance` LIMIT <<<n-1>>>, <<<page size>>>; 

... insertion <<<longitude>>> and <<<latitude>>> from the user’s position, <<<n-1>>> from the number of lines that have already been received minus one, and <<<page size>>> as the required next number of rows to retrieve.

Demo

http://rextester.com/YAEBF16430

Explanation

• ST_Distance_Sphere used to calculate the distance in meters.
• LIMIT <<offset>>, <<num>> used to implement a search call (where the offset is based on zero).

Answer 3

Postgis has two operators (↔ and <#>) for working from a distance and calculating KNN (nearest neighbors). You can use them instead of st_distance. Query operators will look something like this:

 WITH index_query AS ( SELECT ST_Distance(geom, 'SRID=3005;POINT(1011102 450541)'::geometry) as d,edabbr, vaabbr FROM va2005 ORDER BY geom <-> 'SRID=3005;POINT(1011102 450541)'::geometry LIMIT 100) SELECT * FROM index_query ORDER BY d limit 10; 

Using the distance d, you can order.